SHOW:
|
|
- or go back to the newest paste.
| 1 | - | -- https://pgexercises.com/questions/aggregates/nbooking.html |
| 1 | + | -- https://pgexercises.com/questions/aggregates/rankmembers.html |
| 2 | ||
| 3 | - | with t1 as( |
| 3 | + | with t1 as ( |
| 4 | - | select surname, firstname, m.memid, starttime |
| 4 | + | select firstname, surname, ROUND(sum(slots/2.0),-1) as hours |
| 5 | - | from cd.members m |
| 5 | + | -- round(x,-1) rounds x to nearest 10, https://stackoverflow.com/a/41210389 |
| 6 | - | join cd.bookings b |
| 6 | + | from cd.bookings b |
| 7 | - | on b.memid = m.memid |
| 7 | + | join cd.members m |
| 8 | - | where starttime::DATE >= '2012-09-01' |
| 8 | + | on b.memid = m.memid |
| 9 | - | group by 1,2,3,4 -- removing group by produces wrong data in the next step |
| 9 | + | group by 1, 2 |
| 10 | - | order by 3 asc |
| 10 | + | order by 3 desc, 2 asc, 1 asc |
| 11 | ) | |
| 12 | select *, rank() over(order by hours desc) | |
| 13 | - | select distinct on (3) * |
| 13 | + | from t1 |
