DoorDash·Data Scientist·Technical Phone Screen·Intermediate

Intermediate

Jul 2026

Summary

DoorDash data scientist interview with a SQL-heavy technical screen. One question but it packed in a lot, window functions across multiple partitions in a single query. Felt like a reasonable test of whether you actually know SQL or just know the syntax.

Questions Asked (1)

Given an orders table with columns for order ID, user ID, order date, city, and order value, write a single SQL query that returns each order alongside the previous order value for that same user (NULL if it's their first), the rank of that order's value within its city from highest to lowest, and the percent rank of the order value within its city.

Product Analytics & MetricsData Modeling

Author's notes

Three window functions in one query.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Use window functions to compute the previous order value, city rank, and percent rank in a single query. Partition by user_id for the lag, and by city for the ranking functions, ordering appropriately. Ensure the final output includes all required columns and handles NULLs correctly.

Pro tip: Always clarify the ordering for 'previous order' (e.g., by order date) and whether ties in rank should use RANK or DENSE_RANK; also mention that PERCENT_RANK returns a value between 0 and 1, which might need formatting for business users.

1. Identify required window functions

Determine that LAG is needed for previous order value, RANK for city rank, and PERCENT_RANK for percent rank. Specify the partitions and orderings for each.

2. Define partitions and orderings

For LAG, partition by user_id and order by order_date (or order_id as tiebreaker). For RANK and PERCENT_RANK, partition by city and order by order_value descending.

3. Write the SQL query

Construct a SELECT statement with the original columns plus the three window functions. Use OVER clauses with appropriate PARTITION BY and ORDER BY.

4. Handle edge cases and verify

Check that the first order per user returns NULL for previous value, and that ranking functions handle ties correctly. Consider if any filters or additional ordering are needed.

Key Points to Mention

Use of LAG() window function with PARTITION BY user_id ORDER BY order_date
Use of RANK() or DENSE_RANK() with PARTITION BY city ORDER BY order_value DESC
Use of PERCENT_RANK() with PARTITION BY city ORDER BY order_value DESC
Handling of NULLs for first order per user
Tie-breaking in ordering (e.g., by order_id) to ensure deterministic results
Difference between RANK and DENSE_RANK and when to use each

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