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

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.

How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

What difficulty level is this interview question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Technical Screen rounds at Meta.

What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Meta during technical interviews.

Calculate posts per DAU by country today

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

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

Calculate posts per DAU by country today

Quick Overview

Comments (0)

Calculate posts per DAU by country today

Quick Overview

Comments (0)