Coding questions · Point72
Write SQL for recent customer activity
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.
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.
Recent Customer Activity with Net Spend and Last Order Timestamp
Table customers
| column | type | constraints |
|---|---|---|
| customer_id | INT | PRIMARY KEY, NOT NULL |
| name | VARCHAR(50) | NOT NULL |
Sample data:
| customer_id | name |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Chen |
Table orders
| column | type | constraints |
|---|---|---|
| order_id | INT | PRIMARY KEY, NOT NULL |
| customer_id | INT | NOT NULL |
| order_ts | TIMESTAMP | NOT NULL |
| status | VARCHAR(20) | NOT NULL |
Sample data:
| customer_id | order_id | order_ts | status |
|---|---|---|---|
| 1 | 1001 | 2025-08-25 22:00:00 | COMPLETED |
| 1 | 1002 | 2025-08-26 09:00:00 | SHIPPED |
| 2 | 1003 | 2025-08-28 12:30:00 | CANCELED |
| 2 | 1004 | 2025-08-31 15:45:00 | COMPLETED |
| 3 | 1005 | 2025-09-01 20:10:00 | COMPLETED |
Table order_items
| column | type | constraints |
|---|---|---|
| order_id | INT | NOT NULL |
| product_id | VARCHAR(20) | NOT NULL |
| qty | INT | NOT NULL |
| unit_price | DECIMAL(10,2) | NOT NULL |
Sample data:
| order_id | product_id | qty | unit_price |
|---|---|---|---|
| 1001 | p10 | 1 | 20 |
| 1002 | p10 | 2 | 19 |
| 1002 | p11 | 1 | 5 |
| 1003 | p11 | 3 | 5.5 |
| 1004 | p12 | 1 | 30 |
| 1005 | p10 | 1 | 18.5 |
| 1005 | p13 | 2 | 7 |
Table returns
| column | type | constraints |
|---|---|---|
| order_id | INT | NOT NULL |
| product_id | VARCHAR(20) | NOT NULL |
| qty | INT | NOT NULL |
| refund_amount | DECIMAL(10,2) | NOT NULL |
| return_ts | TIMESTAMP | NOT NULL |
Sample data:
| order_id | product_id | qty | refund_amount | return_ts |
|---|---|---|---|---|
| 1002 | p10 | 1 | 19 | 2025-08-30 10:00:00 |
| 1004 | p12 | 1 | 30 | 2025-09-02 10:00:00 |
| 1001 | p10 | 1 | 20 | 2025-08-26 08:00:00 |
Expected output
One row per customer who has at least one order with status IN ('COMPLETED','SHIPPED'). For each such customer: the timestamp of their last non-canceled order (last_order_ts), the count of distinct products bought in qualifying orders in the window [2025-08-26 00:00:00, 2025-09-01 23:59:59] (distinct_products_last_7d), and the net spend in that window (net_spend_last_7d) defined as gross order item spend from qualifying orders minus refunds whose return_ts is in the same 7-day window.
| customer_id | distinct_products_last_7d | last_order_ts | name | net_spend_last_7d |
|---|---|---|---|---|
| 1 | 2 | 2025-08-26 09:00:00 | Alice | 4.00 |
| 2 | 1 | 2025-08-31 15:45:00 | Bob | 30.00 |
| 3 | 2 | 2025-09-01 20:10:00 | Chen | 32.50 |
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. - Use PostgreSQL
TO_CHARto formatlast_order_tsasYYYY-MM-DD HH24:MI:SS.