SQL Retention Query for Premium Users | Dataford Interview Questions

GitLab wants to monitor whether Premium subscribers continue using GitLab in the following week. Login activity must be evaluated against the subscriber's tier at the time of each login.

Write a PostgreSQL query that calculates weekly retention for Premium users.

Requirements

Convert each login timestamp to its ISO week start using DATE_TRUNC('week', ...).
Include a user in a week only when the user had the premium tier at the time of the login.
Deduplicate multiple logins by the same user within the same week.
For each week, calculate the Premium users active in that week, the number who also logged in during the immediately following calendar week, and the retention rate as a percentage.
Return one row per observed Premium activity week, ordered chronologically. Return NULL for the final week when no following week is available.

Schema

login_events

Column	Type	Description
event_idPK	INT	Unique login event identifier
user_id	INT	GitLab user identifier
login_at	TIMESTAMP	Timestamp when the user logged in

subscription_tiers

Column	Type	Description
subscription_idPK	INT	Unique subscription record identifier
user_id	INT	GitLab user identifier
tier	VARCHAR(20)	Subscription tier
valid_from	TIMESTAMP	Start of the tier period
valid_to	TIMESTAMP	Exclusive end of the tier period, or NULL for an active period

Your question is SQL Retention Query for Premium Users. 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