Number of Transactions per Visit

Every visit is a row in Visits; every transaction is a row in Transactions tagged with the same (user_id, date) as the visit that contained it — so a LEFT JOIN from Visits to Transactions followed by COUNT(transaction_date) turns each visit into a single integer (a no-transaction visit keeps its row but counts 0 because COUNT ignores the NULL the outer join produced), and then you simply tally how many visits landed on each integer 0, 1, 2, … up to the busiest visit.

The two stages

The whole problem is two independent transforms chained together. Getting them straight is the key insight:

1. Per-visit count — collapse the join down to one number per visit. This is the genuinely instructive step: the LEFT JOIN guarantees every visit survives, and COUNT(transaction_date) (counting a column, not *) treats the unmatched NULL as 0. Use COUNT(*) here and a zero-transaction visit would wrongly count as 1.
2. Histogram — take that bag of per-visit counts and ask “how many visits had exactly k?” for every k from 0 to the maximum. The trap is k-values that nobody hit (e.g. exactly 2 transactions): the spec demands they appear with visits_count = 0, so you cannot just GROUP BY the counts — you must generate the full dense range 0..max and left-join the histogram onto it.

Worked example, traced end to end

Take the canonical inputs (10 visits, 8 transactions):

Stage 1 yields the bag of per-visit counts {0,0,0,0, 1,1,1,1,1, 3}. The maximum is 3. Stage 2 generates the dense range 0,1,2,3 and tallies each — including 2, which no visit produced:

This output is verified against SQLite 3.51.

The query

Here is a portable, correct version. The per-visit count is unchanged from the original page (it was the strong part); the sequence is generated robustly with a recursive CTE, which works in PostgreSQL, MySQL 8+, SQL Server and SQLite:

WITH per_visit AS (
    SELECT COUNT(t.transaction_date) AS cnt
    FROM   Visits v
    LEFT JOIN Transactions t
           ON  v.user_id    = t.user_id
           AND v.visit_date = t.transaction_date
    GROUP BY v.user_id, v.visit_date
),
seq(n) AS (                          -- generate 0,1,2,...,max  reliably
    SELECT 0
    UNION ALL
    SELECT n + 1 FROM seq
    WHERE  n < (SELECT MAX(cnt) FROM per_visit)
)
SELECT seq.n               AS transactions_count,
       COUNT(per_visit.cnt) AS visits_count
FROM   seq
LEFT JOIN per_visit ON per_visit.cnt = seq.n
GROUP BY seq.n
ORDER BY seq.n;

The final COUNT(per_visit.cnt) counts a column (not *) for the same reason as stage 1: when a generated k matches no visit, the left join supplies NULL and COUNT skips it, yielding the required 0.

Why the original sequence generator is fragile

The widely-copied version generates the range like this:

WITH t AS (
    SELECT ROW_NUMBER() OVER() AS row_num FROM Transactions
    UNION
    SELECT 0
)

It assigns 1..N to the N rows of Transactions and unions in a 0, giving {0,1,…,N} where N = COUNT(*) FROM Transactions. But the required range is 0..M, where M is the maximum transactions in any single visit — and the total transaction count N is almost always larger than the busiest single visit M. So on this very dataset (8 transaction rows, busiest visit = 3) the idiom overshoots: it emits {0,1,…,8} — nine buckets — with spurious trailing rows (4,0),(5,0),…,(8,0) that are not in the answer (only 0..3). It does not “happen to be correct here” — it is wrong here. Sizing a sequence by a table's cardinality is simply the wrong tool: here N > M makes it overshoot; on any other “generate 0..K” problem where K exceeds the table's row count, the same idiom does the opposite and silently truncates. The truncation case, demonstrated:

-- base table has 3 rows, but we want 0..5
WITH t AS (SELECT ROW_NUMBER() OVER() AS row_num FROM Tiny  -- 3 rows
           UNION SELECT 0)
SELECT MAX(row_num) FROM t;   -- returns 3, NOT 5  ← wrong

It also wastes work: you materialise one row per transaction (millions of rows) just to read off the integers 1..N, when you only need 0..max(cnt) — a handful of values. The recursive CTE generates exactly the values you need and makes the upper bound, MAX(cnt), explicit in the code rather than smuggled in through a row count.

Pitfalls

• COUNT(*) instead of COUNT(column). The single most common bug. A zero-transaction visit survives the LEFT JOIN as one row full of NULLs; COUNT(*) counts that row as 1, so your “0 transactions” bucket vanishes and every count is off by one for empty visits.
• Forgetting the empty buckets. If you just GROUP BY the per-visit counts, value 2 (which no visit hit) is dropped from the output. The spec explicitly requires every integer from 0 to max, including ones with visits_count = 0. Generating the dense range is not optional polish — it is the requirement.
• Counting transactions globally instead of per visit. You must GROUP BY (user_id, visit_date), not by user. The histogram is over visits; a user who visits twice contributes two data points.
• Joining on user_id alone. A transaction belongs to a specific visit; the join key must be both user_id and the date. Drop the date and a user's transactions smear across all of their visits.
• Relying on row count to size a sequence. The ROW_NUMBER() OVER() FROM SomeTable trick is not a general number generator — it caps at the table's cardinality and its ordering with an empty OVER() is unspecified. Use a recursive CTE (or a dedicated numbers/tally table) when you need a guaranteed contiguous range.

Takeaways

• Two transforms, kept separate: first collapse each visit to one count (an outer join preserves zero-transaction visits), then build a histogram over those counts.
• COUNT(col) is the load-bearing trick on both joins: it converts the NULL from an unmatched LEFT JOIN into the 0 the problem needs.
• Dense output means generate the axis. When the result must include keys that no data row produced, synthesise the full key range and left-join your data onto it.
• Generate sequences explicitly. Prefer a recursive CTE bounded by a real expression (MAX(cnt)) over the ROW_NUMBER()-over-a-table hack, which only works when the range happens to fit inside a row count.

Problem from LeetCode 1565-series “Number of Transactions per Visit” (Hard). Sequence-generation analysis informed by the SQLite (3.51) and PostgreSQL documentation on recursive CTEs and by Joe Celko's discussion of auxiliary numbers tables in SQL for Smarties. The per-visit LEFT JOIN + COUNT(column) explanation is retained from the original lesson. All query outputs in this page were executed and verified on SQLite 3.51. Re-authored and deepened for this guide: the fragile ROW_NUMBER()-over-Transactions sequence generator was replaced with a robust recursive CTE, with a demonstrated failing case and a note on why the original happened to be correct here.