Restaurant Growth - Practice Coding Problems

Restaurant Growth - Problem

Database Medium

Restaurant Growth Analysis

You are the owner of a popular restaurant and want to analyze your business growth for potential expansion. Your restaurant has been busy enough to guarantee at least one customer every day.

Given a Customer table with transaction data, you need to calculate the 7-day moving average of daily revenue. For each day, compute the average amount paid by customers over a 7-day window (current day + 6 previous days).

Table Schema:

Column Name	Type
customer_id	int
name	varchar
visited_on	date
amount	int

Requirements:

Primary key: (customer_id, visited_on)
Calculate moving average for 7-day windows
Round average_amount to 2 decimal places
Return results ordered by visited_on ascending
Only include dates that have a complete 7-day window

Input & Output

example_1.sql — Basic Restaurant Data

$ Input: Customer table: | 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 |

› Output: | visited_on | amount | average_amount | |--------------|--------|----------------| | 2019-01-07 | 860 | 122.86 | | 2019-01-08 | 840 | 120.00 | | 2019-01-09 | 840 | 120.00 |

💡 Note: Day 7 is the first day with a complete 7-day window (days 1-7). The amount is 100+110+120+130+110+140+150=860, and average is 860/7=122.86. For day 8, we exclude day 1 and include day 8: 110+120+130+110+140+150+80=840, average=120.00.

example_2.sql — Multiple Customers Same Day

$ Input: Customer table: | customer_id | name | visited_on | amount | |-------------|---------|--------------|--------| | 1 | Alice | 2019-01-01 | 50 | | 2 | Bob | 2019-01-01 | 75 | | 3 | Carol | 2019-01-02 | 100 | | 4 | David | 2019-01-03 | 80 | | 5 | Eve | 2019-01-04 | 90 | | 6 | Frank | 2019-01-05 | 60 | | 7 | Grace | 2019-01-06 | 70 | | 8 | Henry | 2019-01-07 | 85 |

› Output: | visited_on | amount | average_amount | |--------------|--------|----------------| | 2019-01-07 | 510 | 72.86 |

💡 Note: Multiple customers on the same day are aggregated. Day 1 total: 50+75=125. The 7-day window sum is 125+100+80+90+60+70+85=510, giving an average of 72.86.

example_3.sql — Minimum Data Set

$ Input: Customer table: | customer_id | name | visited_on | amount | |-------------|-------|--------------|--------| | 1 | John | 2019-01-01 | 100 | | 2 | Jane | 2019-01-02 | 200 | | 3 | Bob | 2019-01-03 | 300 | | 4 | Alice | 2019-01-04 | 400 | | 5 | Carol | 2019-01-05 | 500 | | 6 | Dave | 2019-01-06 | 600 | | 7 | Eve | 2019-01-07 | 700 |

› Output: | visited_on | amount | average_amount | |--------------|--------|----------------| | 2019-01-07 | 2800 | 400.00 |

💡 Note: This shows the minimum case where we have exactly 7 days of data. Only day 7 appears in results with sum=100+200+300+400+500+600+700=2800 and average=400.00.

Visualization

Tap to expand

$100Day 2
$110Day 3
$120Day 4
$130Day 5
$110Day 6
$140Day 7
$150Day 8
$807-Day Window: Days 1-7Total: $860 | Average: $122.86Window Function MagicSUM() OVER (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)Calculates all windows efficiently in one pass

Understanding the Visualization

Daily Aggregation

First, sum up all customer payments for each day to get daily totals

Sliding Window

For each day, look back 6 days plus current day to create 7-day windows

Moving Average

Calculate sum and average for each window, revealing growth trends

Key Takeaway

🎯 Key Insight: Window functions eliminate the need for expensive subqueries by calculating sliding windows in a single, optimized database operation.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

O(n log n) for sorting by date, then O(n) for window function calculation

⚡ Linearithmic

Space Complexity

O(n)

Space for daily aggregation and result set

⚡ Linearithmic Space

Constraints

1 ≤ customer_id ≤ 1000
1 ≤ amount ≤ 1000
At least one customer visits the restaurant every day
visited_on dates are within a reasonable range
Primary key constraint: (customer_id, visited_on) is unique

Asked in

a Amazon 45 ⊞ Microsoft 32 G Google 28 f Meta 22

The optimal solution uses SQL window functions with SUM() OVER (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) to efficiently calculate 7-day moving averages. First aggregate daily totals, then apply the sliding window function to compute running sums and averages in a single pass, avoiding expensive subqueries.

Common Approaches

Approach	Time	Space	Notes
✓ Window Functions (Optimal)	O(n log n)	O(n)	Use SQL window functions to calculate sliding window sums and averages efficiently in one pass
Brute Force (Correlated Subqueries)	O(n²)	O(n)	For each date, use a subquery to calculate the sum and average of the previous 7 days

Window Functions (Optimal) — Algorithm Steps

Aggregate daily totals by grouping customer transactions by visited_on
Use ROW_NUMBER() to identify which dates have complete 7-day windows
Apply SUM() OVER(ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) for sliding window
Calculate average by dividing window sum by 7 and round to 2 decimal places
Filter results to only include dates with complete windows

Visualization

Tap to expand

Step-by-Step Walkthrough

Daily Aggregation

Group all transactions by date to get daily totals

Sliding Window

Use SUM() OVER() with ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

Single Pass

Calculate all 7-day windows in one efficient database operation

Code -

solution.c — C

// SQL Query for Restaurant Growth (Window Functions - Optimal)
char* query = "\
WITH daily_totals AS (\
    SELECT \
        visited_on,\
        SUM(amount) as day_amount\
    FROM Customer\
    GROUP BY visited_on\
),\
windowed_data AS (\
    SELECT \
        visited_on,\
        day_amount,\
        SUM(day_amount) OVER (\
            ORDER BY visited_on \
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW\
        ) as amount,\
        ROW_NUMBER() OVER (ORDER BY visited_on) as row_num\
    FROM daily_totals\
)\
SELECT \
    visited_on,\
    amount,\
    ROUND(amount / 7.0, 2) as average_amount\
FROM windowed_data\
WHERE row_num >= 7\
ORDER BY visited_on;";

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

O(n log n) for sorting by date, then O(n) for window function calculation

⚡ Linearithmic

Space Complexity

O(n)

Space for daily aggregation and result set

⚡ Linearithmic Space

Constraints

1 ≤ customer_id ≤ 1000
1 ≤ amount ≤ 1000
At least one customer visits the restaurant every day
visited_on dates are within a reasonable range
Primary key constraint: (customer_id, visited_on) is unique

42.5K Views

High Frequency

~25 min Avg. Time

1.9K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Window Functions (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler