Given tables User(user_id, user_email, account_id), Meeting(meeting_id, start_time, end_time) and Activity(meeting_id, user_id, activity_type in {join_meeting, leave_meeting}, timestamp), write a SQL query to compute the Daily Active Users (DAU) for every day in the past 1 month. DAU is the number of unique active users on a particular day and a user is considered active on day X if he/she has an account with Zoom and he/she ever joins a meeting with that account whose start time is on day X.
Asked in the phone screen stage. Output columns: Date, DAU. Problem #1a.
Output
- One row for every calendar date in the past month, including dates with no active users.
- Columns named
Date and DAU.
- Order by
Date ascending.
