0Pricing
SQL Interview Prep · Lesson

Ordered Events and Time Windows

Ensuring steps happen in sequence and within a time limit using window functions.

Ordered Events and Time Windows is a free SQL Interview Prep lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Order and Time Matter

The basic funnel from the previous lesson only checks whether a user did each step. A sharper interviewer asks: did the steps happen in the right order, and within a reasonable time?

A user who purchased on Monday and visited the marketing page on Friday did not convert through your funnel. Sequence and timing turn a naive flag-based funnel into a credible one.

The Per-User First-Timestamp Idea

To reason about order, capture each user's first time at each step. The earliest visit, the earliest signup, the earliest purchase.

Then a clean conversion means first_signup_time >= first_visit_time and so on down the chain. MIN(event_time) grouped per step gives you those anchors.

SELECT
  user_id,
  MIN(CASE WHEN event_name = 'visit'    THEN event_time END) AS first_visit,
  MIN(CASE WHEN event_name = 'signup'   THEN event_time END) AS first_signup,
  MIN(CASE WHEN event_name = 'purchase' THEN event_time END) AS first_purchase
FROM events
GROUP BY user_id;

Requiring Steps in Sequence

With per-step first timestamps, enforcing order is a comparison. A user truly converted to step 3 only if each timestamp is non-null and monotonically increasing.

Note how a NULL timestamp (the step never happened) naturally fails the comparison, which is exactly what you want.

WITH t AS (
  SELECT user_id,
    MIN(CASE WHEN event_name='visit'    THEN event_time END) AS visit_t,
    MIN(CASE WHEN event_name='signup'   THEN event_time END) AS signup_t,
    MIN(CASE WHEN event_name='purchase' THEN event_time END) AS purchase_t
  FROM events GROUP BY user_id
)
SELECT COUNT(*) AS converted_in_order
FROM t
WHERE visit_t IS NOT NULL
  AND signup_t  >= visit_t
  AND purchase_t >= signup_t;

Adding a Time Window

Most funnels have a deadline: "convert within 7 days of first visit." Add an interval bound between the first step and the final step.

Date arithmetic varies by dialect. In Postgres you can write visit_t + INTERVAL '7 days'; in MySQL use DATE_ADD(visit_t, INTERVAL 7 DAY). Always state your dialect.

WITH t AS (
  SELECT user_id,
    MIN(CASE WHEN event_name='visit'    THEN event_time END) AS visit_t,
    MIN(CASE WHEN event_name='purchase' THEN event_time END) AS purchase_t
  FROM events GROUP BY user_id
)
SELECT COUNT(*) AS purchased_within_7d
FROM t
WHERE purchase_t >= visit_t
  AND purchase_t <  visit_t + INTERVAL '7 days';

Why First Timestamp, Not Any Timestamp

A subtle interview point: should the window run from the user's first visit or their most recent one before signup? It depends on the product question.

  • First-touch windows measure how long from initial interest to conversion.
  • Last-touch windows measure the conversion sprint after the final visit.

Ask the interviewer which they mean; picking deliberately signals seniority.

Ordered Events With LEAD

For complex multi-step paths, window functions shine. Order each user's events by time, then use LEAD to look at the next event and confirm it is the expected next step.

This handles paths where steps interleave with unrelated events.

SELECT
  user_id,
  event_name,
  event_time,
  LEAD(event_name) OVER (PARTITION BY user_id ORDER BY event_time) AS next_event,
  LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS next_time
FROM events;

Matching the Next Expected Step

Build on LEAD: keep rows where a 'visit' is immediately followed by a 'signup'. This finds true sequential transitions, not just co-occurrence.

You can chain these transition checks to validate an entire ordered path step by step.

WITH seq AS (
  SELECT user_id, event_name, event_time,
    LEAD(event_name) OVER (PARTITION BY user_id ORDER BY event_time) AS next_event
  FROM events
)
SELECT COUNT(DISTINCT user_id) AS visit_then_signup
FROM seq
WHERE event_name = 'visit' AND next_event = 'signup';

Time Between Consecutive Steps

Interviewers love "how long does each step take?" Use LEAD on the timestamp and subtract. The difference between consecutive events is the dwell time at that stage.

Aggregate the median or average per transition to find your slowest funnel stage.

WITH seq AS (
  SELECT user_id, event_name, event_time,
    LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS next_time
  FROM events
)
SELECT
  event_name,
  AVG(EXTRACT(EPOCH FROM (next_time - event_time)) / 3600.0) AS avg_hours_to_next
FROM seq
WHERE next_time IS NOT NULL
GROUP BY event_name;

The Same-Timestamp Edge Case

What if two events share the exact same event_time? Then signup_t >= visit_t is true even if they are simultaneous, and ordering by time alone is ambiguous.

  • Use >= vs > deliberately and say why.
  • Add a tiebreaker like an event sequence id to ORDER BY so windows are deterministic.

Mentioning this unprompted impresses interviewers.

SELECT user_id, event_name,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time, event_id) AS step_seq
FROM events;

Combining Order and Window in One Query

Here is the complete in-order, within-window funnel. It anchors on first visit, requires each later step's first occurrence to come after the prior, and bounds the whole path to 7 days.

This is the answer that separates a candidate who understands funnels from one who only counts flags.

WITH t AS (
  SELECT user_id,
    MIN(CASE WHEN event_name='visit'    THEN event_time END) AS v,
    MIN(CASE WHEN event_name='signup'   THEN event_time END) AS s,
    MIN(CASE WHEN event_name='purchase' THEN event_time END) AS p
  FROM events GROUP BY user_id
)
SELECT
  COUNT(*) FILTER (WHERE v IS NOT NULL)                                   AS visited,
  COUNT(*) FILTER (WHERE s >= v AND s < v + INTERVAL '7 days')            AS signed_up,
  COUNT(*) FILTER (WHERE s >= v AND p >= s AND p < v + INTERVAL '7 days') AS purchased
FROM t;

Cross-Dialect Notes

Two portability reminders for live coding:

  • FILTER (WHERE ...) on aggregates is standard SQL and works in Postgres; in MySQL or older engines fall back to SUM(CASE WHEN ... THEN 1 ELSE 0 END).
  • Interval syntax differs: Postgres + INTERVAL '7 days', MySQL DATE_ADD(d, INTERVAL 7 DAY), SQL Server DATEADD(day, 7, d).

State your assumption and the interviewer rarely cares which dialect, only that you know they differ.

Quick Check

You must count users who completed visit -> signup -> purchase in order, within 7 days of first visit. Which approach is correct?

Recap: Ordered Events and Time Windows

Key takeaways:

  • Capture each user's first timestamp per step with MIN(CASE ...).
  • Enforce sequence by requiring each step's time to be at or after the previous step's.
  • Bound the path with an interval, stating your dialect's syntax.
  • Use LEAD/LAG for transition checks and dwell-time between steps.
  • Handle same-timestamp ties with a tiebreaker in ORDER BY.

Next: shifting from funnels to experiments and computing per-variant metrics.

Frequently asked questions

Is the “Ordered Events and Time Windows” lesson free?

Yes — the full text of “Ordered Events and Time Windows” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Ordered Events and Time Windows”?

Ensuring steps happen in sequence and within a time limit using window functions. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Ordered Events and Time Windows” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Building a Multi-Step Funnel
  2. Ordered Events and Time Windows
  3. A/B Test Assignment and Metrics
  4. Lift, Significance and Guardrails in SQL
← Back to SQL Interview Prep