Impute, join, and upsert using SQL and Python

Write both SQL and Python (pandas) to complete the following data-manipulation tasks. Assume today is 2025-09-01 for any time filters. Schema:

customers(customer_id INT, signup_date DATE, age INT, tier TEXT)
events(event_id INT, customer_id INT, event_date DATE, event_type TEXT, amount DECIMAL(10,2))
staging_events(event_id INT, customer_id INT, event_date DATE, event_type TEXT, amount DECIMAL(10,2))
payments(payment_id INT, customer_id INT, payment_date DATE, amount DECIMAL(10,2))
Sample data (minimal):
customers
+-------------+-------------+-----+--------+
| customer_id | signup_date | age | tier |
+-------------+-------------+-----+--------+
| 1 | 2025-08-20 | 34 | gold |
| 2 | 2025-08-28 | NULL| silver |
| 3 | 2025-08-29 | 27 | silver |
| 4 | 2025-08-30 | NULL| bronze |
+-------------+-------------+-----+--------+

events
+----------+-------------+-------------+------------+--------+
| event_id | customer_id | event_date | event_type | amount |
+----------+-------------+-------------+------------+--------+
| 10 | 1 | 2025-08-28 | purchase | 120.00 |
| 11 | 2 | 2025-08-30 | purchase | 80.00 |
| 12 | 2 | 2025-09-01 | refund | -20.00 |
| 13 | 3 | 2025-08-26 | purchase | 60.00 |
| 14 | 4 | 2025-08-27 | page_view | NULL |
+----------+-------------+-------------+------------+--------+

staging_events
+----------+-------------+-------------+------------+--------+
| event_id | customer_id | event_date | event_type | amount |
+----------+-------------+-------------+------------+--------+
| 12 | 2 | 2025-09-01 | refund | -20.00 |
| 15 | 1 | 2025-08-31 | purchase | 120.00 |
+----------+-------------+-------------+------------+--------+

payments
+------------+-------------+--------------+--------+
| payment_id | customer_id | payment_date | amount |
+------------+-------------+--------------+--------+
| 100 | 1 | 2025-08-31 | 120.00 |
| 101 | 3 | 2025-08-31 | 60.00 |
+------------+-------------+--------------+--------+

Tasks: A) Impute missing ages in customers using the median age within tier, falling back to the global median if a tier’s median is null; return customer_id and imputed_age. B) Upsert from staging_events into events: insert rows whose event_id does not exist in events; if an event_id exists in both with different values, keep a single row with the latest event_date and its values; return the deduplicated events table. C) For the last 7 days inclusive (2025-08-26 to 2025-09-01), compute per-tier net revenue where purchase amounts are positive and refund amounts are negative; exclude non-monetary events (like page_view); use imputed_age and restrict to customers aged 18–65; return tier, total_revenue_7d, and customer_count_7d. D) Compute 7-day retention: among customers with a monetary event in the last 7 days, what fraction also had any event in the prior 7-day window (2025-08-19 to 2025-08-25)? Return one row per tier with retention_rate. Provide both SQL and pandas solutions; state any indexing choices and how you would test correctness.

Impute Missing Ages by Tier Median with Global Fallback

Using the customers table, write a SQL query to impute missing ages. For each customer, compute an imputed_age defined as:

• If age is not NULL, use the existing age.
• If age is NULL, use the median age of customers in the same tier (ignoring NULL ages).
• If a tier has no non-NULL ages (so its tier median is NULL), fall back to the global median age across all customers (ignoring NULL ages). Return one row per customer with columns: customer_id and imputed_age.

Table customers

column	type	constraints
customer_id	INT	PRIMARY KEY
signup_date	DATE	NOT NULL
age	INT
tier	VARCHAR(10)	NOT NULL

Sample data:

age	customer_id	signup_date	tier
34	1	2025-05-20	gold
NULL	2	2025-05-28	silver
27	3	2025-05-29	silver
NULL	4	2025-05-30	bronze

Expected output

