Lesson 35 / 37 · Windows

Ranking, and filtering on it with QUALIFY

The CTE computes totals first (open it above the main query). Then rank() runs after the join, and QUALIFY keeps the top five. WHERE could not do that: it runs before window functions exist. Most other databases have no QUALIFY; there you wrap the query and filter ranking in an outer WHERE.

The query

WITH totals AS (
  SELECT customer_id, sum(amount) AS total
  FROM orders
  WHERE status = 'paid'
  GROUP BY customer_id
)
SELECT c.name, t.total,
       rank() OVER (ORDER BY t.total DESC) AS ranking,
       round(100.0 * t.total / sum(t.total) OVER (), 1) AS pct
FROM totals t
JOIN customers c ON c.id = t.customer_id
QUALIFY ranking <= 5
ORDER BY ranking

Try dense_rank(): Alice becomes 2, not 3, because dense_rank leaves no gap after a tie, and Bob joins the top five.

Next: Keeping one row per group with ROW_NUMBER · All lessons