Abstract / Summary

Given monthly customer counts, determine the month when the restaurant served the most customers for the first time.

Note

The full problem description, including helpful examples, can be found on LeetCode.

This problem is ranked as

and has an acceptance rate of about 59% (400k+ submissions).

Typical patterns to solve this problem:

• Database

Every single line of code was written by me, 100% from scratch, between August 2023 and August 2024.
However, two years later I set up this website and used various AI tools to generate most of the walkthrough / explanations below.

See this dedicated page for more details.

Overview

The script builds a rolling weekly report of customer spending. It first collapses raw transaction rows into daily totals, then computes a seven-day window sum and average for each day, and finally limits the output to dates that are at least six days after the earliest recorded day. The query is expressed with common table expressions (CTEs) to keep intermediate results readable and reusable.

Key Steps

Step 1: Aggregating Daily Amounts

The first CTE, days, groups the source table by the calendar date and adds up the amount column. This produces one row per visited_on with the total amount spent on that day.

WITH days AS (
    SELECT visited_on,
           SUM(amount) AS amount
      FROM Customer
     GROUP BY visited_on
)

By performing the aggregation early, the later join operates on a reduced data set, which limits the amount of data that must be scanned repeatedly.

Step 2: Rolling Weekly Summation

The second CTE, week, joins the days CTE to itself. For each reference day d1.visited_on, it selects all d2 rows whose dates fall within the six-day interval that starts at d2.visited_on. The condition d1.visited_on >= d2.visited_on AND d1.visited_on <= DATE_ADD(d2.visited_on, INTERVAL 6 DAY) ensures that every day contributes to the totals of the seven-day window that ends on that day. The query then aggregates the matching rows, producing the total amount for the window and the average amount per day (total divided by 7, rounded to two decimals).

, week AS (
    SELECT d1.visited_on,
           SUM(d2.amount) AS amount,
           ROUND(SUM(d2.amount) / 7, 2) AS average_amount
      FROM days d1,
           days d2
     WHERE d1.visited_on >= d2.visited_on
       AND d1.visited_on <= DATE_ADD(d2.visited_on, INTERVAL 6 DAY)
     GROUP BY d1.visited_on
)

Because the join is performed on the already aggregated daily totals, the volume of rows processed in this self-join is dramatically smaller than if the original transaction table were used directly.

Step 3: Filtering Based on Date Range

The final SELECT pulls the columns from the week CTE and applies a filter that discards the first six days of the dataset. The subquery (SELECT MIN(visited_on) FROM days) returns the earliest date present in the data, and DATEDIFF(visited_on, …) computes the offset of each row from that start. Only rows where the offset is six or greater are kept, guaranteeing that each reported weekly window has a full seven-day history behind it.

SELECT visited_on,
       amount,
       average_amount
  FROM week
 WHERE 6 <= DATEDIFF(visited_on,
                     (SELECT MIN(visited_on) FROM days))

This filter prevents the average calculation from being skewed by incomplete windows at the beginning of the series.

Edge Cases Handled

The logic gracefully deals with days that have no transactions. Since the days CTE only contains dates that appear in the source table, a missing date simply does not contribute to any window. When a window includes such a gap, the SUM(d2.amount) adds zero for that day, and the average still divides by seven, reflecting the intended per-day average over a full week regardless of activity.

If the source data contains only a handful of distinct dates, the final filter ensures that no row is emitted until a full seven-day span can be assembled, avoiding division by a partial week.

The use of ROUND(..., 2) guarantees that the average amount is presented with a consistent two-decimal format, even when the raw division yields a longer fractional part.

Performance Optimizations

The query’s most notable optimization is the early aggregation in the days CTE. By collapsing potentially millions of transaction rows into a compact daily summary, the subsequent self-join operates on a dataset whose size is bounded by the number of distinct dates rather than the number of raw events.

The self-join uses a range predicate rather than a generic cross join, allowing the optimizer to apply index range scans if an index exists on visited_on. This reduces the number of row comparisons needed to locate matching windows.

The final filter leverages a scalar subquery that returns a single constant (the earliest date). Because this value does not change per row, the database can evaluate it once and reuse the result, avoiding repeated scans of the days CTE.

Overall, the structure of the query-aggregation first, then window calculation, then minimal filtering-minimizes I/O and CPU work while delivering the required weekly totals and averages.

Imagine a big toy box filled with building blocks, where each block has a date written on it and a number representing how many marbles came with that block. The first part of this script acts like a clever robot that picks up all the blocks from the same day and adds their marble counts together so that every day has one tidy total.

Once those daily totals are ready, the script starts a second job. It looks at each day and finds all the other days that happened during the week leading up to it. It gathers all the marbles from those seven days and adds them into one big pile for that specific day. It also does a little bit of math to find the average number of marbles by dividing that big pile by seven, which is like sharing the marbles equally among seven friends.

After all that adding and sorting, the script checks every day to see if there have been at least seven days worth of history in the toy box. If there are enough days to cover a full week, the script writes down the date, the total marbles for that final day, and that special average number. It ignores any days that happen at the very beginning when there wasn't a full week of history yet.

Several LLMs ranked my code quality on a scale from 1 (very poor) to 10 (outstanding):

Time: polynomial
As a rule of thumb, the maximum input size for one second execution time is

• factorial: up to 10 elements
• exponential: up to 20 elements
• polynomial: a few thousand elements
• loglinear: up to 100,000 elements
• linear: a few million elements
• logarithmic: a billion elements
• constant: practically unlimited

I wrote a basic crawler to find other solutions to that problem.
Obviously, I did that after I solved the problem myself.

In general, AlgoMonster and WalkCCC solutions are high-quality and always worth a look.
I haven't checked most of the other solutions, but they might be helpful as well.

And of course, the LeetCode forum is always a good place (or even the best) to look for alternative approaches.

(click on a column header to sort the table)

	author
author	solutions
www	AlgoMonster		C++	Java	Python	TS
	WalkCCC	SQL
	leetcode.ca	SQL
	Progiez		C++	Java	Python
github	suxrobGM	SQL
AlgoMonster	C++, Python, Java, TypeScript
WalkCCC	SQL
leetcode.ca	SQL
Progiez	C++, Java, Python
suxrobGM	SQL

There are a few extra pages giving you some insights into my setup:

• Tech Stack - my development environment and tools
• AI - how AI helped me create this website (NOT solving the problems)
• Progress - progression over time
• Quality - code quality