Calculate posts per DAU by country today
Overview
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.
Meta
Oct 13, 2025, 9:49 PM
Data Scientist
Technical Screen
Data Manipulation (SQL/Python)
0
0
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