SQL Interview Question: Find App Store Power Purchasers with Two-Level GROUP BY

Some SQL problems look simple until you read the rules twice. This one is tagged Apple and rated hard, and it teaches a useful pattern: grouping the results of a grouping.

Let's solve it step by step.

The Problem in Plain English

What is a power purchaser?

A power purchaser is a customer who made at least 3 in-app purchases in each of these months:

• April 2023
• May 2023
• June 2023

Miss the mark in even one month, and the customer is out. No purchases in a month also means out.

What should the query return?

For every power purchaser, return:

• user_id
• email
• total_amount_spent: the sum of all their purchases from April 1 to June 30, 2023, rounded to 2 decimal places

Sort by total_amount_spent from high to low. If two users tie, the smaller user_id comes first.

Rules that trip people up

• A purchase with a NULL amount still counts as a purchase. It adds 0 to the total.
• A purchase with 0.00 also counts as a purchase and adds 0.
• The total includes every purchase in the window, not only the first 3 in each month.

The Tables

users

Column	Type
user_id	integer
join_date	date
email	string

purchases

Column	Type
purchase_id	integer
user_id	integer
purchase_date	date
amount	decimal

A Quick Example

users

user_id	join_date	email
601	2022-03-14	[e-mail]
602	2021-08-02	[e-mail]

purchases

purchase_id	user_id	purchase_date	amount
1	601	2023-04-03	0.99
2	601	2023-04-03	1.99
3	601	2023-04-01	0.00
4	601	2023-05-01	2.99
5	601	2023-05-02	1.99
6	601	2023-05-03	4.99
7	601	2023-05-04	0.99
8	601	2023-06-01	1.99
9	601	2023-06-02	2.99
10	601	2023-06-03	9.99
11	602	2023-04-03	1.99
12	602	2023-04-03	2.99
13	602	2023-04-01	0.99
14	602	2023-05-01	0.99
15	602	2023-05-02	1.99
16	602	2023-05-03	2.99
17	602	2023-06-01	1.99
18	602	2023-06-02	2.99

User 601 made 3 purchases in April (one was 0.00), 4 in May, and 3 in June. Every month reaches 3, so this user qualifies. The total across all 10 purchases is 28.91.

User 602 made 3 in April, 3 in May, but only 2 in June. That is one month short, so this user is out.

Expected output:

user_id	email	total_amount_spent
601	[e-mail]	28.91

How to Think About It

The trick is that we need two rounds of grouping:

1. Group by user and month to find which months have 3 or more purchases.
2. Group those good months by user to find who has all 3 months.

A regular WHERE can't do this, because we are filtering on counts. That is the job of HAVING.

Step-by-Step Solution

Step 1: Count purchases per user, per month

First, keep only the April to June 2023 rows. Then group by user and month.

SELECT
  user_id,
  EXTRACT(MONTH FROM purchase_date) AS purchase_month,
  COUNT(*) AS purchase_count
FROM purchases
WHERE purchase_date BETWEEN '2023-04-01' AND '2023-06-30'
GROUP BY user_id, EXTRACT(MONTH FROM purchase_date);

For our example, this gives:

user_id	purchase_month	purchase_count
601	4	3
601	5	4
601	6	3
602	4	3
602	5	3
602	6	2

COUNT(*) counts rows, so NULL amounts are counted too. That is what the problem wants.

Step 2: Keep only months with 3 or more purchases

Add a HAVING clause to drop weak months.

HAVING COUNT(*) >= 3

Now user 602's June row (count 2) is gone.

Step 3: Find users who passed in all 3 months

Below, monthly_counts stands for the result of Steps 1 and 2. In the final query, we wrap it in a CTE with that name.

Each remaining row is one qualifying month for one user. So if a user has exactly 3 rows, they passed April, May, and June.

SELECT user_id
FROM monthly_counts
GROUP BY user_id
HAVING COUNT(*) = 3;

A user with no purchases in a month never appears for that month, so they can't reach 3 rows. The "missing month" rule is handled automatically.

Step 4: Add up all spending and sort

Now join back to purchases and users. Joining to the list of power users keeps only qualifying customers. Then sum every purchase in the window, not just the ones that passed the test.

Final Query

WITH monthly_counts AS (
  SELECT
    user_id,
    EXTRACT(MONTH FROM purchase_date) AS purchase_month,
    COUNT(*) AS purchase_count
  FROM purchases
  WHERE purchase_date BETWEEN '2023-04-01' AND '2023-06-30'
  GROUP BY user_id, EXTRACT(MONTH FROM purchase_date)
  HAVING COUNT(*) >= 3
),
power_users AS (
  SELECT user_id
  FROM monthly_counts
  GROUP BY user_id
  HAVING COUNT(*) = 3
)
SELECT
  u.user_id,
  u.email,
  CAST(COALESCE(SUM(p.amount), 0) AS DECIMAL(10,2)) AS total_amount_spent
FROM purchases p
JOIN users u        ON u.user_id = p.user_id
JOIN power_users pu ON pu.user_id = p.user_id
WHERE p.purchase_date BETWEEN '2023-04-01' AND '2023-06-30'
GROUP BY u.user_id, u.email
ORDER BY total_amount_spent DESC, u.user_id ASC;

Two small notes on this version:

• SUM skips NULL values, so they add 0 as required. COALESCE is a safety net in case a user's amounts are all NULL.
• The CTEs (the WITH blocks) give each step a name, so the query reads from top to bottom.

Common Mistakes

• Using WHERE COUNT(*) >= 3. Aggregates can't go in WHERE. Use HAVING.
• Summing only the qualifying purchases. The total must cover every purchase in the window.
• Dropping NULL or 0.00 rows. They count toward the 3-purchase minimum.
• Checking the total count instead of each month. A user with 9 purchases in April alone should not pass.

Small Things to Watch

• EXTRACT(MONTH ...) works in PostgreSQL, MySQL, and DuckDB. In SQL Server, use MONTH(purchase_date).
• If your data covered more than one year, group by the year as well. Otherwise April 2022 and April 2023 would be mixed together.
• If purchase_date were a timestamp, BETWEEN ... AND '2023-06-30' would miss most of June 30. Use < '2023-07-01' instead.

Wrap-Up

The pattern here is worth remembering: group, filter with HAVING, then group again. It shows up in many interview questions about streaks, "every month" rules, and consistency checks.

Try changing the rules and see if you can adapt the query. For example, what if a user only needed 3 purchases in any two of the three months?