codejump
HardSQL

Compute a Seven-Day Revenue Window

SQLDatabasesCalendar Window

For every distinct day present in sales, return day, daily_cents, rolling_cents. The rolling sum includes all sales between day - 6 calendar days and day inclusive. Missing dates contribute zero but do not produce output rows. Multiple sales on one day must first contribute to that day's total; negative adjustments are valid. This is a calendar window, not the last seven observed rows. Order by day.

Schema (all columns are NOT NULL unless explicitly nullable):

CREATE TABLE sales (sale_id INTEGER PRIMARY KEY, day TEXT NOT NULL, cents INTEGER NOT NULL);

Write one read-only SQLite query. The tables are already populated. Return exactly the requested columns and ordering, with no headers. Empty input produces no rows unless specified otherwise.

Example setup:

INSERT INTO sales VALUES (1,'2024-01-01',100),(2,'2024-01-01',20),(3,'2024-01-07',30),(4,'2024-01-08',40);

Expected output:

2024-01-01|120|120
2024-01-07|30|150
2024-01-08|40|70

Timebox: 9 minutes. Focus on correctness at boundaries and duplicate handling.

Sample tests

Test #1Inclusive day-six boundary
Input: "CREATE TABLE sales (sale_id INTEGER PRIMARY KEY, day TEXT NOT NULL, cents INTEGER NOT NULL);\nINSERT INTO sales VALUES (1,'2024-01-01',100),(2,'2024-01-01',20),(3,'2024-01-07',30),(4,'2024-01-08',40);"
Output: "2024-01-01|120|120\n2024-01-07|30|150\n2024-01-08|40|70"
Test #2Sparse dates
Input: "CREATE TABLE sales (sale_id INTEGER PRIMARY KEY, day TEXT NOT NULL, cents INTEGER NOT NULL);\nINSERT INTO sales VALUES (1,'2024-01-01',100),(2,'2024-02-01',20);"
Output: "2024-01-01|100|100\n2024-02-01|20|20"
← All challenges