What you will build
You will calculate customer spending first, rank the summaries second and see why window functions preserve rows that ordinary aggregation would collapse.
Kavya needs a leaderboard, not a pile of orders
An orders table may contain thousands of payment events. Kavya’s dashboard needs one total per customer and a rank across those totals. These are two distinct steps: aggregate, then compare.
GROUP BY changes the level of detail
01SELECT customer_id, COUNT(*) AS order_count,02SUM(total_amount) AS total_spend03FROM orders04WHERE status = 'PAID'05GROUP BY customer_id;
Before grouping, one row means one order. After grouping, one row means one customer summary. Every selected column must identify the group or be produced by an aggregate.
A window adds context without collapsing rows
01WITH customer_spend AS (02SELECT c.customer_id, c.name,03SUM(o.total_amount) AS total_spend04FROM customers AS c05JOIN orders AS o ON o.customer_id = c.customer_id06WHERE o.status = 'PAID'07GROUP BY c.customer_id, c.name08)09SELECT customer_id, name, total_spend,10RANK() OVER (ORDER BY total_spend DESC) AS spend_rank11FROM customer_spend12ORDER BY spend_rank, customer_id13LIMIT 10;
Grouping by ID and name prevents different customers with the same name from being merged. The final ORDER BY guarantees display order; LIMIT 10 alone does not mean “top ten.”
Read the exact output
| customer_id | name | total_spend | spend_rank |
|---|---|---|---|
| C101 | Meena | ₹12,000 | 1 |
| C103 | Arun | ₹9,500 | 2 |
| C102 | Farah | ₹7,200 | 3 |
RANK() gives ties the same rank and leaves a gap afterward. DENSE_RANK() removes the gap; ROW_NUMBER() gives every row a unique sequence.
Running total and moving average
01SELECT order_day, daily_revenue,02SUM(daily_revenue) OVER (03ORDER BY order_day04ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW05) AS running_revenue,06AVG(daily_revenue) OVER (07ORDER BY order_day08ROWS BETWEEN 6 PRECEDING AND CURRENT ROW09) AS seven_row_average10FROM daily_sales11ORDER BY order_day;
The explicit frame states which rows contribute. A seven-row window is not automatically seven calendar days when dates are missing.
Real-world application: retention dashboard
A subscription team can group events into one monthly total per plan, then use LAG() to compare each month with the previous month. Every month remains visible while the query adds trend context.
Common analysis mistakes
Common mistakes
- Mixing row-level columns with aggregates without defining a group.
- Grouping only by a customer name that may not be unique.
- Claiming a window replaces
GROUP BY; useful queries can use both in stages. - Applying
LIMITbefore a deterministic final sort. - Forgetting that the window frame changes moving calculations.
Practice the two-stage pattern
Build one row per category with SUM(total_amount), then apply DENSE_RANK() to rank categories by revenue. Keep aggregation in a CTE and ranking in the outer query.
Choose an answer, inspect the explanation and explain the idea in your own words.
Why aggregate in a CTE before applying RANK()?
Interview-ready explanation
GROUP BY vs window function
GROUP BY collapses detail rows into one row per group. A window function calculates across related rows while keeping the current result rows visible. They solve different problems and can be combined in stages.
Questions beginners usually ask
Which ranking function should I choose? Use ROW_NUMBER for a unique sequence, RANK for ties with gaps and DENSE_RANK for ties without gaps.
Why specify a window frame? It makes a running or moving calculation’s participating rows explicit and avoids database-default surprises.
What to remember
Key takeaways
GROUP BYchanges detail rows into summary rows.- Window functions calculate across related rows without hiding them.
- Aggregate first and rank second when the business question needs both.
- Stable IDs, explicit frames and a final
ORDER BYprevent reporting errors.
Next Byte: move beyond keyword matching and retrieve semantically related documents with PostgreSQL and pgvector.