Compute Fitness App DAU
Company: DoorDash
Role: Analytics Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: hard
Interview Round: Technical Screen
You are working on a fitness app. The schema is: users(user_id BIGINT, signup_ts TIMESTAMP, timezone VARCHAR, is_test_user BOOLEAN) and app_events(event_id BIGINT, user_id BIGINT, event_ts TIMESTAMP, event_name VARCHAR). app_events.user_id joins to users.user_id. Define DAU as the number of distinct non-test users who generated at least one event on a UTC calendar day, regardless of event type. Write SQL to return, for the last 30 UTC days, one row per day with columns activity_date DATE, dau BIGINT, and dau_7d_avg NUMERIC, where dau_7d_avg is the 7-day rolling average of daily DAU ordered by activity_date.
Overview: This question evaluates skills in data manipulation and time-series analytics, including SQL joins, deduplication of non-test users, UTC date bucketing, aggregation for daily active users (DAU), and computation of rolling averages.
Read the full DoorDash Analytics Engineer interview experience this question came from
Community answers
Answer by ashkakatira1
with daily_dau as (
select
(e.event_ts at time zone 'UTC')::date as activity_date,
count(distinct e.user_id) as dau
from app_events e
join users u on u.user_id = e.user_id
where u.is_test_user = false
group by 1
),
rolling as (
select
activity_date,
dau,
avg(dau) over (
order by activity_date
rows between 6 preceding and current row
) as dau_7d_avg
from daily_dau
)
select activity_date, dau, dau_7d_avg
from rolling
where activity_date >= (now() at time zone 'UTC')::date - 29
order by activity_date;