Highest Spending DayCompany: Spotify | Difficulty: medium

Spotify's commerce analytics team wants to identify the day on which each product generated the highest total spending.

For every product_id, combine all transactions from the same calendar date and return the product_id, the date on which it occurred (with no time component) as transaction_date, and the total daily spend as total_spend. Ignore transactions with a missing transaction_date or a missing spend value. If multiple dates have the same highest total for a product, choose the most recent date.

Sort the final results by product_id in descending order.

Table Schema

Table	Column	Type
`transactions`	`transaction_id`	integer
`transactions`	`product_id`	integer
`transactions`	`transaction_date`	timestamp
`transactions`	`spend`	decimal(10,2)

Example Input: `transactions`

transaction_id	product_id	transaction_date	spend
1	101	2022-12-29 10:15:00	1200.00
2	101	2022-12-29 14:30:00	945.32
3	101	2022-12-31 12:00:00	2145.32
4	102	2022-12-30 09:00:00	800.00
5	102	2022-12-31 16:45:00	1250.00

Example Output Explanation

Product 101 has total spending of 2145.32 on December 29 and 2145.32 on December 31. Because the totals tie, December 31 is selected because it is the more recent date. Product 102 has its highest total spending on December 31.

SchemaDROP TABLE IF EXISTS transactions; CREATE TABLE transactions ( transaction_id INT, product_id INT, transaction_date TIMESTAMP, spend DECIMAL(10,2) ); INSERT INTO transactions VALUES (1, 101, '2022-12-29 10:15:00', 1200.00), (2, 101, '2022-12-29 14:30:00', 945.32), (3, 101, '2022-12-31 12:00:00', 2145.32), (4, 102, '2022-12-30 09:00:00', 800.00), (5, 102, '2022-12-31 16:45:00', 1250.00);← All SQL Challenges · Spotify Questions · Learn SQL Free