Split customers into four spend groups

Split our customers into four equal groups by total spend, with 1 for the biggest spenders. New tool: NTILE(4) OVER (ORDER BY spend DESC) deals the rows into 4 groups as evenly as it can, biggest first. With 6 customers, the first two groups get 2 each and the last two get 1.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

NTILE: equal groups

WITH s AS (
  SELECT customer_id, SUM(revenue) AS spend
  FROM orders
  GROUP BY customer_id
)
SELECT customer_id,
       spend,
       NTILE(4) OVER (ORDER BY spend DESC) AS spend_group
FROM s

Result:

customer_id	spend	spend_group
C101	204	1
C104	160	1
C106	150	2
C103	125	2
C105	95	3
C102	85	4

Step by step

1 is the biggest spenders.

WITH s AS (SELECT customer_id, SUM(revenue) AS spend FROM orders GROUP BY customer_id) SELECT customer_id, spend, NTILE(4) OVER (ORDER BY spend DESC) AS spend_group FROM s

No join.

WITH s AS (SELECT customer_id, SUM(revenue) AS spend FROM orders GROUP BY customer_id) SELECT customer_id, spend, NTILE(4) OVER (ORDER BY spend DESC) AS spend_group FROM s

Every customer gets a group.

WITH s AS (SELECT customer_id, SUM(revenue) AS spend FROM orders GROUP BY customer_id) SELECT customer_id, spend, NTILE(4) OVER (ORDER BY spend DESC) AS spend_group FROM s

A CTE for the totals, then NTILE(4) OVER (ORDER BY spend DESC).

WITH s AS (SELECT customer_id, SUM(revenue) AS spend FROM orders GROUP BY customer_id) SELECT customer_id, spend, NTILE(4) OVER (ORDER BY spend DESC) AS spend_group FROM s

Practice

Try each one in your head first, then open the answer.

1. Split customers into four groups by total spend, 1 for the biggest spenders. Show customer_id, spend and spend_group.

WITH s AS (SELECT customer_id, SUM(revenue) AS spend FROM orders GROUP BY customer_id) SELECT customer_id, spend, NTILE(4) OVER (ORDER BY spend DESC) AS spend_group FROM s

2. Split the orders into two halves by revenue, 1 for the bigger half. Show order_id, revenue and half.

SELECT order_id, revenue, NTILE(2) OVER (ORDER BY revenue DESC) AS half FROM orders

3. Split the orders into three groups by revenue, 1 for the biggest. Show order_id, revenue and grp.

SELECT order_id, revenue, NTILE(3) OVER (ORDER BY revenue DESC) AS grp FROM orders

4. Which customers are in the top spend group of four? Show customer_id and spend.

WITH s AS (SELECT customer_id, SUM(revenue) AS spend FROM orders GROUP BY customer_id), g AS (SELECT customer_id, spend, NTILE(4) OVER (ORDER BY spend DESC) AS spend_group FROM s) SELECT customer_id, spend FROM g WHERE spend_group = 1

5. Split each store's orders into two halves by revenue, 1 for the bigger half. Show store, order_id, revenue and half.

SELECT store, order_id, revenue, NTILE(2) OVER (PARTITION BY store ORDER BY revenue DESC) AS half FROM orders

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed