1321. Restaurant Growth

SQL 50 · Subqueries (day 6). Decode it with the hints, solve it on LeetCode, then compare with the reference solution in MySQLPostgreSQL.

MediumSolve it on LeetCode ↗

Your work

Status Notes Your solution (MySQLPostgreSQL)

Hints

Open one at a time. Try to finish with as few as you can.

1. What does one output row represent?

One day, from day 7 on.

2. Approach

First total per day (GROUP BY visited_on). Then SUM OVER ROWS BETWEEN 6 PRECEDING AND CURRENT ROW, average = sum / 7.

3. The trap

Aggregate per day first; a day can have many rows. Skip the first 6 days.

Reference solution

Tested on MySQL 8.0 and PostgreSQL 16. Switch dialect at the top of the page.

Show the MySQLPostgreSQL solution

WITH daily AS (
  SELECT visited_on, SUM(amount) AS amount
  FROM Customer
  GROUP BY visited_on
), w AS (
  SELECT visited_on,
    SUM(amount) OVER (ORDER BY visited_on
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS amount,
    ROW_NUMBER() OVER (ORDER BY visited_on) AS rn
  FROM daily
)
SELECT visited_on, amount, ROUND(amount / 7, 2) AS average_amount
FROM w
WHERE rn >= 7
ORDER BY visited_on;WITH daily AS (
  SELECT visited_on, SUM(amount) AS amount
  FROM Customer
  GROUP BY visited_on
), w AS (
  SELECT visited_on,
    SUM(amount) OVER (ORDER BY visited_on
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS amount,
    ROW_NUMBER() OVER (ORDER BY visited_on) AS rn
  FROM daily
)
SELECT visited_on, amount, ROUND(amount / 7.0, 2) AS average_amount
FROM w
WHERE rn >= 7
ORDER BY visited_on;

Patterns used

Window functions · Phrase finder