Restaurant Growth

Restaurant Growth

What is this problem about?

The Restaurant Growth interview question is a SQL window function problem. Given a table of customer spending records by date, compute the moving average of a customer's total spending over a sliding 7-day window ending on each day (inclusive). Only return results for days that have a full 7-day window of data. The result should be rounded to 2 decimal places and ordered by date.

Why is this asked in interviews?

Microsoft, Point72, Meta, Amazon, and Bloomberg ask this database problem because moving averages are fundamental to financial analytics, trend analysis, and time-series reporting. It tests whether candidates can use SQL window functions (SUM() OVER) with a ROWS/RANGE clause to implement sliding aggregations efficiently, rather than resorting to correlated subqueries which are much slower.

Algorithmic pattern used

The pattern is SQL window function with a RANGE frame. Use SUM(amount) OVER (ORDER BY visited_on RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) to compute the 7-day rolling sum. Divide by 7 for the average. Filter out rows where the window doesn't have a full 7 days (i.e., where visited_on < MIN(visited_on) + 6). A CTE makes this readable:

WITH daily AS (
  SELECT visited_on, SUM(amount) AS day_total
  FROM Customer
  GROUP BY visited_on
),
rolling AS (
  SELECT visited_on,
         SUM(day_total) OVER (ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS window_sum,
         COUNT(*) OVER (ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS day_count
  FROM daily
)
SELECT visited_on, window_sum AS amount, ROUND(window_sum / 7.0, 2) AS average_amount
FROM rolling
WHERE day_count = 7
ORDER BY visited_on;

Example explanation

Daily totals: Jan 1: $50, Jan 2:$70, Jan 3: $80, ..., Jan 7:$90, Jan 8: $60.

7-day window ending Jan 7: sum of Jan 1–7. Average = total / 7, rounded to 2 decimal places. 7-day window ending Jan 8: sum of Jan 2–8.

Days Jan 1–6 are excluded (incomplete 7-day windows).

Common mistakes candidates make

• Not aggregating daily first when multiple customers visit on the same day, inflating the sum.
• Using RANGE BETWEEN with interval dates instead of ROWS BETWEEN when data has exactly one row per day.
• Forgetting to exclude the first 6 days (incomplete windows) from the result.
• Dividing by the window size (7) before verifying the window is full.

Interview preparation tip

For the Restaurant Growth coding problem, the database interview pattern is sliding window aggregation using SQL window functions. Practice the ROWS BETWEEN N PRECEDING AND CURRENT ROW syntax — it appears in many financial and analytics SQL interviews. Point72 and Bloomberg interviewers often ask about the difference between ROWS and RANGE frames — be ready to explain: ROWS counts physical rows, RANGE groups rows with identical order values.