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.
Overview: 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.
Country-level 7-day conversion rate using window functions
You are given two tables:
- users(id, signup_date, country)
- events(user_id, event_date, event_type, revenue)
Compute, by country, the 7-day conversion rate for the period FROM 2025-08-26 TO 2025-09-01 (inclusive).
Definitions:
- Consider only users whose signup_date is on or before 2025-09-01.
- A user is counted in the denominator for a country if they have at least one 'visit' event in the window [2025-08-26, 2025-09-01].
- A user is counted in the numerator for a country if they have at least one 'purchase' event in the same window, regardless of whether they had a visit in that window.
- Each user must be counted at most once in the numerator and at most once in the denominator.
Return one row per country with the columns:
- country
- conversion_rate = (number of users with ≥1 purchase in the window) / (number of users with ≥1 visit in the window)
Use window functions where appropriate to de-duplicate at the user level before aggregating by country.
Tables
users(id INT, signup_date DATE, country VARCHAR(2))
events(user_id INT, event_date DATE, event_type VARCHAR(20), revenue DECIMAL(10,2))
Hints
- First, reduce events to a per-user level indicating whether each user had any visit or purchase in the date window.
- Use window functions over user_id to compute has_visit and has_purchase flags, then aggregate by country.
First purchase date and days to convert per user
Using the same users and events tables, return for each user:
- user_id
- first_purchase_date: the date of their earliest 'purchase' event (NULL if they never purchased)
- days_to_convert: the number of days between signup_date and first_purchase_date (NULL if they never purchased)
Use window functions to de-duplicate purchase events so that you correctly identify the first purchase per user.
Tables
users(id INT, signup_date DATE, country VARCHAR(2))
events(user_id INT, event_date DATE, event_type VARCHAR(20), revenue DECIMAL(10,2))
Hints
- Identify the earliest purchase per user using ROW_NUMBER() over (PARTITION BY user_id ORDER BY event_date).
- Left join the first purchase back to the users table and subtract signup_date from the purchase date.
Users with a visit before their first purchase (self join on events)
Using the events table only, list the user_ids of users who had at least one 'visit' event strictly before their first 'purchase' event.
Use a self join on the events table to implement this logic (you may use additional subqueries if needed). Return each qualifying user_id only once.
Tables
users(id INT, signup_date DATE, country VARCHAR(2))
events(user_id INT, event_date DATE, event_type VARCHAR(20), revenue DECIMAL(10,2))
Hints
- First, determine each user's first purchase date (e.g., with a grouped subquery).
- Self-join events as visits to those first purchases, and keep users where a visit event_date is less than the first_purchase_date.
Effect of LEFT vs RIGHT JOIN on conversion query when some countries have no purchases
Consider your solution to Question 1 (country-level conversion rate over [2025-08-26, 2025-09-01]). Suppose some countries have users with visits in the window but no users with purchases in the window (for example, country 'IN' in the sample data).
Explain briefly how using a LEFT JOIN versus a RIGHT JOIN between the "denominator" side (users/visits) and the "numerator" side (purchases) would affect the result set by country. In particular, when would countries disappear from the output, and when would they appear with a conversion_rate of 0?
Answer in terms of JOIN direction and NULL handling; you may include a small illustrative query or comments.
Tables
users(id INT, signup_date DATE, country VARCHAR(2))
events(user_id INT, event_date DATE, event_type VARCHAR(20), revenue DECIMAL(10,2))
Hints
- Think of the JOIN starting table as defining the universe of rows that can appear in the output.
- A LEFT JOIN from visits/users to purchases keeps countries with visits but no purchases; a RIGHT JOIN from purchases drops countries without purchases.