SQL and Lifecycle Analytics | Dataford Interview Questions

How do you approach query optimization and data extraction using SQL and Python or R for lifecycle analytics?

Use the tables below to return monthly lifecycle metrics by stage. Focus on producing a clean extract that can be reused in Python or R for downstream analysis.

Output

One row per month and lifecycle_stage
Columns: month, lifecycle_stage, active_users, events, conversion_rate
Include only months with at least one qualifying event
Sort by month, then lifecycle_stage

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
email	VARCHAR(255)	User email address
lifecycle_stage	VARCHAR(50)	Current lifecycle stage
signup_date	DATE	Date the user signed up

events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User who triggered the event
event_type	VARCHAR(50)	Event type such as open, click, or conversion
event_date	DATE	Date of the event
channel	VARCHAR(50)	Marketing channel associated with the event

Your question is SQL and Lifecycle Analytics. Start with the requirements and the two 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