← Meta Interview Insights

Meta·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
Jul 2023Remote

Summary

Meta DS interview with a SQL question focused on video call analytics. Pretty standard technical screen but the product framing made it feel a bit more applied than a typical coding exercise.

Questions Asked (1)

Q1

Write a SQL query to return the top 10 users by average call duration in minutes, considering only calls that started within the last 7 days.

Product Analytics & MetricsData Modeling
Author's notes

The core mechanics weren't too bad.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and defining 'average call duration in minutes' as the mean of (end_time - start_time) in minutes per user. Then filter calls to those started in the last 7 days, group by user, compute the average, order descending, and limit to 10.

Pro tip: Mention that you would confirm whether 'last 7 days' means a rolling 7-day window from now or the last 7 complete days, and whether to include calls with null end times or zero duration, as these choices can significantly affect the results.

1. Clarify requirements and schema

Ask about the table structure, column names, and definitions of 'call duration' and 'last 7 days'. Confirm whether to include only completed calls and how to handle nulls.

2. Filter calls by time window

Use a WHERE clause to select calls where start_time is within the last 7 days, e.g., start_time >= CURRENT_DATE - INTERVAL '7 days' or equivalent.

3. Compute average duration per user

Calculate the duration in minutes for each call, then group by user_id and compute the average duration using AVG().

4. Rank and limit results

Order the results by average duration descending and limit to the top 10 users.

5. Validate and discuss edge cases

Mention potential edge cases like ties, users with very few calls, and timezone considerations, and how you would handle them.

Key Points to Mention

  • Definition of call duration: end_time - start_time, converted to minutes
  • Time window: using start_time >= CURRENT_DATE - INTERVAL '7 days' (or equivalent)
  • Handling of NULL end_time or ongoing calls (e.g., exclude or use COALESCE)
  • Grouping by user_id and using AVG() for average duration
  • Ordering by average duration DESC and LIMIT 10
  • Potential need for a minimum call count threshold to avoid skewed averages

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