SQL Academy · Lesson

Real-World Reporting Patterns

Implement classic dashboards: retention curves, top-N per category, sessionisation — all with window functions.

Pattern: Top-N Per Group

Top 3 orders per user:

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC) AS rn
  FROM orders
)
SELECT * FROM ranked WHERE rn <= 3;

Cumulative revenue over time:

SELECT day, revenue,
       SUM(revenue) OVER (ORDER BY day) AS running_total
FROM daily_revenue;