Compute Fitness App DAU
Quick 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.
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.