Katkat
Back

Calculate French User Engagement and U.S. Call Duration

MetaData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

This question evaluates proficiency in SQL-based data manipulation and analytics, testing skills such as joining event and profile tables, aggregating metrics, and computing user-level engagement proportions and average durations.

call_logs

+---------+-----------+------------+---------+----------+ | caller | recipient | ds | call_id | duration | +---------+-----------+------------+---------+----------+ | 123 | 456 | 2019-01-01 | 4325 | 864.4 | | 032 | 789 | 2019-01-01 | 9395 | 263.7 | | 456 | 032 | 2019-01-01 | 0879 | 22.0 | +---------+-----------+------------+---------+----------+

​

user_profile

+---------+-----------+---------+------------+----------+------------+ | user_id | age_bucket| country | primary_os | dau_flag | ds | +---------+-----------+---------+------------+----------+------------+ | 123 | 25-34 | US | iOS | 1 | 2019-01-01 | | 456 | 35-44 | FR | Android | 1 | 2019-01-01 | | 789 | 18-24 | FR | iOS | 0 | 2019-01-01 | +---------+-----------+---------+------------+----------+------------+

Scenario

A video-calling app wants to monitor user engagement. Analysts must compute (

  1. what proportion of French users placed at least one video call yesterday, and (
  2. today’s average total call duration per U.S. daily-active user.
Question

Write an SQL query to return the percentage of users whose country = 'France' that made or received at least one call yesterday.

Write an SQL query to return total call-time divided by count of U.S. DAU for today.

Hints

Join call_logs to user_profile twice (caller, recipient). Filter by ds with DATE_SUB/CURRENT_DATE. Use DISTINCT users to avoid double-counting, then aggregate.