Write SQL for Transactions and Customers
Company: Affirm
Role: Data Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You are given two tables:
- customers(customer_id INT PRIMARY KEY, signup_date DATE, region VARCHAR, segment VARCHAR)
- transactions(transaction_id INT PRIMARY KEY, customer_id INT, amount DECIMAL(10,
2), status VARCHAR, transaction_ts TIMESTAMP)
Assume status = 'SUCCESS' indicates a completed purchase.
Write SQL to answer the following:
1) Using a CTE, compute for the last three full calendar months the total successful revenue and number of active customers per region (an active customer has at least one successful transaction in that month). Return month (YYYY-MM), region, revenue, active_customers, and also rank regions by revenue within each month using a window function.
2) For each customer, return their top three successful transactions by amount. Break ties by earlier transaction_ts. Output customer_id, transaction_id, amount, transaction_ts, and the per-customer rank (use a window function).
3) Using a CTE and window functions, compute the average number of days between consecutive successful transactions per customer, then aggregate to the segment level over the past 180 days. Return segment, customers_covered, and avg_gap_days.
Overview: This question evaluates proficiency with SQL CTEs, window functions, aggregation, per-group top-N queries, and time-based analytics on transactional and customer data.
Monthly Regional Revenue and Active Customers with Ranking
You are given two tables:
- customers(customer_id INT PRIMARY KEY, signup_date DATE, region VARCHAR, segment VARCHAR)
- transactions(transaction_id INT PRIMARY KEY, customer_id INT, amount DECIMAL(10,2), status VARCHAR, transaction_ts TIMESTAMP)
Assume status = 'SUCCESS' indicates a completed purchase.
Using a CTE, compute for the calendar months 2025-03, 2025-04, and 2025-05 the total successful revenue and number of active customers per region. An active customer is one who has at least one successful transaction in that month. Return the month formatted as 'YYYY-MM', region, revenue, active_customers, and also rank regions by revenue within each month using a window function (1 = highest revenue).
Tables
customers(customer_id INT, signup_date DATE, region VARCHAR(20), segment VARCHAR(20))
transactions(transaction_id INT, customer_id INT, amount DECIMAL(10,2), status VARCHAR(20), transaction_ts TIMESTAMP)
Hints
- First aggregate successful transactions by month and region in a CTE, using DATE_TRUNC and COUNT(DISTINCT customer_id).
- In the outer query, apply a RANK() window function partitioned by month and ordered by revenue DESC.
Top 3 Successful Transactions per Customer
For each customer, return their top three successful transactions by amount.
Use `transactions` and include only rows where `status = 'SUCCESS'`. Rank transactions within each `customer_id` by:
1. higher `amount` first
2. earlier `transaction_ts` first as the tie-breaker
Return these columns:
- `customer_id`
- `transaction_id`
- `amount`
- `transaction_ts`, formatted as `YYYY-MM-DD HH24:MI:SS`
- `txn_rank`: the per-customer `ROW_NUMBER()` rank
Keep only ranks 1 through 3 and order by `customer_id`, then `txn_rank`.
Tables
customers(customer_id INT, signup_date DATE, region VARCHAR(20), segment VARCHAR(20))
transactions(transaction_id INT, customer_id INT, amount DECIMAL(10,2), status VARCHAR(20), transaction_ts TIMESTAMP)
Hints
- Filter to successful transactions before ranking.
- Use ROW_NUMBER() partitioned by customer_id.
Average Days Between Transactions by Segment (Last 180 Days)
You are given two tables:
- customers(customer_id INT PRIMARY KEY, signup_date DATE, region VARCHAR, segment VARCHAR)
- transactions(transaction_id INT PRIMARY KEY, customer_id INT, amount DECIMAL(10,2), status VARCHAR, transaction_ts TIMESTAMP)
Assume status = 'SUCCESS' indicates a completed purchase.
Using a CTE and window functions, compute the average number of days between consecutive successful transactions per customer over the period from 2024-12-04 to 2025-06-01 (inclusive). Then aggregate these per-customer averages to the segment level. Return segment, customers_covered (number of customers that had at least two successful transactions in this window), and avg_gap_days (average of the per-customer average gaps, in days).
Tables
customers(customer_id INT, signup_date DATE, region VARCHAR(20), segment VARCHAR(20))
transactions(transaction_id INT, customer_id INT, amount DECIMAL(10,2), status VARCHAR(20), transaction_ts TIMESTAMP)
Hints
- First filter to the desired 2024-12-04 to 2025-06-01 window, then use LAG over (PARTITION BY customer_id ORDER BY transaction_ts) to get previous transaction dates.
- Average the day differences per customer, then join to customers and aggregate those per-customer averages by segment.