Quick Overview

This question evaluates proficiency in SQL-based data manipulation and analytics—specifically aggregation, grouping, time-window filtering, and percentage calculations—by asking for the top call initiators over the last seven days and the proportion of active users in France on video calls yesterday, and is categorized under Data Manipulation (SQL/Python) for a Data Scientist role with a focus on practical application. It is commonly asked in technical interviews to assess a data scientist's ability to extract actionable engagement metrics from event logs, reason about temporal windows and population denominators, and produce accurate, explainable reporting from production-style datasets.

Analyze Top Call Initiators and Active French Video Callers

Company: Meta

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

calls +---------+-----------+-------------+---------------------+---------+-----------+ | call_id | caller_id | receiver_id | call_start_time | country | call_type | +---------+-----------+-------------+---------------------+---------+-----------+ | 1 | 101 | 202 | 2023-10-10 09:05:00 | France | video | | 2 | 103 | 204 | 2023-10-10 10:15:00 | India | voice | | 3 | 101 | 105 | 2023-10-09 22:30:00 | France | video | | 4 | 106 | 107 | 2023-10-03 05:45:00 | Brazil | video | | 5 | 108 | 109 | 2023-10-10 13:20:00 | France | voice | +---------+-----------+-------------+---------------------+---------+-----------+ ##### Scenario A social media platform wants SQL queries to analyze its call feature usage. ##### Question Write a SQL query to return the top 10 users who initiated the highest number of calls in the last 7 days. Write a SQL query to calculate the percentage of active users in France who were on a video call yesterday. ##### Hints Filter by date, group by caller, count calls, use sub-query or CTE for total active users and video callers, and calculate the percentage.

Overview: This question evaluates proficiency in SQL-based data manipulation and analytics—specifically aggregation, grouping, time-window filtering, and percentage calculations—by asking for the top call initiators over the last seven days and the proportion of active users in France on video calls yesterday, and is categorized under Data Manipulation (SQL/Python) for a Data Scientist role with a focus on practical application. It is commonly asked in technical interviews to assess a data scientist's ability to extract actionable engagement metrics from event logs, reason about temporal windows and population denominators, and produce accurate, explainable reporting from production-style datasets.

Top 10 Call Initiators (2025-05-26 to 2025-06-01)

Return the top 10 users (by caller_id) who initiated the highest number of calls between 2025-05-26 and 2025-06-01 (both dates inclusive).

Tables

calls(call_id INTEGER, caller_id INTEGER, receiver_id INTEGER, call_start_time TIMESTAMP, country VARCHAR(100), call_type VARCHAR(20))

Hints

  1. Filter calls using a fixed date window: call_start_time >= DATE '2025-05-26' AND call_start_time < DATE '2025-06-02'.
  2. GROUP BY caller_id, ORDER BY call_count DESC, and LIMIT 10.

France Video Active Users Percentage on 2025-05-31

Calculate the percentage of active users in France who were on a video call on 2025-05-31. Return a single row with columns: date, active_users, video_users, and percentage_video.

Tables

calls(call_id INTEGER, caller_id INTEGER, receiver_id INTEGER, call_start_time TIMESTAMP, country VARCHAR(100), call_type VARCHAR(20))

Hints

  1. Include both caller_id and receiver_id by UNION ALL to define the set of active users on 2025-05-31.
  2. Filter by country = 'France' and the fixed window call_start_time >= DATE '2025-05-31' AND call_start_time < DATE '2025-06-01'.

Loading coding console...