Cumulative Sales Over Time with Lag
HardSQLPractice library · +30 XPrunning totalssqlwindow functions
A retail company wants to track the cumulative daily sales for each product and understand the day-over-day sales change. The data includes daily sales records for various products.
Tables:
products:product_id(INT, PK),product_name(VARCHAR(50))sales:sale_id(INT, PK),product_id(INT, FK),sale_date(DATE),amount(NUMERIC(10,2))
Return:
product_namesale_datedaily_sales_amount: The sales amount for that specific product on that specific date.cumulative_sales: The running total of sales for that product up to and including thesale_date.previous_day_sales_diff: The difference betweendaily_sales_amountand thedaily_sales_amountof the previous recorded sale for that product. If there is no previous sale for the product, this should be NULL.
Order the results by product_name, then sale_date.
Tables
CREATE TABLE products (
product_id INTEGER PRIMARY KEY NOT NULL,
product_name VARCHAR(50) NOT NULL
);
CREATE TABLE sales (
sale_id INTEGER PRIMARY KEY NOT NULL,
product_id INTEGER NOT NULL,
sale_date DATE NOT NULL,
amount NUMERIC(10,2) NOT NULL
);Example data
INSERT INTO products (product_id, product_name) VALUES (1, 'Laptop'), (2, 'Mouse'), (3, 'Keyboard'), (4, 'Monitor'); INSERT INTO sales (sale_id, product_id, sale_date, amount) VALUES (101, 1, DATE '2025-01-01', 1200.00), (102, 2, DATE '2025-01-01', 25.00), (103, 1, DATE '2025-01-02', 1250.00), (104, 3, DATE '2025-01-02', 75.00), (105, 2, DATE '2025-01-03', 23.00), (106, 1, DATE '2025-01-03', 1180.00), (107, 4, DATE '2025-01-03', 300.00), (108, 1, DATE '2025-01-05', 1300.00);
Your query is also checked on hidden datasets with different data.
Hints
-- Write your SQL query here
SELECT
p.product_name,
s.sale_date,
s.amount AS daily_sales_amount
FROM
sales s
JOIN
products p ON s.product_id = p.product_id
Your query runs on the example tables shown with the problem, then on hidden datasets when you submit.
Write standard SQL. MySQL habits work too: IFNULL, IF, YEAR(), DATE_FORMAT, GROUP_CONCAT, `backticks` and LIMIT 5, 10. It runs on PostgreSQL, so integer division keeps whole numbers (5 / 2 = 2): write 5.0 / 2 for 2.5.