Katkat
Back

Analyze Top Call Initiators and Active French Video Callers

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

Last updated

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.

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.