Total Hours Between Consecutive Events

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

hard

Seniority

L4

Asked in

Shopify

Problem

event_data holds one row per event with its event_type, event_timestamp and user_id, and a row with no user_id comes from an automated job, so it never sits on any timeline. Each event type is a single timeline that interleaves every user's events, and a quiet stretch is a gap of more than 30 days between a user event of that type and the very next one of that type, whoever triggered it. Report the total hours each type spent in quiet stretches.

Hours between state changes.

Tables

event_data

Columns: event_id INTEGER, event_type TEXT, user_id INTEGER, event_timestamp TEXT, payload TEXT, tags TEXT, properties TEXT

Expected output

Worked solution and explanation

This is a gap-length filter dressed up as an activity report. Here is the part nobody says out loud: summing every gap between consecutive events is a trick with no teeth, because the gaps telescope and the total is just the last timestamp minus the first. The 30-day bar is what gives the problem teeth: once only long gaps count, every individual gap has to exist as its own row, and that takes a partitioned LAG. The second trap is quieter. Automated rows (no user_id) must leave before the gaps are measured, because a job firing at the edge of a quiet month moves where that stretch starts or ends, and a filter applied afterwards is too late to put it back.

Why the shortcut stops working

Every gap counted

The sum collapses: (t2 - t1) + (t3 - t2) + ... equals MAX(event_timestamp) - MIN(event_timestamp) per type. A plain GROUP BY event_type answers it, and the window was never needed.

Only gaps over 30 days

Whether a gap counts depends on its own length, so the sum no longer telescopes. You need each adjacent pair as a row, test it against 720 hours, then total the survivors.

Building it

Step 1: Drop automated rows inside the subquery

WHERE user_id IS NOT NULL sits in the same SELECT as the window, so it runs before LAG is evaluated. Automated rows never enter any type's timeline, so they can neither split a long stretch nor serve as its first or last endpoint.

Step 2: Pair each event with its same-type predecessor

LAG(event_timestamp) OVER (PARTITION BY event_type ORDER BY event_timestamp) hands every row the previous timestamp in its own type's timeline. No user_id goes in the partition: every user's events share the type's timeline. The first event of each type gets NULL.

Step 3: Turn the difference into hours

JULIANDAY differences are in days, so multiply by 24 to get gap_hours. Thirty days is then 720, and comparing against 30 is the silent unit bug.

Step 4: Keep the long gaps and total them

The outer WHERE gap_hours > 720 drops short gaps and, for free, the NULL first gap, since a comparison with NULL is never true. SUM(gap_hours) grouped by event_type gives total_hours.

SELECT event_type, SUM(gap_hours) AS total_hours
FROM (
    SELECT
        event_type,
        (JULIANDAY(event_timestamp) - JULIANDAY(
            LAG(event_timestamp) OVER (
                PARTITION BY event_type
                ORDER BY event_timestamp
            )
        )) * 24 AS gap_hours
    FROM event_data
    WHERE user_id IS NOT NULL
) gaps
WHERE gap_hours > 720
GROUP BY event_type

Common follow-up questions

• How would you return the single longest quiet stretch per type, with the dates it started and ended? (Carrying `LAG(event_timestamp)` as its own column and taking the row with the largest gap per type.)
• What if two events of the same type share an `event_timestamp`? (Adding `event_id` as a tiebreaker in the window's `ORDER BY` so `LAG` is deterministic.)
• What changes if quiet stretches should be measured per user rather than per event type? (Partitioning by `user_id` and noticing the zipf skew moves to the heaviest users.)
• How would you restrict this to stretches that ended in 2026 without cutting a stretch that started the year before? (Filtering on the later timestamp after the window, not on the rows before it.)