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-calendar-day average revenue ending on that day. The 30-day period includes the current day and the previous 29 calendar days. Products without sales on a calendar day must still appear with daily revenue of zero. January 31 is included because it is needed to calculate the first reporting day. Only March dates should appear in the result. Multiple sales for the same product on the same day must be combined. Treat a NULL revenue value as zero. Return the product ID, sales date, daily revenue, and 30-day average revenue rounded to two decimal places. Sort by product ID descending and sales date descending. ### Table Schema | Table | Column | Type | |---|---|---| | products | product_id | integer | | products | product_name | varchar(100) | | product_sales | sale_id | integer | | product_sales | product_id | integer | | product_sales | sale_date | date | | product_sales | revenue | decimal(10,2) | ### Example Input: products | product_id | product_name | |---:|---| | 101 | Aurora Lamp | | 102 | Nimbus Chair | | 103 | Orbit Desk | ### Example Input: product_sales | sale_id | product_id | sale_date | revenue | |---:|---:|---|---:| | 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 | ### Example Output Explanation The result contains every product for every March calendar day, including days without sales. For product 101, the March 1 daily revenue is 50.00, while March 2 has zero revenue because no sale occurred. The trailing average for March 1 includes January 31 through March 1, which is exactly 30 calendar days. Product 103 remains present even though its only recorded revenue is NULL, which contributes zero.
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);