← Meta Interview Insights

Meta·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
May 2026

Summary

Meta DS interview with a SQL question focused on cross-country call analytics. Pretty straightforward technically but the double-join setup tripped me up for a moment.

Questions Asked (1)

Q1

Given a Users table and a Calls table, write a SQL query that returns, for each caller country and receiver country pair, the total number of calls and the average call duration over the last 30 days.

Product Analytics & MetricsData Modeling
Author's notes

The part that slowed me down was joining Users twice, once for the caller and once for the receiver.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and the definition of 'last 30 days' (e.g., relative to current date or a fixed date). Then write a query that joins the Calls table to the Users table twice (once for caller, once for receiver) to get country information, filters calls to the last 30 days, groups by caller country and receiver country, and computes COUNT(*) and AVG(duration).

Pro tip: Mention that you would check for data quality issues like null durations or missing country info, and consider whether to use INNER JOIN or LEFT JOIN based on whether you want to include calls with unknown users. Also, discuss the trade-off between using a subquery to filter recent calls first versus filtering after the join for performance.

1. Clarify requirements and schema

Ask about the table structures, column names, and how 'last 30 days' is defined (e.g., based on call start time). Confirm whether to include calls with missing user info.

2. Filter calls to last 30 days

Use a WHERE clause on the Calls table to restrict to calls within the last 30 days, e.g., call_date >= CURRENT_DATE - INTERVAL '30 days'.

3. Join to Users for caller and receiver countries

Join Calls to Users twice: once on caller_id = Users.user_id to get caller_country, and once on receiver_id = Users.user_id to get receiver_country.

4. Group and aggregate

Group by caller_country and receiver_country, then compute COUNT(*) as total_calls and AVG(duration) as avg_duration.

5. Consider edge cases and performance

Discuss handling of NULLs, potential duplicate calls, and indexing strategies to optimize the query.

Key Points to Mention

  • Use of aliases for the Users table to distinguish caller and receiver joins.
  • Filtering by date range using appropriate date functions (e.g., DATE_SUB, INTERVAL).
  • Aggregation functions: COUNT(*) for total calls, AVG(duration) for average duration.
  • Grouping by both caller_country and receiver_country.
  • Handling of NULL values in duration or country fields (e.g., COALESCE or filtering).
  • Performance considerations: filtering before joining, indexing on date and user IDs.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.