SHOW:
|
|
- or go back to the newest paste.
| 1 | - | -- https://pgexercises.com/questions/aggregates/rankmembers.html |
| 1 | + | -- https://pgexercises.com/questions/aggregates/facrev3.html |
| 2 | ||
| 3 | - | with t1 as ( |
| 3 | + | with t1 as( |
| 4 | - | select firstname, surname, ROUND(sum(slots/2.0),-1) as hours |
| 4 | + | select name, sum(slots * case when memid=0 then guestcost else membercost end) as revenue |
| 5 | - | -- round(x,-1) rounds x to nearest 10, https://stackoverflow.com/a/41210389 |
| 5 | + | from cd.bookings b |
| 6 | - | from cd.bookings b |
| 6 | + | join cd.facilities f |
| 7 | - | join cd.members m |
| 7 | + | on f.facid = b.facid |
| 8 | - | on b.memid = m.memid |
| 8 | + | group by 1 |
| 9 | - | group by 1, 2 |
| 9 | + | order by 2 desc) |
| 10 | - | order by 3 desc, 2 asc, 1 asc |
| 10 | + | |
| 11 | - | ) |
| 11 | + | select name, rank() over(order by revenue desc) |
| 12 | - | select *, rank() over(order by hours desc) |
| 12 | + | from t1 |
| 13 | - | from t1 |
| 13 | + | limit 3 |
