Quick Overview

This question evaluates the ability to perform data manipulation and metric computation across relational tables using SQL or Python, specifically measuring daily active users and video-call participation for a geographic subset.

Compute French DAU video-call percentage yesterday

Company: Meta

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Onsite

Compute the percentage of daily active users (DAU) from France who were on at least one video call yesterday (2025-08-31 UTC). DAU is defined as users with any event on 2025-08-31 in events. A user is "from France" if users.country_code = 'FR'. A user counts as having a video call if they appeared as caller or callee in calls with media_type = 'video' and started_at on 2025-08-31 UTC. Return a single row with columns denom_dau_fr, numer_video_callers_fr, pct_video_callers_fr (as a decimal between 0 and 1). Schemas: - users(user_id INT, country_code CHAR(2), created_at TIMESTAMP) - events(event_id INT, user_id INT, event_type TEXT, event_ts TIMESTAMP) - calls(call_id INT, caller_id INT, callee_id INT, started_at TIMESTAMP, media_type TEXT) Sample rows (subsets): users +---------+--------------+ | user_id | country_code | +---------+--------------+ | 1 | FR | | 2 | FR | | 3 | US | | 4 | FR | +---------+--------------+ events +----------+---------+------------+---------------------+ | event_id | user_id | event_type | event_ts | +----------+---------+------------+---------------------+ | 501 | 1 | app_open | 2025-08-31 10:00:00 | | 502 | 2 | search | 2025-08-31 11:00:00 | | 503 | 3 | app_open | 2025-08-31 12:00:00 | | 504 | 4 | app_open | 2025-08-31 13:00:00 | | 505 | 4 | like | 2025-08-30 14:00:00 | +----------+---------+------------+---------------------+ calls +---------+-----------+----------+---------------------+------------+ | call_id | caller_id | callee_id| started_at | media_type | +---------+-----------+----------+---------------------+------------+ | 701 | 1 | 2 | 2025-08-31 15:00:00 | video | | 702 | 2 | 1 | 2025-08-31 16:00:00 | audio | | 703 | 4 | 3 | 2025-08-31 17:00:00 | video | | 704 | 2 | 5 | 2025-09-01 09:00:00 | video | +---------+-----------+----------+---------------------+------------+ Write a single SQL query; avoid hard-coding user_ids.

Quick Answer: This question evaluates the ability to perform data manipulation and metric computation across relational tables using SQL or Python, specifically measuring daily active users and video-call participation for a geographic subset.

Loading coding console...