Query conversion and retention with SQL windows

Q: Query conversion and retention with SQL windows

This is a Data Manipulation (SQL/Python) interview question from Other for Data Scientist roles. View the full question and solution on PracHub.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) 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.

Query conversion and retention with SQL windows

Comments (0)