Quick Overview

This question evaluates SQL and pandas proficiency in data manipulation tasks including missing-value imputation, tiered aggregation, joins and upsert/deduplication, time-windowed revenue computation, cohort retention analysis, and handling of monetary versus non-monetary events within a relational schema.

Impute, join, and upsert using SQL and Python

Company: Capital One

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

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.

Overview: This question evaluates SQL and pandas proficiency in data manipulation tasks including missing-value imputation, tiered aggregation, joins and upsert/deduplication, time-windowed revenue computation, cohort retention analysis, and handling of monetary versus non-monetary events within a relational schema.

Read the full Capital One Data Scientist interview experience this question came from

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.

Tables

customers(customer_id INT, signup_date DATE, age INT, tier VARCHAR(10))

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.

Tables

events(event_id INT, customer_id INT, event_date DATE, event_type VARCHAR(20), amount DECIMAL(10,2))

staging_events(event_id INT, customer_id INT, event_date DATE, event_type VARCHAR(20), amount DECIMAL(10,2))

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).

Tables

customers(customer_id INT, signup_date DATE, age INT, tier VARCHAR(10))

events(event_id INT, customer_id INT, event_date DATE, event_type VARCHAR(20), amount DECIMAL(10,2))

staging_events(event_id INT, customer_id INT, event_date DATE, event_type VARCHAR(20), amount DECIMAL(10,2))

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.

Tables

customers(customer_id INT, signup_date DATE, age INT, tier VARCHAR(10))

events(event_id INT, customer_id INT, event_date DATE, event_type VARCHAR(20), amount DECIMAL(10,2))

staging_events(event_id INT, customer_id INT, event_date DATE, event_type VARCHAR(20), amount DECIMAL(10,2))

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.

Loading coding console...