One row per customer with customer_id and imputed_age, where missing ages are filled using tier median or global median as a fallback.

customer_id	imputed_age
1	34
2	27
3	27
4	30.5

Hints

1. Compute the median age per tier and the global median age using PERCENTILE_CONT.
2. Use COALESCE to prefer the actual age, then the tier median, then the global median.

Upsert Staging Events into Events with Latest-Event Deduplication

You have a main events table and a staging_events table with the same schema. Write a SQL query that returns the deduplicated set of events as if you had upserted staging_events into events with the following rules:

1. If an event_id exists only in staging_events, include it (insert case).
2. If an event_id exists only in events, keep it.
3. If an event_id exists in both tables, keep exactly one row: the row with the latest event_date (and its associated values). If the dates are the same, any one of the identical rows is fine. Return the resulting events set with columns: event_id, customer_id, event_date, event_type, amount.

Table events

column	type	constraints
event_id	INT	PRIMARY KEY
customer_id	INT	NOT NULL
event_date	DATE	NOT NULL
event_type	VARCHAR(20)	NOT NULL
amount	DECIMAL(10,2)

Sample data:

amount	customer_id	event_date	event_id	event_type
120	1	2025-05-28	10	purchase
80	2	2025-05-30	11	purchase
-20	2	2025-06-01	12	refund
60	3	2025-05-26	13	purchase
NULL	4	2025-05-27	14	page_view

Table staging_events

column	type	constraints
event_id	INT	PRIMARY KEY
customer_id	INT	NOT NULL
event_date	DATE	NOT NULL
event_type	VARCHAR(20)	NOT NULL
amount	DECIMAL(10,2)

Sample data:

amount	customer_id	event_date	event_id	event_type
-20	2	2025-06-01	12	refund
120	1	2025-05-31	15	purchase

Expected output

The union of events and staging_events, deduplicated by event_id keeping only the most recent event_date for each event_id.

amount	customer_id	event_date	event_id	event_type
120	1	2025-05-28	10	purchase
80	2	2025-05-30	11	purchase
-20	2	2025-06-01	12	refund
60	3	2025-05-26	13	purchase
NULL	4	2025-05-27	14	page_view
120	1	2025-05-31	15	purchase

Hints

1. Start by UNION ALL-ing events and staging_events together.
2. Use ROW_NUMBER() partitioned by event_id and ordered by event_date DESC to pick the latest row.

Per-Tier Net Revenue Over Last 7 Days with Imputed Ages

Using the customers, events, and staging_events tables, compute per-tier net revenue for the 7 days from 2025-05-26 to 2025-06-01 (inclusive). Requirements:

1. First, impute ages exactly as in Question 1 (tier median, then global median fallback); use these imputed ages to filter customers.
2. Upsert events exactly as in Question 2 (union events and staging_events, deduplicate by event_id keeping the latest event_date), and use this deduplicated events set.
3. Consider only monetary events: event_type IN ('purchase', 'refund'). Treat purchase amounts as positive and refund amounts as negative (use the amount field as given: purchases are positive, refunds negative).
4. Restrict to events with event_date BETWEEN '2025-05-26' AND '2025-06-01' (inclusive).
5. Restrict to customers whose imputed_age is between 18 and 65 inclusive. For each tier, return:

• tier
• total_revenue_7d: the sum of amount over the 7-day window
• customer_count_7d: the number of distinct customers in that tier who had at least one monetary event in that window (after the age filter).

Table customers

column	type	constraints
customer_id	INT	PRIMARY KEY
signup_date	DATE	NOT NULL
age	INT
tier	VARCHAR(10)	NOT NULL

Sample data:

age	customer_id	signup_date	tier
34	1	2025-05-20	gold
NULL	2	2025-05-28	silver
27	3	2025-05-29	silver
NULL	4	2025-05-30	bronze

Table events

column	type	constraints
event_id	INT	PRIMARY KEY
customer_id	INT	NOT NULL
event_date	DATE	NOT NULL
event_type	VARCHAR(20)	NOT NULL
amount	DECIMAL(10,2)

