Slot Machine Streaks

An arcade slot machine logs each spin's winning segment in spin_log. Spins are numbered in order, but voided spins are deleted, so spin_id can skip numbers. A skipped number breaks a streak: spins 4 and 6 are not consecutive.

Find every segment that landed on at least three consecutive spins (spin ids n, n+1, n+2 all present and all showing that segment).

Return one column, segment, each value once, ordered alphabetically. If no streak exists, return no rows.

Tables

spin_log — One row per valid spin. Rows for voided spins have been deleted.

Example

spin_log

Expected output

Return the rows in exactly the order described. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Window Functions · problem 37 of 70 · SQLite

The idea

Anchor every possible streak on its first spin a. A streak exists when the rows with ids a.spin_id + 1 and a.spin_id + 2 both exist and show the same segment. Two self joins express that directly, and because they match on id arithmetic, a deleted spin makes the join fail, which is exactly the rule that a gap breaks a streak.

A streak of five spins has three valid starting points, and a segment can streak at different times, so DISTINCT is needed to list each segment once.

The window version reads the previous and next row with LAG and LEAD. Those return the neighbouring row, not the neighbouring spin number, so it also fetches the neighbours' ids and requires them to be exactly one less and one more. Without that check, spins 2, 4 and 5 would look like a run.

Solution

SELECT DISTINCT a.segment
FROM spin_log AS a
JOIN spin_log AS b ON b.spin_id = a.spin_id + 1 AND b.segment = a.segment
JOIN spin_log AS c ON c.spin_id = a.spin_id + 2 AND c.segment = a.segment
ORDER BY a.segment;

Another way

WITH neighbours AS (
  SELECT
    spin_id,
    segment,
    LAG(spin_id)  OVER w AS prev_id,
    LAG(segment)  OVER w AS prev_segment,
    LEAD(spin_id) OVER w AS next_id,
    LEAD(segment) OVER w AS next_segment
  FROM spin_log
  WINDOW w AS (ORDER BY spin_id)
)
SELECT DISTINCT segment
FROM neighbours
WHERE prev_id = spin_id - 1 AND prev_segment = segment
  AND next_id = spin_id + 1 AND next_segment = segment
ORDER BY segment;

Why common answers fail

Compares neighbouring rows without checking their ids, so a run that spans a voided spin is counted as consecutive.

WITH neighbours AS (
  SELECT segment,
         LAG(segment)  OVER (ORDER BY spin_id) AS prev_segment,
         LEAD(segment) OVER (ORDER BY spin_id) AS next_segment
  FROM spin_log
)
SELECT DISTINCT segment FROM neighbours
WHERE prev_segment = segment AND next_segment = segment
ORDER BY segment;

Leaves out DISTINCT, so a streak of four or more (or two separate streaks) lists the same segment several times.

SELECT a.segment
FROM spin_log AS a
JOIN spin_log AS b ON b.spin_id = a.spin_id + 1 AND b.segment = a.segment
JOIN spin_log AS c ON c.spin_id = a.spin_id + 2 AND c.segment = a.segment
ORDER BY a.segment;

Counts how often a segment appears overall, ignoring whether the appearances are consecutive.

SELECT segment FROM spin_log GROUP BY segment HAVING COUNT(*) >= 3 ORDER BY segment;

What the interviewer is checking

The interviewer wants to see whether you define "consecutive" precisely, by id arithmetic rather than row order, and deduplicate the answer.

Mistakes to avoid

• Assuming ids have no gaps when using LAG/LEAD.
• Forgetting DISTINCT for runs longer than three.
• Counting total occurrences instead of consecutive ones.

Follow-ups to expect

• Find runs of at least K for any K (gaps-and-islands: group by spin_id - ROW_NUMBER() OVER (PARTITION BY segment ORDER BY spin_id)).
• Report the start id and length of every streak (same island grouping with MIN and COUNT).
• Treat rows as consecutive by order, ignoring gaps (plain LAG/LEAD without the id check).

In real work

Detecting repeated readings, such as a sensor stuck on one value or three failed logins in a row, is the same consecutive-run pattern.

Practice next