DoorDash·Data Scientist·Technical Phone Screen·Intermediate

Intermediate

Jun 2026

Summary

DoorDash data scientist interview with a SQL-heavy technical screen. One question, pretty involved, centered on percentile filtering over a rolling window. Not the kind of thing you can wing.

Questions Asked (1)

Write a SQL query to return all orders from restaurants whose total completed-order revenue over the last 30 days falls in the bottom 25th percentile across all restaurants.

Product Analytics & MetricsData Modeling

Author's notes

The setup sounds manageable until you actually sit down to write it.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, aggregate completed orders per restaurant over the last 30 days to compute total revenue. Then, use a window function or subquery to calculate the 25th percentile threshold across all restaurants, and finally filter orders to include only those from restaurants whose revenue is at or below that threshold.

Pro tip: Clarify the definition of 'completed orders' and 'revenue' (e.g., exclude refunds, include only delivered orders) and mention that percentile calculations may vary by SQL dialect (e.g., PERCENTILE_CONT vs. APPROX_QUANTILES). Also, consider whether to include restaurants with zero completed orders in the percentile calculation.

1. Define and filter completed orders

Identify the orders table and filter for completed orders within the last 30 days. Ensure you understand what 'completed' means (e.g., status = 'delivered') and how revenue is calculated (e.g., sum of order total).

2. Aggregate revenue per restaurant

Group by restaurant_id and sum the revenue to get total completed-order revenue for each restaurant over the last 30 days.

3. Compute the 25th percentile threshold

Calculate the 25th percentile of total revenue across all restaurants. This can be done using a window function like PERCENTILE_CONT(0.25) or a subquery with ORDER BY and LIMIT/OFFSET, depending on the SQL dialect.

4. Identify restaurants below the threshold

Select restaurant_ids where total revenue is less than or equal to the 25th percentile value. Be mindful of whether to include restaurants with zero revenue.

5. Return all orders from those restaurants

Join the original orders table (filtered for last 30 days and completed status) with the list of bottom-quartile restaurants to return all relevant orders.

Key Points to Mention

Definition of 'completed orders' and 'revenue' (e.g., status filter, refunds, fees).
Time window: last 30 days relative to current date, using appropriate date functions.
Percentile calculation method: PERCENTILE_CONT, APPROX_QUANTILES, or manual ranking.
Handling of restaurants with zero completed orders (include or exclude).
SQL dialect considerations (e.g., BigQuery, PostgreSQL, Snowflake) and performance implications.
Final output: all orders (not just aggregated) from the identified restaurants.

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