Coding questions · Meta
Calculate posts per DAU by country today
This question evaluates data aggregation and join semantics, date-based filtering, inclusion of zero-event users, and numeric rounding when computing average posts per DAU.
Given two tables:
- user_activity(user_id INT, activity_date DATE, country STRING, dau_flag TINYINT CHECK(dau_flag IN (0,1)))
- composer(user_id INT, event STRING CHECK(event IN ('enter','post','cancel')), event_date DATE) Calculate, by country, the average number of posts per Daily Active User (DAU) for "today" (2025-09-01). Requirements: 1) Define DAUs as users with dau_flag=1 on activity_date='2025-09-01'. 2) Count only posts that occurred on event_date='2025-09-01'. 3) Include DAUs with zero posts in the denominator. 4) Return one row per country present among DAUs, with columns country and avg_posts_per_dau (rounded to 2 decimals). 5) Use a LEFT JOIN from the DAU set to posts to avoid dropping zero-post users. Example sample data:
user_activity
user_id | activity_date | country | dau_flag
1 | 2025-09-01 | US | 1
2 | 2025-09-01 | US | 1
3 | 2025-09-01 | CA | 1
4 | 2025-09-01 | US | 0
composer
user_id | event | event_date
1 | post | 2025-09-01
1 | post | 2025-09-01
2 | enter | 2025-09-01
2 | cancel | 2025-09-01
3 | post | 2025-09-01
Average Posts per DAU by Country on 2025-09-01
Table user_activity
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| activity_date | DATE | NOT NULL |
| country | VARCHAR(2) | NOT NULL |
| dau_flag | SMALLINT | NOT NULL |
Sample data:
| activity_date | country | dau_flag | user_id |
|---|---|---|---|
| 2025-09-01 | US | 1 | 1 |
| 2025-09-01 | US | 1 | 2 |
| 2025-09-01 | CA | 1 | 3 |
| 2025-09-01 | US | 0 | 4 |
| 2025-09-01 | UK | 1 | 5 |
| 2025-09-01 | CA | 0 | 6 |
| 2025-08-31 | US | 1 | 7 |
Table composer
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| event | VARCHAR(10) | NOT NULL |
| event_date | DATE | NOT NULL |
Sample data:
| event | event_date | user_id |
|---|---|---|
| post | 2025-09-01 | 1 |
| post | 2025-09-01 | 1 |
| enter | 2025-09-01 | 2 |
| cancel | 2025-09-01 | 2 |
| post | 2025-09-01 | 3 |
| post | 2025-08-31 | 3 |
| post | 2025-09-01 | 4 |
| enter | 2025-09-01 | 5 |
Expected output
One row per country among DAUs on 2025-09-01. avg_posts_per_dau is total posts on 2025-09-01 by those DAUs divided by number of DAUs in that country, rounded to 2 decimals. DAUs with zero posts must be included in the denominator.
| avg_posts_per_dau | country |
|---|---|
| 1 | CA |
| 0 | UK |
| 1 | US |
Hints
- Build a DAU set filtered to activity_date = '2025-09-01' and dau_flag = 1.
- Aggregate posts per user for event = 'post' on '2025-09-01', then LEFT JOIN to DAUs and compute sum(posts)/count(DAUs) per country.