Restaurant Growth (LeetCode 1321) — SQL Interview Mastery

This chapter explores an advanced SQL problem focused on calculating rolling 7-day moving averages and cumulative sums using window-like logic and correlated subqueries in MySQL.

1. Problem Overview & Schema Setup

As a restaurant owner analyzing possible business expansion, you are provided with a customer table containing daily transaction records. The objective is to compute the moving average of customer payments over a 7-day window (specifically: current day plus the preceding 6 days).

Table Schema: customer

Note: The primary key of the table is the composite combination of (customer_id, visited_on). Multiple customers can visit on the same day, and a single customer can visit multiple times across different days.

2. Core Mental Model: The 7-Day Rolling Window

To compute a moving average or moving sum over a trailing 7-day period, we cannot simply aggregate the entire table. Each row in our output represents a specific target date and must aggregate data spanning exactly 7 calendar days.

Real-Life Analogy: The Sliding Postal Window

Imagine you are tracking daily postal deliveries through a moving 7-day frame. On Day 7, your frame looks backward to include Day 1 through Day 7. On Day 8, your frame slides forward by one day, dropping Day 1 and now encompassing Day 2 through Day 8. The start date of your output report does not begin on the very first day of recorded history (1st Jan), but rather on the 7th day (7th Jan), because that is the first day that has a complete 6-day history preceding it.

3. Step 1: Determining the Valid Output Date Range (visited_on)

We must first determine which dates should appear in our final result set.

• The earliest recorded visit in our table (MIN(visited_on)) serves as our baseline.
• Because we need a full 7-day window (current day + 6 prior days), our output cannot start until MIN(visited_on) + 6 days.

SQL Implementation for Dates

SELECT visited_on
FROM customer C
WHERE visited_on >= (
    SELECT DATE_ADD(MIN(visited_on), INTERVAL 6 DAY)
    FROM customer
)
GROUP BY visited_on
ORDER BY visited_on ASC;

Why GROUP BY visited_on? Since multiple customers can visit on the same calendar day, the raw customer table contains duplicate visited_on entries. Grouping by visited_on ensures each date appears exactly once in our primary tracking list.

4. Step 2: Calculating the 7-Day Moving Sum (amount)

For each valid visited_on date in our outer query, we need to calculate the sum of amount from visited_on - 6 days up to visited_on.

Subquery Date Range Mechanics

We utilize MySQL's DATE_SUB() function paired with the BETWEEN operator:

• Upper Bound: C.visited_on (the current date in the outer iteration)
• Lower Bound: DATE_SUB(C.visited_on, INTERVAL 6 DAY) (6 days prior to the current date)

SELECT 
    C.visited_on,
    (
        SELECT SUM(amount)
        FROM customer
        WHERE visited_on BETWEEN DATE_SUB(C.visited_on, INTERVAL 6 DAY) AND C.visited_on
    ) AS amount
FROM customer C
WHERE C.visited_on >= (
    SELECT DATE_ADD(MIN(visited_on), INTERVAL 6 DAY)
    FROM customer
)
GROUP BY C.visited_on
ORDER BY C.visited_on ASC;

5. Step 3: Computing the Rounded Moving Average (average_amount)

The final required column is average_amount, defined as the 7-day total amount divided by 7, rounded to 2 decimal places.

We can reuse our moving sum subquery logic, divide the result by 7.0 (to ensure floating-point division), and wrap it inside MySQL's ROUND(val, 2) function.

SELECT 
    C.visited_on,
    (
        SELECT SUM(amount)
        FROM customer
        WHERE visited_on BETWEEN DATE_SUB(C.visited_on, INTERVAL 6 DAY) AND C.visited_on
    ) AS amount,
    ROUND(
        (
            SELECT SUM(amount)
            FROM customer
            WHERE visited_on BETWEEN DATE_SUB(C.visited_on, INTERVAL 6 DAY) AND C.visited_on
        ) / 7, 
        2
    ) AS average_amount
FROM customer C
WHERE C.visited_on >= (
    SELECT DATE_ADD(MIN(visited_on), INTERVAL 6 DAY)
    FROM customer
)
GROUP BY C.visited_on
ORDER BY C.visited_on ASC;

6. Complete End-to-End Execution Flow

Below is the step-by-step data transformation pipeline executed by the final query:

7. Alternative Approaches & Performance Considerations

8. Summary and Key Takeaways

• Targeted Window Aggregations: Moving average problems require dynamic date ranges (BETWEEN date - 6 AND date), preventing the use of simple static group-bys.
• Handling Baseline Offsets: Use DATE_ADD(MIN(visited_on), INTERVAL 6 DAY) to accurately isolate the starting row where a full 7-day historical window becomes available.
• Duplicate Date Management: Always include GROUP BY visited_on in your outer query when dealing with tables where multiple transactions occur on the same calendar day.
• Precision Rounding: Wrap division results in ROUND(val, 2) to meet strict decimal formatting requirements in interview test cases.
• Floating-Point Division: Divide sums by 7.0 instead of integer 7 to prevent integer truncation bugs in SQL dialects.