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
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.
Read the full Meta Data Scientist interview experience this question came from
Write a PostgreSQL query. You are given two tables:
- user_activity(user_id, activity_date, country, dau_flag)
- composer(user_id, event, event_date)
For the date '2025-09-01', calculate by country the average number of posts per Daily Active User (DAU).
Requirements:
1) Define DAUs as users with dau_flag = 1 on activity_date = '2025-09-01'.
2) Count only posts where event = 'post' and 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
- avg_posts_per_dau (rounded to 2 decimals)
5) Use a LEFT JOIN from the DAU set to the posts counts to avoid dropping DAUs with zero posts.
Tables
user_activity(user_id INT, activity_date DATE, country VARCHAR(2), dau_flag SMALLINT)
composer(user_id INT, event VARCHAR(10), event_date DATE)
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.