Sample data:

amount	customer_id	event_date	event_id	event_type
120	1	2025-05-28	10	purchase
80	2	2025-05-30	11	purchase
-20	2	2025-06-01	12	refund
60	3	2025-05-26	13	purchase
NULL	4	2025-05-27	14	page_view

Table staging_events

column	type	constraints
event_id	INT	PRIMARY KEY
customer_id	INT	NOT NULL
event_date	DATE	NOT NULL
event_type	VARCHAR(20)	NOT NULL
amount	DECIMAL(10,2)

Sample data:

amount	customer_id	event_date	event_id	event_type
-20	2	2025-06-01	12	refund
120	1	2025-05-31	15	purchase

Expected output

One row per tier with total net revenue and distinct customer count over 2025-05-26 to 2025-06-01, using imputed ages and deduplicated events.

customer_count_7d	tier	total_revenue_7d
1	gold	240
2	silver	120

Hints

1. Reuse the median-imputation logic from Question 1 as a CTE to create an imputed_customers table.
2. Reuse the upsert/deduplication logic from Question 2 to define a deduped_events CTE, then filter by date range and monetary event types before aggregating by tier.

7-Day Retention by Tier Using Event Windows

Using the customers, events, and staging_events tables, compute 7-day retention by tier. Definitions:

• Last 7-day window ("current window"): event_date BETWEEN '2025-05-26' AND '2025-06-01' (inclusive).
• Prior 7-day window: event_date BETWEEN '2025-05-19' AND '2025-05-25' (inclusive).
• A "monetary event" is an event with event_type IN ('purchase', 'refund'). Steps:

1. As in Question 2, combine events and staging_events and deduplicate by event_id, keeping the row with the latest event_date.
2. Identify, for each tier, the set of customers who had at least one monetary event in the current window.
3. Among those customers, find which ones also had any event (of any type) in the prior window. For each tier, compute retention_rate = (number of customers in that tier who had a monetary event in the current window AND any event in the prior window) / (number of customers in that tier who had a monetary event in the current window). Return one row per tier with columns: tier and retention_rate.

Table customers

column	type	constraints
customer_id	INT	PRIMARY KEY
signup_date	DATE	NOT NULL
age	INT
tier	VARCHAR(10)	NOT NULL

Sample data:

age	customer_id	signup_date	tier
34	1	2025-05-20	gold
NULL	2	2025-05-28	silver
27	3	2025-05-29	silver
NULL	4	2025-05-30	bronze

Table events

column	type	constraints
event_id	INT	PRIMARY KEY
customer_id	INT	NOT NULL
event_date	DATE	NOT NULL
event_type	VARCHAR(20)	NOT NULL
amount	DECIMAL(10,2)

Sample data:

amount	customer_id	event_date	event_id	event_type
120	1	2025-05-28	10	purchase
80	2	2025-05-30	11	purchase
-20	2	2025-06-01	12	refund
60	3	2025-05-26	13	purchase
NULL	4	2025-05-27	14	page_view

Table staging_events

column	type	constraints
event_id	INT	PRIMARY KEY
customer_id	INT	NOT NULL
event_date	DATE	NOT NULL
event_type	VARCHAR(20)	NOT NULL
amount	DECIMAL(10,2)

Sample data:

amount	customer_id	event_date	event_id	event_type
-20	2	2025-06-01	12	refund
120	1	2025-05-31	15	purchase

Expected output

One row per tier with the fraction of customers (having monetary events in 2025-05-26 to 2025-06-01) who also had any event in 2025-05-19 to 2025-05-25. With the sample data, no customers have events in the prior window, so retention is 0 for tiers that have monetary events in the current window.

retention_rate	tier
0	gold
0	silver

Hints

1. First, build a deduplicated events set using the same ROW_NUMBER() pattern as in Question 2.
2. Create two distinct customer sets: those with monetary events in the current window, and those with any events in the prior window; then join and aggregate to compute the fraction.