AI Hints
Homeโ€บSQL Tutorialโ€บRunning Totals and Trends
SQL ยท Lesson 69 of 126

Practice: Who Brings the Money?

NTILE on real spending.

โฑ 2 min readโ–ถ Video at 1:37:22๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

Practice: Who Brings the Money?
From the video: Practice: Who Brings the Money?
  • 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.

โœ๏ธ Exercise: Spending quarters

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
solution.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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.

Quick check
Compare each month with the previous month

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
Next up: Creating Tables โ€” CREATE TABLE