Query conversion and retention with SQL windows
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
Schema and sample data (PostgreSQL):
users(id, signup_date, country)
1 | 2025-08-20 | US
2 | 2025-08-25 | US
3 | 2025-08-27 | CA
4 | 2025-08-30 | US
5 | 2025-08-31 | IN
events(user_id, event_date, event_type, revenue)
1 | 2025-08-21 | visit | 0
1 | 2025-08-26 | purchase | 50
2 | 2025-08-26 | visit | 0
2 | 2025-09-01 | purchase | 20
3 | 2025-08-28 | visit | 0
3 | 2025-08-31 | purchase | 30
4 | 2025-08-31 | visit | 0
5 | 2025-09-01 | visit | 0
Tasks (use window functions where appropriate; treat "today" as 2025-09-01): (a) Compute, by country, the 7-day conversion rate on 2025-09-01, defined as users with ≥1 purchase in [2025-08-26, 2025-09-01] divided by users with ≥1 visit in the same window (users must have signed up by 2025-09-01). Ensure each user counts at most once in numerator and denominator. (b) For each user, return first_purchase_date and days_to_convert from signup_date; use window functions to de-duplicate events. (c) Using a self join, list users who had at least one visit strictly before their first purchase. (d) Explain when LEFT JOIN vs RIGHT JOIN changes results in (a) if some countries have no purchases during the window.
Quick Answer: This question evaluates proficiency in SQL data manipulation—specifically window functions, event de-duplication, self-joins, date-windowed aggregation, and conversion/retention metric calculation—and is targeted at Data Scientist roles in the Data Manipulation (SQL/Python) domain.