Calculate posts per DAU by country today
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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
Quick Answer: 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.