Analyze Retention Metrics Using SQL and Python
Company: Netflix
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Other
transactions
+----------+--------+------------+---------+-----------+
| user_id | txn_id | txn_date | amount | is_fraud |
+----------+--------+------------+---------+-----------+
| 101 | 9001 | 2024-04-01 | 13.99 | 0 |
| 102 | 9002 | 2024-04-02 | 7.99 | 1 |
| 101 | 9003 | 2024-04-03 | 15.49 | 0 |
| 103 | 9004 | 2024-04-05 | 11.99 | 0 |
| 102 | 9005 | 2024-04-06 | 8.49 | 1 |
+----------+--------+------------+---------+-----------+
##### Scenario
Analyst is provided with transaction logs and must write SQL and lightweight Python to build retention metrics.
##### Question
Write a SQL query that returns, for each user, the first transaction date and the number of transactions made within 7 days of that first purchase. Write a SQL query that computes overall Day-7 retention rate. Given a Python list of n integers, write a function that returns the two numbers whose sum is closest to zero (assume at least two numbers).
##### Hints
Use window functions for datediff; in Python aim for O(n log n) or better.
Overview: This question evaluates proficiency in SQL time-based cohort analysis and practical Python algorithm implementation, testing retention metric computation using windowed date calculations and an efficient approach for finding two numbers whose sum is closest to zero.
You are given a transactions table with one row per transaction. Return one combined result set for Day-7 retention analysis.
Use columns result_set, user_id, first_txn_date, txn_count_7d, and retention_rate_7d:
1. result_set = 'per_user_7d_activity': for each user, return their first transaction date and the number of transactions whose txn_date is between first_txn_date and first_txn_date + INTERVAL '7 days' inclusive.
2. result_set = 'overall_retention': return one row with the overall Day-7 retention rate, defined as the proportion of users with at least one transaction strictly after the first transaction date and on or before first_txn_date + INTERVAL '7 days'.
Fields that do not apply to a row should be NULL. Sort per-user rows by user_id and place the overall row last.
Tables
transactions(user_id INTEGER, txn_id INTEGER, txn_date DATE, amount DECIMAL(10,2), is_fraud INTEGER)
Hints
- Find each user's first transaction date before counting the seven-day window.
- Retention should exclude the first transaction itself.