0

Top-N per group, dedup & running totals

SQLused in ~60% of apps

DevLog's sprint board has three tickets: a "most active authors" panel, each author's 3 most recent posts, and a running total of posts per week. All three sound like different problems. They are the same two moves — number the rows, then filter or accumulate — wearing different clothes, and getting the tie-breaking wrong on the first one ships a panel that quietly forgets one of two equally-active authors.

Core idea. Number rows with a window function inside a CTE, then filter or accumulate in the query that reads the CTE. row_number()/rank()/dense_rank() filtered to rn <= N gives you top-N-per-group or dedup; sum() with an explicit ROWS frame gives you a running total. Which ranking function you pick changes what happens at a tie — and a tie you don't plan for either loses a row or returns extra ones.
Playground, this isn't a screenshot

row_number() over (partition by user_id order by created_at desc) on alice's posts. The goal: her 3 most recent only.

1Draft: indexing notesJun 16kept: rn <= 3
2Window functions clicked for meJun 15kept: rn <= 3
3CTEs vs subqueriesJun 10kept: rn <= 3
4RLS, explainedJun 3discarded
5Why I switched to PostgresJun 1discarded
where rn <= 3 cannot go in this same SELECT --WHERE runs before window functions are computed, so rn does not exist yet at that point. Wrap the numbered query in a CTE (or subquery) and filter the CTE.
↑ play a little first, then dig in