Company: Apple | Difficulty: hard
Apple's App Store team defines a power purchaser as a customer who made at least 3 in-app purchases in every one of three specific calendar months: April, May, and June 2023. A customer must clear the 3-purchase minimum in all three months to qualify — falling short in even one month, or having no purchases at all in one of the months, disqualifies them.
Using the purchases and users tables, identify every power purchaser. For each one, report their user_id, email, and total_amount_spent — the sum of the amount column across every purchase they made between April 1 and June 30, 2023 (inclusive), regardless of which individual purchases counted toward the 3-per-month minimum.
purchases
users
user_id
email
total_amount_spent
amount
A purchase row with a NULL amount still counts toward that month's purchase total, but contributes 0 to total_amount_spent. The same applies to a purchase with an amount of 0.00 (for example, an in-app item redeemed with promotional credit) — it still counts as a purchase, and adds 0 to the revenue total. Round total_amount_spent to 2 decimal places.
NULL
0
0.00
Order the results by total_amount_spent descending, breaking ties by user_id ascending.
join_date
purchase_id
purchase_date
user_id 601 made 3 purchases in April (one priced at 0.00), 4 in May, and 3 in June — every one of the three months clears the 3-purchase minimum, so 601 qualifies, with a total_amount_spent of 28.91 across all 10 purchases. user_id 602 made 3 purchases in April and 3 in May, but only 2 in June, falling short of the minimum for that month, so 602 does not qualify and is excluded entirely.
601
28.91
602
DROP TABLE IF EXISTS purchases; DROP TABLE IF EXISTS users; CREATE TABLE users (user_id INT, join_date DATE, email VARCHAR(255)); CREATE TABLE purchases (purchase_id INT, user_id INT, purchase_date DATE, amount DECIMAL(10,2)); INSERT INTO users VALUES (601, '2022-03-14', 'j.alvarado@icloud.com'), (602, '2021-08-02', 's.moreau@icloud.com'); INSERT INTO purchases VALUES (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);
← All SQL Challenges · Apple Questions · Learn SQL Free