1321. Restaurant Growth
On LeetCode ->Problem¶
Given restaurant transactions, return each date starting from the 7th available day with:
- Total revenue over that date and the previous 6 days.
- Average daily revenue over those 7 days, rounded to 2 decimals.
- Results ordered by date.
Input:
# Customer
------------+--------+------------+-------
customer_id | name | visited_on | amount
------------+--------+------------+-------
1 | 'A' | 2019-01-01 | 10
2 | 'B' | 2019-01-02 | 20
3 | 'C' | 2019-01-03 | 30
4 | 'D' | 2019-01-04 | 40
5 | 'E' | 2019-01-05 | 50
6 | 'F' | 2019-01-06 | 60
7 | 'G' | 2019-01-07 | 70
8 | 'H' | 2019-01-08 | 80
9 | 'I' | 2019-01-08 | 20
Output:
-----------+--------+---------------
visited_on | amount | average_amount
-----------+--------+---------------
2019-01-07 | 280 | 40.00
2019-01-08 | 370 | 52.86
Key trick¶
Aggregate by day first, then apply a 7-row rolling window over daily totals.
Trap¶
- Do not roll over raw customer rows because multiple customers can visit on the same day.
- Do not divide by the number of rows; divide by 7 days.
- Do not output the first 6 days because their window is incomplete.
Why is it interesting?¶
It tests whether you recognize the correct grain of analysis before using window functions or rolling operations.
SQL solution¶
WITH daily_amount AS (
-- One row per day is required before computing a 7-day window.
SELECT
visited_on,
SUM(amount) AS daily_amount
FROM Customer
GROUP BY visited_on
),
rolling_amount AS (
SELECT
visited_on,
SUM(daily_amount) OVER (
ORDER BY visited_on
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS amount,
COUNT(*) OVER (
ORDER BY visited_on
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS days_in_window
FROM daily_amount
)
SELECT
visited_on,
amount,
ROUND(amount / 7.0, 2) AS average_amount
FROM rolling_amount
WHERE days_in_window = 7
ORDER BY visited_on;
Pandas solution¶
import pandas as pd
def restaurant_growth(customer: pd.DataFrame) -> pd.DataFrame:
# Aggregate to daily revenue first because several customers can visit the same day.
daily = (
customer
.groupby("visited_on", as_index=False)["amount"]
.sum()
.sort_values("visited_on")
)
# Compute the 7-day rolling sum on daily rows.
daily["amount"] = daily["amount"].rolling(window=7).sum()
# Keep only complete 7-day windows.
result = daily[daily["amount"].notna()].copy()
# Average is over 7 days, not over the number of transactions.
result["average_amount"] = (result["amount"] / 7).round(2)
result["amount"] = result["amount"].astype("int64")
return result[["visited_on", "amount", "average_amount"]].reset_index(drop=True)
import pandas as pd
import numpy as np
df = pd.DataFrame({"B": [0, 1, 2, np.nan, 4]})
df
# B
# 0 0.0
# 1 1.0
# 2 2.0
# 3 NaN
# 4 4.0
df.rolling(2).sum()
# B
# 0 NaN
# 1 1.0
# 2 3.0
# 3 NaN
# 4 NaN
Pytest test¶
import sqlite3
import pandas as pd
import pytest
from pandas.testing import assert_frame_equal
SQL_QUERY = """
WITH daily_amount AS (
SELECT
visited_on,
SUM(amount) AS daily_amount
FROM Customer
GROUP BY visited_on
),
rolling_amount AS (
SELECT
visited_on,
SUM(daily_amount) OVER (
ORDER BY visited_on
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS amount,
COUNT(*) OVER (
ORDER BY visited_on
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS days_in_window
FROM daily_amount
)
SELECT
visited_on,
amount,
ROUND(amount / 7.0, 2) AS average_amount
FROM rolling_amount
WHERE days_in_window = 7
ORDER BY visited_on;
"""
def restaurant_growth(customer: pd.DataFrame) -> pd.DataFrame:
daily = (
customer
.groupby("visited_on", as_index=False)["amount"]
.sum()
.sort_values("visited_on")
)
daily["amount"] = daily["amount"].rolling(window=7).sum()
result = daily[daily["amount"].notna()].copy()
result["average_amount"] = (result["amount"] / 7).round(2)
result["amount"] = result["amount"].astype("int64")
return result[["visited_on", "amount", "average_amount"]].reset_index(drop=True)
@pytest.mark.parametrize(
"rows, expected_rows",
[
(
[
[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],
],
[
["2019-01-07", 860, 122.86],
["2019-01-08", 840, 120.00],
["2019-01-09", 840, 120.00],
["2019-01-10", 1000, 142.86],
],
),
(
[
[1, "A", "2019-01-01", 10],
[2, "B", "2019-01-02", 20],
[3, "C", "2019-01-03", 30],
[4, "D", "2019-01-04", 40],
[5, "E", "2019-01-05", 50],
[6, "F", "2019-01-06", 60],
],
[],
),
(
[
[1, "A", "2019-01-01", 10],
[2, "B", "2019-01-02", 20],
[3, "C", "2019-01-03", 30],
[4, "D", "2019-01-04", 40],
[5, "E", "2019-01-05", 50],
[6, "F", "2019-01-06", 60],
[7, "G", "2019-01-07", 70],
[8, "H", "2019-01-08", 80],
[9, "I", "2019-01-08", 20],
],
[
["2019-01-07", 280, 40.00],
["2019-01-08", 370, 52.86],
],
),
],
)
def test_restaurant_growth_sql_and_pandas(rows, expected_rows):
columns = ["customer_id", "name", "visited_on", "amount"]
customer = pd.DataFrame(rows, columns=columns)
customer["visited_on"] = pd.to_datetime(customer["visited_on"])
expected = pd.DataFrame(
expected_rows,
columns=["visited_on", "amount", "average_amount"],
)
expected["visited_on"] = pd.to_datetime(expected["visited_on"])
expected["amount"] = expected["amount"].astype("int64")
expected["average_amount"] = expected["average_amount"].astype("float64")
pandas_result = restaurant_growth(customer)
assert_frame_equal(pandas_result, expected)
with sqlite3.connect(":memory:") as connection:
connection.execute(
"""
CREATE TABLE Customer (
customer_id INTEGER,
name TEXT,
visited_on TEXT,
amount INTEGER
);
"""
)
connection.executemany(
"""
INSERT INTO Customer (customer_id, name, visited_on, amount)
VALUES (?, ?, ?, ?);
""",
rows,
)
sql_result = pd.read_sql_query(SQL_QUERY, connection)
sql_result["visited_on"] = pd.to_datetime(sql_result["visited_on"])
sql_result["amount"] = sql_result["amount"].astype("int64")
sql_result["average_amount"] = sql_result["average_amount"].astype("float64")
assert_frame_equal(sql_result, expected)
Comment on my solution¶
Your SQL idea is close, but it has portability and correctness issues:
- It uses PostgreSQL interval syntax, so it is not valid SQLite.
- It rolls from every customer row, so duplicated dates produce duplicated intermediate rows and need
DISTINCTas a patch. OFFSET 6assumes exactly one row per day before filtering, which breaks when there are multiple visits on a day.- The robust approach is to aggregate by
visited_onfirst, then compute the rolling 7-day sum.
-- WORKS
WITH customer_rolling_amount AS (
SELECT
c1.visited_on,
(SELECT SUM(amount)
FROM Customer AS c2
WHERE c2.visited_on BETWEEN c1.visited_on - INTERVAL '6 days' AND c1.visited_on) AS amount
FROM Customer AS c1
)
SELECT DISTINCT
visited_on,
amount,
ROUND(amount / 7.0, 2) AS average_amount
FROM customer_rolling_amount
ORDER BY visited_on
LIMIT ALL OFFSET 6;