DoorDash·Data Scientist·Technical Phone Screen·Intermediate

Intermediate

May 2026

Summary

DoorDash Data Scientist interview with a SQL window functions question built around an e-commerce orders scenario. Pretty technical for a phone screen but not unreasonable if you've worked with analytics queries before.

Questions Asked (1)

Given an orders table with order_id, customer_id, order_value, order_date, and city, write a SQL query that returns each order alongside the previous order value for that customer, plus the rank and percent rank of the current order value within that customer's order history, sorted by order date descending.

Product Analytics & MetricsData Modeling

Author's notes

The LAG part was fine, I've used that plenty.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Use window functions partitioned by customer_id to compute the previous order value (LAG), rank (RANK or DENSE_RANK), and percent rank (PERCENT_RANK) for each order. Then sort the final result by order_date descending. Ensure the window frame is correctly defined to include all orders for each customer.

Pro tip: Clarify whether 'previous order' means the immediately preceding order by date or by order_id, and confirm the ranking method (e.g., RANK vs DENSE_RANK) based on business needs. Also, consider how ties in order_date should be handled to avoid ambiguous results.

1. Understand the requirements

Identify the need for per-customer calculations: previous order value, rank, and percent rank. Determine the ordering column (order_date) and the partition (customer_id).

2. Choose appropriate window functions

Use LAG(order_value) OVER (PARTITION BY customer_id ORDER BY order_date) for previous value. Use RANK() or DENSE_RANK() and PERCENT_RANK() with the same partition and order.

3. Construct the SQL query

Write a SELECT statement that includes order_id, customer_id, order_value, order_date, city, and the computed window functions. Use a subquery or CTE if needed for clarity.

4. Sort the final output

Add an ORDER BY clause to sort the result by order_date descending, as requested. Ensure the sorting is applied after the window functions are computed.

5. Validate and optimize

Check for edge cases (e.g., first order per customer, ties in dates) and consider performance implications of window functions on large datasets.

Key Points to Mention

Use of PARTITION BY customer_id to compute metrics per customer.
LAG function to retrieve previous order value, with proper ordering by order_date.
RANK or DENSE_RANK for ranking order values within each customer's history.
PERCENT_RANK to calculate the relative standing of each order value.
Handling of ties in order_date or order_value to ensure deterministic results.
Final ORDER BY order_date DESC to meet the sorting requirement.

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