SQL for Full Funnel Conversion | Dataford Interview Questions

How would you write a SQL query to find students who opened an email, clicked a link, and then submitted an application?

Use the provided student, email event, and application records. A valid sequence requires the click to occur after the open and the submitted application to occur after the click.

Output

One row per qualifying student with student_id, student_name, opened_at, clicked_at, and submitted_at.
Include only students with a complete valid sequence.
Sort by student_id ascending.

Schema

students

Column	Type	Description
student_idPK	INT	Unique student identifier
full_name	VARCHAR(100)	Student's full name
email_address	VARCHAR(255)	Student email address

email_events

Column	Type	Description
event_idPK	INT	Unique email event identifier
student_id	INT	Student associated with the event
campaign_id	VARCHAR(30)	Email campaign identifier
event_type	VARCHAR(20)	Email interaction type
event_time	TIMESTAMP	Time when the interaction occurred

applications

Column	Type	Description
application_idPK	INT	Unique application identifier
student_id	INT	Student associated with the application
status	VARCHAR(20)	Current application status
submitted_at	TIMESTAMP	Application submission timestamp

Your question is SQL for Full Funnel Conversion. Start with the requirements and the three tables in the Question tab.

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

Problem