Exclude free subscribers via anti-join
Company: Intuit
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Overview: This question evaluates competency with SQL anti-joins, join semantics, deduplication, and correct pre-aggregation filtering to prevent excluded entities from affecting aggregated monthly and year-over-year metrics; it is categorized as Data Manipulation (SQL/Python) for a Data Scientist role.
Tables
Subscriptions(subscription_id INT, company_id INT, start_date DATE)
Free_Subs(company_id INT)
Month_Calendar(month_start DATE, year INT, month INT)
Hints
- First compute each company’s first paid subscription date, and apply the anti-join against Free_Subs in that step before aggregating.
- Use DATE_TRUNC('month', first_paid_date) and a LEFT JOIN to Month_Calendar to ensure every month appears with zeroes where appropriate.