SQL for MAU Subscription Watchers | Dataford Interview Questions

Write a SQL query to find the monthly active users (MAUs) who purchased a subscription but did not watch any games in their first week.

Treat the first week as the seven-day period starting at the subscription purchase timestamp. Return one result for each subscription purchase month and count each qualifying user once per month.

Output

Columns: subscription_month and mau_count
One row per calendar month with at least one qualifying subscription user
Include only users with no game view during their first seven days
Sort by subscription_month ascending

Schema

subscriptions

Column	Type	Description
subscription_idPK	INT	Unique subscription purchase identifier
user_id	INT	MLB account identifier
purchased_at	TIMESTAMP	Timestamp when the subscription was purchased
plan_name	VARCHAR(30)	Subscription plan purchased

game_views

Column	Type	Description
view_idPK	INT	Unique game viewing event identifier
user_id	INT	MLB account identifier associated with the view
game_id	VARCHAR(20)	MLB game identifier
watched_at	TIMESTAMP	Timestamp when the game was watched

Your question is SQL for MAU Subscription Watchers. 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