← DoorDash Interview Insights

DoorDash·Software Engineer·Technical Phone Screen·Intermediate

Intermediate
May 2026

Summary

Analytics Engineer screen at DoorDash, basically one meaty SQL question the whole time. The problem was well-scoped but had enough layers to trip you up if you weren't careful about UTC handling and window functions.

Questions Asked (1)

Q1

Given a users table and an app_events table for a fitness app, write SQL to return one row per UTC calendar day for the last 30 days, showing the count of distinct non-test users with at least one event (DAU), plus a 7-day rolling average of that DAU ordered by date.

Product Analytics & MetricsData Modeling
Author's notes

The join and filter parts were fine, excluding test users is just a WHERE clause.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by generating a complete date series for the last 30 days using a recursive CTE or a date dimension table to ensure all days are represented, even those with zero events. Then join to the app_events and users tables, filtering out test users and aggregating distinct user counts per day. Finally, compute the 7-day rolling average using a window function and order by date.

Pro tip: Explicitly state your assumptions about test user identification (e.g., email domain or a flag) and timezone handling (events stored in UTC). This shows attention to data quality and prevents ambiguity.

1. Generate date series

Create a list of all UTC calendar days for the last 30 days using a recursive CTE or a date dimension table. This ensures days with no events are included.

2. Filter and aggregate DAU

Join the date series to app_events and users, filter out test users, and count distinct non-test users with at least one event per day.

3. Compute rolling average

Use a window function to calculate the 7-day rolling average of DAU, ordering by date and including the current day and the six preceding days.

4. Finalize output

Select the date, DAU, and rolling average, ensuring the result is ordered by date ascending and limited to the last 30 days.

Key Points to Mention

  • Use of a date series (e.g., recursive CTE or calendar table) to include days with zero events
  • Definition of 'test users' and how to filter them (e.g., email domain, user flag)
  • Distinct count of users with at least one event per day (DAU)
  • 7-day rolling average calculation using window functions (e.g., AVG OVER ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
  • Timezone handling: ensure events are bucketed by UTC calendar day
  • Ordering by date and limiting to last 30 days

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