Query conversion and retention with SQL windows

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Onsite rounds at Other.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Other during technical interviews.

Question

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.

PracHub · Accepted 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.

Quick Overview

Quick Overview