SQL User Segmentation Query | Dataford Interview Questions

Write a SQL query using a subquery and a CASE statement to segment users based on their subscription tier and active login frequency at McAfee.

Use login events from March 2025 to calculate each user's active login count. Classify Gold or Platinum users with at least two active logins as Priority, users with at least two active logins as Engaged, users with one active login as At Risk, and users with none as Dormant.

Output

One row per user with user_id, subscription_tier, active_login_count, and user_segment.
Include users with no active logins, order by user_id ascending.

Schema

users

Column	Type	Description
user_idPK	INT	Unique McAfee user identifier
email	VARCHAR(255)	User email address
subscription_tier	VARCHAR(30)	Current McAfee subscription tier

login_events

Column	Type	Description
login_idPK	INT	Unique login event identifier
user_id	INT	User associated with the login event
login_at	TIMESTAMP	Timestamp when the user logged in

Your question is SQL User Segmentation Query. 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