Quick 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.

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.

Loading coding console...