Rolling Weekly Revenue Trend Report

Medium

A restaurant analytics team is preparing an expansion proposal and needs a stable weekly revenue trend metric. You are given one transaction table: customer columns: customer_id: unique customer identifier name: customer display name visited_on: calendar date of visit (YYYY-MM-DD) amount: amount paid in that transaction row Data rule: (customer_id, visited_on) is unique. There is at least one transaction row for every date in the covered period. Task: Build a rolling 7-day revenue report. Computation details: First aggregate rows by visited_on to get daily_total_revenue. For each date d, compute: rolling_amount(d) = sum of daily_total_revenue for dates in [d-6, d] average_amount(d) = rolling_amount(d) / 7 Round average_amount to 2 decimal places. Output requirements: Return only dates where a complete 7-day window exists. Return exactly these columns in this order: visited_on amount average_amount Sort by visited_on in ascending order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

customer:
| customer_id | name    | visited_on | amount |
|-------------|---------|------------|--------|
| 1           | Jhon    | 2019-01-01 | 100    |
| 2           | Daniel  | 2019-01-02 | 110    |
| 3           | Jade    | 2019-01-03 | 120    |
| 4           | Khaled  | 2019-01-04 | 130    |
| 5           | Winston | 2019-01-05 | 110    |
| 6           | Elvis   | 2019-01-06 | 140    |
| 7           | Anna    | 2019-01-07 | 150    |
| 8           | Maria   | 2019-01-08 | 80     |
| 9           | Jaze    | 2019-01-09 | 110    |
| 1           | Jhon    | 2019-01-10 | 130    |
| 3           | Jade    | 2019-01-10 | 150    |

Output

[
  {"visited_on":"2019-01-07","amount":860,"average_amount":122.86},
  {"visited_on":"2019-01-08","amount":840,"average_amount":120.0},
  {"visited_on":"2019-01-09","amount":840,"average_amount":120.0},
  {"visited_on":"2019-01-10","amount":1000,"average_amount":142.86}
]

Explanation

Daily totals are computed first. For 2019-01-10, the 7-day window includes 2019-01-04 through 2019-01-10, with a total of 1000 and average 142.86.

Example 2

Input

customer:
| customer_id | name | visited_on | amount |
|-------------|------|------------|--------|
| 10          | A    | 2024-04-01 | 200    |
| 11          | B    | 2024-04-01 | 50     |
| 10          | A    | 2024-04-02 | 180    |
| 12          | C    | 2024-04-03 | 300    |
| 13          | D    | 2024-04-04 | 170    |
| 14          | E    | 2024-04-05 | 250    |
| 15          | F    | 2024-04-06 | 220    |
| 16          | G    | 2024-04-07 | 230    |

Output

[
  {"visited_on":"2024-04-07","amount":1600,"average_amount":228.57}
]

Explanation

Only one complete window exists (2024-04-01 through 2024-04-07). The first day has two customer rows and must be aggregated to 250 before rolling computation.

Example 3

Input

customer:
| customer_id | name | visited_on | amount |
|-------------|------|------------|--------|
| 1           | X    | 2025-01-01 | 100    |
| 1           | X    | 2025-01-02 | 200    |
| 2           | Y    | 2025-01-03 | 150    |
| 3           | Z    | 2025-01-04 | 100    |
| 2           | Y    | 2025-01-05 | 100    |
| 4           | W    | 2025-01-06 | 100    |

Output

[]

Explanation

There are only 6 dates, so no full 7-day window can be produced.

Constraints

1 <= number of rows in customer <= 100000
1 <= customer_id <= 1000000000
1 <= amount <= 1000000
name is a non-empty string
visited_on is a valid date in YYYY-MM-DD format
(customer_id, visited_on) is unique
There is at least one transaction row for each day in the covered date interval
Return rows only for dates with complete 7-day windows
Output columns must be exactly: visited_on, amount, average_amount
Round average_amount to 2 decimal places
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: visited_on, amount, average_amount

customer: | customer_id | name | visited_on | amount | |-------------|---------|------------|--------| | 1 | Jhon | 2019-01-01 | 100 | | 2 | Daniel | 2019-01-02 | 110 | | 3 | Jade | 2019-01-03 | 120 | | 4 | Khaled | 2019-01-04 | 130 | | 5 | Winston | 2019-01-05 | 110 | | 6 | Elvis | 2019-01-06 | 140 | | 7 | Anna | 2019-01-07 | 150 | | 8 | Maria | 2019-01-08 | 80 | | 9 | Jaze | 2019-01-09 | 110 | | 1 | Jhon | 2019-01-10 | 130 | | 3 | Jade | 2019-01-10 | 150 |

[ {"visited_on":"2019-01-07","amount":860,"average_amount":122.86}, {"visited_on":"2019-01-08","amount":840,"average_amount":120.0}, {"visited_on":"2019-01-09","amount":840,"average_amount":120.0}, {"visited_on":"2019-01-10","amount":1000,"average_amount":142.86} ]

customer: | customer_id | name | visited_on | amount | |-------------|------|------------|--------| | 10 | A | 2024-04-01 | 200 | | 11 | B | 2024-04-01 | 50 | | 10 | A | 2024-04-02 | 180 | | 12 | C | 2024-04-03 | 300 | | 13 | D | 2024-04-04 | 170 | | 14 | E | 2024-04-05 | 250 | | 15 | F | 2024-04-06 | 220 | | 16 | G | 2024-04-07 | 230 |

customer: | customer_id | name | visited_on | amount | |-------------|------|------------|--------| | 1 | X | 2025-01-01 | 100 | | 1 | X | 2025-01-02 | 200 | | 2 | Y | 2025-01-03 | 150 | | 3 | Z | 2025-01-04 | 100 | | 2 | Y | 2025-01-05 | 100 | | 4 | W | 2025-01-06 | 100 |