SQL

What's being tested

These questions test SQL analytical data manipulation for marketplace metrics: aggregating orders, joining customer/restaurant/event tables, grouping by time, ranking entities, and computing rates or percentiles. Interviewers are probing whether you can translate ambiguous DoorDash business definitions into correct SQL or Python logic with clean handling of timestamps, nulls, duplicates, and windowed metrics.

Patterns & templates

• Time-based aggregation — use `DATE_TRUNC`(`'month'`, `created_at`) or `EXTRACT` for monthly metrics; confirm timezone and order-status filters upfront.

• Conditional aggregation — compute rates with `SUM`(`CASE WHEN condition THEN 1 ELSE 0 END) * 1.0 / `COUNT`(*); guard against zero denominators.

• Entity ranking — use `ROW_NUMBER`, `RANK`, or `DENSE_RANK` with `PARTITION BY` for top customers, restaurants, or months; define tie-breaking explicitly.

• Windowed rolling metrics — use `AVG`, `SUM`, or `COUNT` over `ROWS BETWEEN` or date-range windows; distinguish row-count windows from calendar windows.

• Percentiles and quartiles — use `PERCENTILE_CONT`, `NTILE`(`4`), or `APPROX_PERCENTILE`; know whether the question needs exact thresholds or segmentation buckets.

• Event sequencing — use `LAG`, `LEAD`, and `ROW_NUMBER()` OVER (`PARTITION BY order_id ORDER BY event_ts`) to identify request flows and latest valid states.

• Python equivalent — map SQL patterns to `pandas`: `groupby`, `agg`, `merge`, `rank`, `rolling`, `quantile`, and `shift`; watch memory for large tables.

Common pitfalls

Pitfall: Counting rows instead of distinct orders or customers can inflate metrics after joins, especially with order-event or item-level tables.

Pitfall: Treating “late,” “cold,” or “completed” as obvious definitions without asking for SLA thresholds, cancellation handling, and timestamp source.

Pitfall: Using `WHERE` filters after a `LEFT JOIN` can silently turn it into an inner join and drop customers or restaurants with zero activity.

Practice these

The practice cards below cover the canonical variants — solve all of them and time yourself.