Practice: Who Brings the Money?
NTILE on real spending.
Here's a famous business question. Do a small group of customers bring in most of the money? Let's find out. Split Helen's customers into four equal groups, by how much they've spent, and add up each group's revenue.

- Do a few customers bring most money?
- Four equal groups
- Revenue per group
Spending quarters
Pause and try it. You'll need a CTE for spending, n tile four, and a group by. Here's a solution. The top quarter of customers brought in about twenty three thousand six hundred dollars. That's more than half of all of Helen's revenue. The bottom quarter brought in less than three thousand.
Split customers into 4 spending groups; customers and revenue per group
Show hint
CTE for spend, NTILE(4) OVER (ORDER BY spent DESC), GROUP BY
Show solution
WITH spend AS (
SELECT o.customer_id, SUM(oi.quantity * b.price) AS spent
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN books AS b ON b.book_id = oi.book_id
WHERE o.status IN ('delivered', 'shipped')
GROUP BY o.customer_id
),
quarters AS (
SELECT spent, NTILE(4) OVER (ORDER BY spent DESC) AS quarter
FROM spend
)
SELECT quarter,
COUNT(*) AS customers,
ROUND(SUM(spent), 0) AS revenue
FROM quarters
GROUP BY quarter;โโโโโโโโโโโฌโโโโโโโโโโโโฌโโโโโโโโโโ โ quarter โ customers โ revenue โ โโโโโโโโโโโผโโโโโโโโโโโโผโโโโโโโโโโค โ 1 โ 113 โ 23601.0 โ โ 2 โ 113 โ 11270.0 โ โ 3 โ 112 โ 6632.0 โ โ 4 โ 112 โ 2796.0 โ โโโโโโโโโโโดโโโโโโโโโโโโดโโโโโโโโโโ
Quick check
Quick check. Which function would you use to compare each month with the previous month? Pause and choose. The answer is B. Lag looks at the previous row.
Summary
You've learned window functions. Numbering, ranking, running totals, lag, and n tile. These are the tools data analysts use every day. Next, we stop reading data, and start changing it.
Key takeaways- Rank, number, top N
- Running totals, LAG
- Moving averages, NTILE