SQL Inactive Active Subscribers | Dataford Interview Questions

Write a SQL query to find all users who have active subscriptions but have not logged into the app in the last 30 days at Truebill.

Use the provided users, subscriptions, and app_logins tables. Treat subscriptions with status active and no past end date as active.

Output

Return one row per qualifying user.
Include user_id, email, and last_login_at.
Include users with no login history, exclude users with a login during the last 30 days, and sort by user_id ascending.

users

Column	Type	Description
user_idPK	INT	Unique Truebill user identifier
email	VARCHAR(255)	User email address

subscriptions

Column	Type	Description
subscription_idPK	INT	Unique subscription identifier
user_id	INT	User who owns the subscription
status	VARCHAR(20)	Current subscription status
end_date	DATE	Subscription end date, or NULL for an open-ended subscription

app_logins

Column	Type	Description
login_idPK	INT	Unique login event identifier
user_id	INT	User who logged into the app
login_at	TIMESTAMP	Timestamp of the login event

Your question is SQL Inactive Active Subscribers. 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.