Rolling Weekly Revenue Trend Report

MEDIUM20 pts

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:
1. visited_on
2. amount
3. average_amount
Sort by visited_on in ascending order.

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

customer =

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

Rolling Weekly Revenue Trend Report

Hints

Rolling Weekly Revenue Trend Report

Hints