Meta·Data Scientist·Technical Phone Screen·Senior

Senior

Jun 2026

Summary

Meta DS interview with a SQL-heavy question on ad revenue analytics. Pretty standard for a monetization-adjacent role but the multi-table join with conditional billing logic kept it interesting.

Questions Asked (1)

Given ad impression, click, and billing tables for a shop-ads system, write a SQL query that computes revenue by country and region over the last 30 days, including total revenue, impression count, click count, CTR, and revenue per thousand impressions. Filter to geographies with at least 100k impressions and sort by revenue descending.

Product Analytics & MetricsData Modeling

Author's notes

The tricky part was the billing model.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the table schemas and join keys, then build a CTE that aggregates impressions, clicks, and revenue by country and region over the last 30 days. Join the aggregated metrics, compute CTR and RPM, apply the 100k impression filter, and sort by revenue descending.

Pro tip: Mention that you would validate the join granularity (e.g., impression_id) to avoid fan-out and double-counting, and consider using a date filter on the impression table to leverage partitioning for performance.

1. Clarify schema and join keys

Ask about the columns in each table, especially the join keys (e.g., impression_id) and how billing relates to impressions/clicks. Confirm the date column and time zone for the 30-day window.

2. Aggregate metrics per geography

Write separate CTEs to compute total impressions, clicks, and revenue per country and region for the last 30 days. Ensure you filter by date in each CTE to reduce data volume.

3. Join and compute derived metrics

Join the aggregated CTEs on country and region, then calculate CTR (clicks/impressions) and RPM (revenue/impressions*1000). Use LEFT JOINs to keep all geographies with impressions.

4. Apply filters and sort

Filter to geographies with at least 100,000 impressions and order the final result by total revenue descending.

5. Validate and optimize

Check for duplicate rows or fan-out from joins, and suggest indexing or partitioning strategies for performance on large datasets.

Key Points to Mention

Use of CTEs for readability and modular aggregation
Correct join keys to avoid double-counting (e.g., impression_id)
Date filtering with a 30-day window (e.g., DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY))
Handling NULLs or zero impressions in CTR and RPM calculations
Applying the 100k impression threshold after aggregation
Sorting by revenue descending and limiting output if needed

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