SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 133 of 250

Hard

Rank customer spending within signup sources

Your task

Return source, customer_id, spent_cents, and spend_rank for each customer. Count completed amounts only, with zero for customers who never completed an order. Rank higher totals first within each source, giving ties the same rank and leaving no gaps. Sort by source, rank, then customer ID.

Calculate each customer’s total before ranking them so the window sees one row per customer.

Sample data

Choose a table
idnamesignup_datesource
1Avery2025-01-02organic
2Casey2025-01-05email
3Jordan2025-01-12organic
4Morgan2025-02-01email
5Riley2025-02-10social
6Sam2025-02-20organic
7Taylor2025-03-01social
8Devon2025-02-07email

Expected output

sourcecustomer_idspent_centsspend_rank
email456001
email250002
email831003
organic165001
organic617002
organic303
social501
social701
Show a hint

Get customer totals with a LEFT JOIN and apply DENSE_RANK to those totals.

Window functions guide

Keep detail rows while summarizing

GROUP BY would return one row per genre. A window aggregate with PARTITION BY leaves every album in the result and gives each row its genre's average. This makes it easy to compare a row with its peers.

SELECT title, genre, price,
       AVG(price) OVER (PARTITION BY genre) AS genre_average
FROM albums;

Specify the window order and frame

The ORDER BY inside OVER defines the order of calculation. ROWS defines which neighboring rows contribute. This example adds the current sale to all earlier sales by ID. The last ORDER BY controls the order shown on screen.

SELECT id, quantity,
       SUM(quantity) OVER (
         ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_quantity
FROM sales
ORDER BY id;

Rank first, then filter

Window results are calculated too late for the same SELECT's WHERE clause. Compute a rank in a CTE, then filter the named result. ROW_NUMBER assigns one position per row. RANK gives ties the same position and may leave gaps.

WITH ranked AS (
  SELECT title, genre, ROW_NUMBER() OVER (
    PARTITION BY genre ORDER BY price, id
  ) AS position
  FROM albums
)
SELECT genre, title FROM ranked WHERE position = 1;
SQLiteCtrl / ⌘ + Enter

Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.

Results

Loading SQLite…