Point72·Data Scientist·Technical Phone Screen·Senior

Senior

Apr 2026

Summary

Point72 data scientist interview with a single heavy SQL question that covers multiple aggregation layers, conditional filtering, and edge case handling all in one shot. No fluff, just write the query and defend your logic.

Questions Asked (1)

Write a single ANSI SQL query that returns, for every customer with at least one non-canceled order: their most recent completed or shipped order timestamp across all time, the count of distinct products purchased in the last 7 days (completed/shipped only), and net spend in the last 7 days after subtracting refunds whose return timestamp falls in that same window. Customers with no activity in the window should still appear with 0s for the last two metrics.

Data ModelingProduct Analytics & MetricsSystem Design

Author's notes

This took me way longer to untangle than I expected.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by identifying customers with at least one non-canceled order using a subquery or CTE. Then compute the three metrics separately: most recent completed/shipped order timestamp, distinct product count in last 7 days, and net spend in last 7 days after refunds. Finally, left join these metrics to the customer list and coalesce nulls to zero.

Pro tip: Clarify the definition of 'last 7 days'—whether it's relative to the current date or the latest order date—and explicitly state your assumption. Also, ensure refunds are subtracted only if the return timestamp falls within the same 7-day window, which may require a separate aggregation.

1. Identify eligible customers

Use a subquery or CTE to select distinct customer IDs from orders where status is not 'canceled'. This forms the base population.

2. Compute most recent completed/shipped order timestamp

For each eligible customer, find the maximum order timestamp among orders with status 'completed' or 'shipped' across all time.

3. Calculate distinct product count in last 7 days

Filter orders to those with status 'completed' or 'shipped' and order timestamp within the last 7 days (relative to current date or latest date). Count distinct product IDs per customer.

4. Compute net spend in last 7 days

Sum order amounts for completed/shipped orders in the last 7 days, then subtract the sum of refund amounts for returns with return timestamp in the same 7-day window. Ensure refunds are linked to the correct customer.

5. Combine and handle missing values

Left join the aggregated metrics to the eligible customer list and use COALESCE to replace nulls with 0 for the count and net spend metrics.

Key Points to Mention

Use of CTEs or subqueries to modularize the query and improve readability.
Definition of 'last 7 days'—whether relative to CURRENT_DATE or the maximum order date in the dataset.
Filtering conditions: status NOT IN ('canceled') for eligibility, and status IN ('completed', 'shipped') for metrics.
Handling refunds: join returns to orders on order_id, and ensure return timestamp falls within the 7-day window.
Use of LEFT JOIN and COALESCE to include customers with no activity in the window and show 0s.
Potential need to deduplicate orders or returns if multiple records exist per order.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.