Prompt and Applicable Context
You are given this PostgreSQL table:
CREATE TABLE events (
user_id bigint NOT NULL,
event_id bigint PRIMARY KEY,
event_name text NOT NULL,
event_time timestamptz NOT NULL
);Calculate a three-step funnel: visit → signup → purchase. A user enters the cohort if they have at least one visit in the half-open reporting interval [startat, endat). Their earliest visit in that interval is the funnel anchor. The qualifying signup is the earliest signup after that anchor; the qualifying purchase is the earliest purchase after the selected signup. Both must occur no later than 24 hours after the anchor visit.
event_id is unique. The event producer guarantees that when two events for the same user share a timestamp, the smaller event_id happened first. A purchase exactly 24 hours after the anchor counts; one even a microsecond later does not. Events between funnel steps are allowed, and duplicate step events must not count a user twice.
Return one row with visitedusers, signedupusers, purchasedusers, signup_rate, and purchase_rate. Both rates are cumulative from the visit cohort, expressed as fractions rounded to four decimal places. An empty cohort returns zero counts and null rates.
This question fits data analyst, analytics engineer, and product analytics interviews. It tests much more than conditional aggregation: the candidate must define the cohort grain, preserve event order, select a valid chain when events repeat, handle time boundaries, and prove that the query cannot count an impossible journey.
What the Interviewer Evaluates
The first signal is metric-contract discipline. “Users who completed all three events” is not enough. The interviewer wants to know which visit anchors the journey, whether steps must be ordered, whether the window closes 24 hours after the visit or after each step, and whether rates use the previous step or the original cohort as denominator. Each choice changes the result.
The second signal is grain control. The raw table is one row per event, but the output counts users. The cohort must have at most one row per user, and every later lookup must produce at most one event for that cohort row. A broad join followed by COUNT(DISTINCT user_id) may hide an incorrect event chain rather than establish one.
The third signal is sequential reasoning. Independent expressions such as MIN(CASE WHEN event_name = 'purchase' ...) do not necessarily find the earliest purchase after the selected signup. A user may purchase, then sign up, then purchase again. The valid purchase is the second one. Each step therefore needs the event selected by the previous step.
The fourth signal is deterministic temporal logic. Timestamps alone do not order equal-time events. This prompt supplies event_id as a tie-breaker, so the comparison key is the tuple (eventtime, eventid). Without that guarantee, the data cannot prove which equal-time event came first, and SQL should not invent causality.
The final signal is operational judgment. A funnel whose window extends beyond end_at needs later event data. The report is mature only after the pipeline has processed through end_at + 24 hours. The candidate should discuss late arrivals, indexes, query plans, and fixtures that attack the metric definition instead of stopping at syntactically valid SQL.
Questions to Clarify Before Answering
- Which event anchors a user? This answer uses the earliest visit inside the reporting interval,
even if the user visited before the interval. “First-ever visit” would require historical data and a different cohort filter.
- Is the funnel ordered? Yes. A signup must follow the anchor visit, and a purchase must follow the
selected signup. Unordered event presence is a different metric.
- Where does the window start and end? It starts at the anchor visit and closes inclusively at
visit_time + 24 hours. It does not restart after signup.
- Can later steps fall outside
end_at? Yes, provided they are within the user's 24-hour window.
end_at selects anchor visits; it does not truncate conversion opportunities.
- How are equal timestamps ordered? Compare
eventtimefirst andeventidsecond, using the
producer's stated guarantee. If no trustworthy sequence exists, equal-time steps are ambiguous.
- Which denominator defines each rate? Both use
visited_users. A step-to-step purchase rate would
divide purchasers by signed-up users and should have a different column name.
- What does empty input return? Counts are zero; rates are null because there is no meaningful
denominator. NULLIF prevents division by zero.
- When is the report final? Only after a data-completeness watermark covers every cohort member's
full 24-hour opportunity and the late-arrival policy has been applied.
30-Second Answer Framework
“I would first make the cohort one row per user by ranking visits in the half-open reporting interval and keeping rank one. For each anchor, a left lateral lookup finds the earliest signup whose (eventtime, eventid) is after the visit and whose time is within 24 hours. A second left lateral lookup does the same for purchase, but starts after the selected signup while retaining the original 24-hour deadline. That creates one progress row per visited user. I can then use filtered counts for the two reached stages and divide both by the visit count. I would test reversed events, duplicates, equal timestamps, the exact 24-hour boundary, repeated visits, an empty cohort, and report maturity.”
Step-by-Step Deep Dive
The cohort comes first. Filtering before ROW_NUMBER() means “earliest visit in the reporting interval,” exactly matching the prompt. Adding event_id to the window order makes the selected anchor stable when visits share a timestamp.
WITH ranked_visits AS (
SELECT
user_id,
event_id AS visit_event_id,
event_time AS visit_time,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY event_time, event_id
) AS visit_rank
FROM events
WHERE event_name = 'visit'
AND event_time >= :start_at
AND event_time < :end_at
),
cohort AS (
SELECT user_id, visit_event_id, visit_time
FROM ranked_visits
WHERE visit_rank = 1
)
SELECT *
FROM cohort;The main query uses two dependent lateral lookups. PostgreSQL evaluates a lateral subquery with values from the rows to its left. LEFT JOIN LATERAL preserves the cohort row when no qualifying event is found, which is exactly what a funnel needs: a visitor who never signs up remains in the denominator.
WITH ranked_visits AS (
SELECT
user_id,
event_id AS visit_event_id,
event_time AS visit_time,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY event_time, event_id
) AS visit_rank
FROM events
WHERE event_name = 'visit'
AND event_time >= :start_at
AND event_time < :end_at
),
cohort AS (
SELECT user_id, visit_event_id, visit_time
FROM ranked_visits
WHERE visit_rank = 1
),
progress AS (
SELECT
c.user_id,
c.visit_time,
s.signup_time,
p.purchase_time
FROM cohort AS c
LEFT JOIN LATERAL (
SELECT
e.event_id AS signup_event_id,
e.event_time AS signup_time
FROM events AS e
WHERE e.user_id = c.user_id
AND e.event_name = 'signup'
AND (
e.event_time > c.visit_time
OR (
e.event_time = c.visit_time
AND e.event_id > c.visit_event_id
)
)
AND e.event_time <= c.visit_time + INTERVAL '24 hours'
ORDER BY e.event_time, e.event_id
LIMIT 1
) AS s ON TRUE
LEFT JOIN LATERAL (
SELECT e.event_time AS purchase_time
FROM events AS e
WHERE e.user_id = c.user_id
AND e.event_name = 'purchase'
AND s.signup_time IS NOT NULL
AND (
e.event_time > s.signup_time
OR (
e.event_time = s.signup_time
AND e.event_id > s.signup_event_id
)
)
AND e.event_time <= c.visit_time + INTERVAL '24 hours'
ORDER BY e.event_time, e.event_id
LIMIT 1
) AS p ON TRUE
)
SELECT
COUNT(*) AS visited_users,
COUNT(*) FILTER (WHERE signup_time IS NOT NULL) AS signed_up_users,
COUNT(*) FILTER (WHERE purchase_time IS NOT NULL) AS purchased_users,
ROUND(
COUNT(*) FILTER (WHERE signup_time IS NOT NULL)::numeric
/ NULLIF(COUNT(*), 0),
4
) AS signup_rate,
ROUND(
COUNT(*) FILTER (WHERE purchase_time IS NOT NULL)::numeric
/ NULLIF(COUNT(*), 0),
4
) AS purchase_rate
FROM progress;The signup lookup proves three properties together: same user, strict tuple order after the anchor, and inclusion in the anchor's 24-hour window. Ordering plus LIMIT 1 selects one deterministic event. The purchase lookup depends on that selected signup, so a purchase before it is ignored while a later valid purchase remains eligible. Its deadline still refers to c.visit_time; otherwise the query would accidentally grant up to 48 hours.
This greedy choice is correct for a fixed three-step ordered funnel. Choosing the earliest qualifying signup cannot eliminate a purchase that a later signup could admit: any purchase after the later signup is also after the earlier signup, and the common deadline is unchanged. The same argument applies to choosing the earliest qualifying purchase. The invariant after each lookup is that the selected chain is valid and leaves the largest possible remaining suffix of the window.
The progress CTE has exactly one row per cohort user because each lateral lookup returns at most one row. Filtered aggregates therefore count users without requiring DISTINCT. The purchase count cannot exceed the signup count, and the signup count cannot exceed the visit count. Those inequalities are useful result-level assertions.
Avoid the tempting independent-minimum pattern:
MIN(CASE WHEN event_name = 'signup' THEN event_time END),
MIN(CASE WHEN event_name = 'purchase' THEN event_time END)For the sequence visit(09:00), purchase(09:05), signup(09:10), purchase(09:20), independent minima select the 09:05 purchase and may reject the user. The dependent query selects the 09:10 signup and then the 09:20 purchase. Conditional minima can work only when each minimum is constrained by the previous selected step, which is the central difficulty here.
Two indexes are plausible starting points:
CREATE INDEX events_anchor_scan_idx
ON events (event_name, event_time, user_id, event_id);
CREATE INDEX events_user_step_lookup_idx
ON events (user_id, event_name, event_time, event_id);The first supports the event-name and time range used to build the cohort. The second supports repeated per-user step probes. They add storage and write amplification, and actual value depends on selectivity, clustering, table size, and PostgreSQL's chosen plan. Validate with EXPLAIN (ANALYZE, BUFFERS) on representative data. For frequent large-scale reports, an event model with a stable sequence field or an incrementally maintained user-journey table may be more appropriate, but it must preserve the same cohort and window contract.
A compact adversarial fixture should include these cases:
1. purchase before visit -> visitor only
2. visit, purchase, signup -> signed up, not purchased
3. visit, purchase, signup, later purchase -> completes all steps
4. duplicate signups and purchases -> user still counts once per stage
5. purchase exactly at visit + 24 hours -> counts
6. purchase one microsecond after visit + 24 hours -> does not count
7. equal timestamps -> event_id determines sequence
8. repeated visits in the interval -> earliest visit remains the anchor
9. no visits -> zero counts and null ratesAlso test the reporting lifecycle. If end_at is midnight July 1, a visitor at 23:59 on June 30 can convert until 23:59 on July 1. A run at July 1 midnight is incomplete. Use an ingestion watermark, not the wall clock, and recompute affected cohorts when late events arrive.
High-Quality Sample Answer
“I would state the attribution rule before writing SQL: one funnel opportunity per user, anchored at their earliest visit inside the reporting interval. The interval is half-open, later steps may occur after its end, and both signup and purchase must fit within 24 hours of the anchor. Equal timestamps are ordered by the guaranteed event ID sequence.
I first filter visits to the interval, rank them by (eventtime, eventid) per user, and retain rank one. That gives the correct denominator grain. For every anchor I use a left lateral subquery ordered by the same tuple to fetch the earliest valid signup. A second left lateral subquery references the selected signup and fetches the earliest valid purchase, while keeping the deadline tied to the visit. Because each lookup has LIMIT 1, the progress relation remains one row per visitor. Missing steps stay null rather than removing the user.
The final aggregate counts all progress rows, then uses filtered counts for non-null signup and purchase timestamps. Both rates divide by the visit cohort count and use NULLIF for empty input. I would assert purchasedusers <= signedupusers <= visitedusers, inspect sample chains for every stage, and specifically test reversed events, repeated steps, equal timestamps, and both sides of the 24-hour boundary.
For production I would not call the newest cohort complete until the ingestion watermark covers its full opportunity window. I would compare plans with and without indexes that support the anchor scan and per-user step lookups. If timestamps and event IDs do not represent trustworthy order, I would stop and repair the event contract because the query cannot recover missing causality.”
Common Mistakes
- Checking only event presence. Three event flags can count
purchase → visit → signup. A funnel
needs an explicit ordered chain.
- Taking independent first timestamps. The earliest purchase may precede the selected signup even
when a later purchase completes a valid journey.
- Anchoring at every visit unintentionally. That changes the grain from one user opportunity to one
visit opportunity and can count or attribute a user differently.
- Using only
event_timefor ordering. Equal timestamps make the result unstable. Use a trustworthy
sequence key or acknowledge that order is unknowable.
- Restarting the window at each step. Giving signup 24 hours and purchase another 24 hours violates
an anchor-based 24-hour contract.
- Filtering later steps by
end_at. That shortens the opportunity for visitors near the reporting
boundary. Fetch later events through each anchor's deadline.
- Using an inner lateral join. Visitors with no signup disappear, inflating the conversion rate.
- Counting raw event rows. Retries and repeated actions can make stage counts exceed cohort size.
- Dividing by the previous step accidentally. That computes a step conversion rate, not the stated
cumulative rate from visits.
- Publishing an immature cohort. The absence of a later event is not evidence of drop-off until the
full window is covered by reliable data.
Follow-Up Questions and Responses
What if any visit may start a valid journey?
The grain changes. Generate candidate anchors for every visit, match a valid chain from each, and then apply an attribution rule such as the earliest completed journey per user. Do not simply replace the anchor CTE: overlapping windows can compete for the same later event, so the product contract must say whether reuse is allowed.
What if each step must share a session or product?
Carry the correlation key from the anchor and require it in both lateral lookups. The grain becomes user-session or user-product rather than user alone. Define how null keys behave and whether the key is immutable before relying on the join.
How would you support ten funnel steps?
Ten handwritten lateral joins are hard to audit. Depending on the database, use recursive SQL, a native sequence or funnel function, or a preprocessing job that walks ordered events as a state machine. Preserve the same invariants: one defined anchor, monotonic step order, one shared deadline, and an auditable attribution rule.
What if events arrive late or out of order?
Separate event time from ingestion time, publish a completeness watermark, and restate cohorts whose windows receive late data. Event-time order can still be calculated after arrival, but the report is provisional until the agreed lateness interval has passed.
Can a window function solve the entire problem?
It can, for example by scanning each user's ordered stream and carrying state, but LAG alone is not enough because irrelevant and duplicate events may sit between steps. The lateral solution makes the dependency between selected steps explicit. Choose another formulation only if its state transitions and attribution semantics are equally clear and its measured plan is better.