Write SQL for recent customer activity
Company: Point72
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Online Assessment
Write a single ANSI‑SQL query that produces, for every customer who has at least one non‑canceled order ever: (1) last_order_ts across all time considering only status IN ('COMPLETED','SHIPPED'); (2) distinct_products_last_7d = count of distinct product_id purchased in the last 7 days; (3) net_spend_last_7d = sum(qty*unit_price) for orders with status IN ('COMPLETED','SHIPPED') in the last 7 days minus refunds recorded in the last 7 days. If a customer has no qualifying activity in the last 7 days, return 0 and 0.00 for the metrics but still report their last_order_ts if any. Consider 'today' = 2025-09-01 23:59:59 and the rolling 7‑day window as [2025-08-26 00:00:00, 2025-09-01 23:59:59]. Ties for last_order_ts are resolved by the max timestamp.
Schema and small ASCII sample tables:
customers
+-------------+-------+
| customer_id | name |
+-------------+-------+
| 1 | Alice |
| 2 | Bob |
| 3 | Chen |
orders
+----------+-------------+---------------------+-----------+
| order_id | customer_id | order_ts | status |
+----------+-------------+---------------------+-----------+
| 1001 | 1 | 2025-08-25 22:00:00 | COMPLETED |
| 1002 | 1 | 2025-08-26 09:00:00 | SHIPPED |
| 1003 | 2 | 2025-08-28 12:30:00 | CANCELED |
| 1004 | 2 | 2025-08-31 15:45:00 | COMPLETED |
| 1005 | 3 | 2025-09-01 20:10:00 | COMPLETED |
order_items
+----------+------------+-----+------------+
| order_id | product_id | qty | unit_price |
+----------+------------+-----+------------+
| 1001 | p10 | 1 | 20.00 |
| 1002 | p10 | 2 | 19.00 |
| 1002 | p11 | 1 | 5.00 |
| 1003 | p11 | 3 | 5.50 |
| 1004 | p12 | 1 | 30.00 |
| 1005 | p10 | 1 | 18.50 |
| 1005 | p13 | 2 | 7.00 |
returns
+----------+------------+-----+---------------+---------------------+
| order_id | product_id | qty | refund_amount | return_ts |
+----------+------------+-----+---------------+---------------------+
| 1002 | p10 | 1 | 19.00 | 2025-08-30 10:00:00 |
| 1004 | p12 | 1 | 30.00 | 2025-09-02 10:00:00 |
| 1001 | p10 | 1 | 20.00 | 2025-08-26 08:00:00 |
Requirements and clarifications:
- Only orders with status IN ('COMPLETED','SHIPPED') contribute to last_order_ts and spending; CANCELED orders contribute to neither.
- distinct_products_last_7d counts unique product_id across qualifying orders in the 7‑day window.
- net_spend_last_7d subtracts refunds with return_ts in the 7‑day window (even if the original order was earlier). If no orders ever, exclude the customer from the output; if orders exist but none in the last 7 days, metrics are 0 and 0.00.
- Write one query; use common table expressions if desired; avoid vendor‑specific extensions beyond ANSI SQL/window functions.
Overview: This question evaluates SQL data-manipulation and analytics competencies including joining and filtering multiple tables, time-windowed aggregations, distinct counts, monetary aggregation with refunds, and handling status-based inclusion and edge cases.
Read the full Point72 Data Scientist interview experience this question came from
Write a single PostgreSQL query that produces, for every customer who has at least one non-canceled order ever (i.e., at least one order with status IN ('COMPLETED','SHIPPED')): (1) last_order_ts across all time, rendered as `YYYY-MM-DD HH24:MI:SS`, considering only status IN ('COMPLETED','SHIPPED'); (2) distinct_products_last_7d = count of distinct product_id purchased in the last 7 days; (3) net_spend_last_7d = sum(qty*unit_price) for orders with status IN ('COMPLETED','SHIPPED') in the last 7 days minus refunds recorded in the last 7 days.
If a customer has no qualifying activity in the last 7 days, return 0 and 0.00 for the metrics but still report their last_order_ts if any. Consider 'today' = 2025-09-01 23:59:59 and the rolling 7‑day window as [2025-08-26 00:00:00, 2025-09-01 23:59:59]. Ties for last_order_ts are resolved by the max timestamp.
Requirements and clarifications:
- Only orders with status IN ('COMPLETED','SHIPPED') contribute to last_order_ts and spending; CANCELED orders contribute to neither.
- distinct_products_last_7d counts unique product_id across qualifying orders in the 7‑day window.
- net_spend_last_7d subtracts refunds with return_ts in the 7‑day window (even if the original order was earlier). If no orders ever, exclude the customer from the output; if orders exist but none in the last 7 days, metrics are 0 and 0.00.
- Write one PostgreSQL query; use common table expressions if desired.
Tables
customers(customer_id INT, name VARCHAR(50))
orders(order_id INT, customer_id INT, order_ts TIMESTAMP, status VARCHAR(20))
order_items(order_id INT, product_id VARCHAR(20), qty INT, unit_price DECIMAL(10,2))
returns(order_id INT, product_id VARCHAR(20), qty INT, refund_amount DECIMAL(10,2), return_ts TIMESTAMP)
Hints
- Build one CTE for each customer's last qualifying order timestamp, then separate CTEs for seven-day purchases and seven-day refunds.
- Refunds are filtered by `return_ts`, even when the original order occurred before the seven-day window.