Company: Shopify | Difficulty: hard
Shopify merchants want to monitor the recent sales performance of every listed product.
For every product and every day from March 1 through March 31, 2025, calculate the daily_revenue and the trailing_30_day_avg revenue ending on that day (the current day plus the previous 29 calendar days).
daily_revenue
trailing_30_day_avg
Products without sales on a calendar day must still appear with a daily_revenue of 0.00. Multiple sales for the same product on the same day must be combined. Treat a NULL revenue value as 0.00.
0.00
NULL
revenue
For the trailing average calculation, you must average across exactly 30 calendar days. Missing sales days contribute 0.00 to the total, meaning the sum is always divided by exactly 30. 2025-01-31 is included in the input data specifically to provide a full 30 days of lookback data for the first reporting day. Only March dates should appear in the final result.
2025-01-31
Return the product_id, sales_date, daily_revenue, and trailing_30_day_avg rounded to two decimal places. Sort by product_id descending and sales_date descending.
product_id
sales_date
products
product_name
product_sales
sale_id
sale_date
The result contains every product_id for every March calendar day, including days without sales. For product 101, the 2025-03-01 daily_revenue is 50.00, while 2025-03-02 has zero revenue because no sale occurred. The trailing average for 2025-03-01 includes 2025-01-31 through 2025-03-01, which is exactly 30 calendar days — for product 101 that is 100.00 (from 2025-01-31) plus 50.00 (from 2025-03-01) averaged over exactly 30 days (sum divided by 30), giving 5.00. Product 103 remains present even though its only recorded revenue is NULL, which contributes zero.
101
2025-03-01
50.00
2025-03-02
100.00
5.00
103
DROP TABLE IF EXISTS product_sales; DROP TABLE IF EXISTS products; CREATE TABLE products ( product_id INT, product_name VARCHAR(100) ); CREATE TABLE product_sales ( sale_id INT, product_id INT, sale_date DATE, revenue DECIMAL(10,2) ); INSERT INTO products VALUES (101, 'Aurora Lamp'), (102, 'Nimbus Chair'), (103, 'Orbit Desk'); INSERT INTO product_sales VALUES (1, 101, '2025-01-31', 100.00), (2, 101, '2025-03-01', 50.00), (3, 101, '2025-03-03', 70.00), (4, 102, '2025-03-02', 200.00), (5, 102, '2025-03-02', 50.00), (6, 103, '2025-02-15', NULL);
← All SQL Challenges · Shopify Questions · Learn SQL Free