← Meta Interview Insights

Meta·Data Scientist·Technical Phone Screen·Senior

Senior
May 2026

Summary

Meta Data Scientist interview with a SQL-heavy technical screen. One question, three tables, and a deadline that made me second-guess every JOIN I've ever written.

Questions Asked (1)

Q1

Given three related tables for ads, impressions, and conversions, write a SQL query that returns the top 5 ads by conversion count over the past 30 days. Output should include ad_id, advertiser_id, and total_conversions, sorted descending, with ads that have zero conversions excluded.

Product Analytics & MetricsData Modeling
Author's notes

The LEFT JOIN direction tripped me up for a second.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the table schemas and relationships, then write a query that joins ads to conversions (filtered to the last 30 days) and aggregates conversion counts per ad. Use a LEFT JOIN to include ads with zero conversions, filter them out with HAVING or WHERE, and finally order by total_conversions descending and limit to 5.

Pro tip: Mention that you would verify the date range logic (e.g., using CURRENT_DATE - INTERVAL '30 days') and consider whether conversions should be counted based on conversion timestamp or impression timestamp, as this can significantly affect results.

1. Clarify schema and relationships

Ask or state assumptions about the columns in each table (e.g., ad_id, advertiser_id, conversion_date) and how they join (e.g., ads.ad_id = conversions.ad_id).

2. Filter conversions to last 30 days

Apply a date filter on the conversions table to only include events from the past 30 days, using an appropriate date function.

3. Aggregate conversions per ad

Group by ad_id and advertiser_id (or join to ads to get advertiser_id) and count the number of conversions.

4. Exclude zero conversions and sort

Use HAVING COUNT(*) > 0 to exclude ads with no conversions, then ORDER BY total_conversions DESC.

5. Limit to top 5

Apply LIMIT 5 to return only the top 5 ads by conversion count.

Key Points to Mention

  • Use of LEFT JOIN to include all ads, then filter out zero conversions with HAVING or WHERE.
  • Date filtering: ensure the 30-day window is correctly applied (e.g., conversion_date >= CURRENT_DATE - INTERVAL '30 days').
  • Aggregation: COUNT(*) or COUNT(conversion_id) to get total_conversions.
  • Grouping by ad_id and advertiser_id to ensure correct output columns.
  • Sorting and limiting: ORDER BY total_conversions DESC LIMIT 5.
  • Handling potential duplicates or multiple conversions per user (if relevant, mention DISTINCT or counting distinct conversion IDs).

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