Analyze Retention Metrics Using SQL and Python

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.

Day-7 retention by user and overall retention rate

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.

Table transactions

column	type	constraints
user_id	INTEGER	NOT NULL
txn_id	INTEGER	PRIMARY KEY, NOT NULL
txn_date	DATE	NOT NULL
amount	DECIMAL(10,2)	NOT NULL
is_fraud	INTEGER	NOT NULL, CHECK (is_fraud IN (0,1))

Sample data:

amount	is_fraud	txn_date	txn_id	user_id
13.99	0	2024-04-01	9001	101
7.99	1	2024-04-02	9002	102
15.49	0	2024-04-03	9003	101
11.99	0	2024-04-05	9004	103
8.49	1	2024-04-06	9005	102

Expected output

One combined result set: per-user Day-7 transaction counts followed by the overall retention rate. Retention excludes the first transaction itself and requires a later transaction within seven days.

first_txn_date	result_set	retention_rate_7d	txn_count_7d	user_id
2024-04-01	per_user_7d_activity	NULL	2	101
2024-04-02	per_user_7d_activity	NULL	2	102
2024-04-05	per_user_7d_activity	NULL	1	103
NULL	overall_retention	0.6667	NULL	NULL

Hints

1. Find each user's first transaction date before counting the seven-day window.
2. Retention should exclude the first transaction itself.
3. Use UNION ALL with typed NULLs to combine metric rows with per-user rows.