← Meta Interview Insights

Meta·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
Aug 2023Remote

Summary

Meta DS technical screen, basically one meaty SQL question around a new product feature rollout. Pretty focused session, no fluff.

Questions Asked (1)

Q1

Given a call logs table with fields for call start time, a flag indicating whether it was a group call, and the user who initiated it, write SQL to return per-day stats: total calls, total group calls, unique users who started a group call, and the percentage of calls that were group calls. Then filter to only days where that percentage falls below 10%.

Product Analytics & MetricsA/B Testing & Experimentation
Author's notes

The aggregation part was fine, DATE() on the timestamp, COUNT(*), SUM on the flag.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the table schema and definitions (e.g., what counts as a group call, how to handle NULLs). Then write a single SQL query that aggregates per day using conditional aggregation for the metrics, and finally apply a HAVING clause to filter days where the group call percentage is below 10%.

Pro tip: Mention that you'd validate the percentage calculation by checking edge cases (e.g., days with zero calls) and consider using a subquery or CTE for readability and to avoid repeating the percentage expression in the HAVING clause.

1. Clarify requirements and schema

Ask about the exact table name, column names, and definitions (e.g., what does the group call flag look like? Is it a boolean or a string?). Confirm that 'unique users who started a group call' means distinct initiators for group calls only.

2. Aggregate per day

Use DATE(start_time) to group by day. Compute total calls with COUNT(*), total group calls with SUM(CASE WHEN is_group THEN 1 ELSE 0 END), and unique group call initiators with COUNT(DISTINCT CASE WHEN is_group THEN initiator_id END).

3. Calculate percentage

Compute the percentage as (total group calls / total calls) * 100. Be mindful of integer division; cast to float or multiply by 100.0.

4. Filter days with percentage < 10%

Apply a HAVING clause to filter days where the percentage is less than 10. Alternatively, use a subquery or CTE to compute the percentage and then filter in an outer query.

5. Format and present the query

Write the final SQL query clearly, using aliases and proper formatting. Explain each part briefly to demonstrate understanding.

Key Points to Mention

  • Use of conditional aggregation (CASE WHEN) to compute multiple metrics in one pass.
  • Handling of NULLs in the group call flag or initiator ID.
  • Avoiding integer division by casting to float or multiplying by 1.0.
  • Using HAVING vs. subquery for filtering on aggregated percentage.
  • Considering time zone or date truncation if start_time includes time component.
  • Ensuring 'unique users' counts distinct initiators only for group calls.

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