Calculating Daily Active Users in SQL
WHAT IT TESTS: Metric definition plus dedup SQL. OUTLINE: Need per-event user_id and timestamp and a clear active definition; count distinct user_id within the day in a fixed timezone. RED FLAG: Counting rows or fuzzy date-boundary and timezone handling.
WHAT IT TESTS: Whether you define a metric precisely and write correct deduplicating SQL. ANSWER OUTLINE: Required data: an events table with user_id and a timestamp, plus an agreed definition of active. DAU is COUNT(DISTINCT user_id) over events whose timestamp falls within the chosen day in a fixed timezone, using half-open bounds to avoid double counting the boundary. State the timezone and active definition explicitly. RED FLAG: COUNT(*) instead of COUNT(DISTINCT user_id), ambiguous timezone, or BETWEEN that includes midnight twice.
Read the original → interview
- #sql
- #dau
- #active-users
- #metrics
- #analytics
Get five bites like this every day.
Tezvyn delivers a daily feed of 60-second tech bites with quizzes to lock in what you learn.