SQL for Engagement by Segment | Dataford Interview Questions

How would you write a SQL query to compare engagement across different user segments at PrizePicks?

Use the provided user and activity records for January 2025. Include users with no qualifying activity and exclude activity outside the month.

Output

One row per user segment
Columns: segment, users_in_segment, engaged_users, total_events, avg_events_per_user, and engagement_rate_pct
Include segments with zero engaged users, order by engagement rate descending, then segment ascending

Treat January 2025 as the interval from 2025-01-01 inclusive through 2025-02-01 exclusive.
Count a user as engaged only when their in-range event count is greater than zero.
Represent users without qualifying events with total_events equal to 0.
Round avg_events_per_user and engagement_rate_pct to two decimal places.

users

Column	Type	Description
user_idPK	INT	Unique PrizePicks user identifier
segment	VARCHAR(50)	User engagement segment

activity_events

Column	Type	Description
event_idPK	INT	Unique activity event identifier
user_id	INT	User associated with the activity event
event_type	VARCHAR(50)	Type of user activity
occurred_at	DATE	Date on which the activity occurred

Your question is SQL for Engagement by Segment. 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.