SQL Funnel Drop-Off Detection | Dataford Interview Questions

Your question is SQL Funnel Drop-Off Detection. Start with the requirements and the three tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Apple Store analytics receives user funnel events from a distributed warehouse. Write a PostgreSQL query that identifies consecutive missing steps in each user session's purchase funnel.

Use the canonical Apple Store funnel defined in funnel_steps. Report only missing-step streaks containing at least two consecutive steps.

Requirements

Deduplicate repeated events for the same user, session, and funnel step.
Include every canonical funnel step for each observed session, marking whether the step was reached.
Use window functions to assign consecutive missing steps to the same streak.
Return the user, session, last reached step before the streak, streak boundaries, and streak length.
Exclude completed sessions and isolated one-step gaps, and order results by user and starting step.

Schema

apple_store_users

Column	Type	Description
user_idPK	INT	Apple Store user identifier
country	VARCHAR(30)	User billing country
signup_date	DATE	Date the user registered

funnel_events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	References apple_store_users.user_id
session_id	VARCHAR(20)	Apple Store browsing session
event_ts	TIMESTAMP	Timestamp when the event occurred
device_type	VARCHAR(20)	Client device category

funnel_steps

Column	Type	Description
step_numberPK	INT	Canonical order of the funnel step
step_name	VARCHAR(30)	Canonical Apple Store funnel step name