Meta·Data Scientist·Technical Phone Screen·Senior

SeniorPrefer not to say

Jul 2026

Summary

Meta DS interview, got a pretty involved SQL question for an ads analytics context. One question, lots of moving parts, felt like a take-home but done live.

Questions Asked (1)

Given an ad impressions table and a conversions table, write a SQL query that attributes each conversion to the most recent eligible impression within 7 days (same user and ad, impression before conversion). For January 2024 impressions, return per campaign and per day: impression count, distinct users, attributed conversions, conversion rate, total spend, total attributed revenue, and average hours from impression to conversion.

Product Analytics & MetricsData Modeling

Author's notes

This one took me a minute to even parse.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, clarify the schema and attribution rules (e.g., same user and ad, impression before conversion, within 7 days, most recent eligible impression). Then, outline a SQL strategy using a window function to rank impressions per conversion, filter to the top-ranked impression, and aggregate metrics per campaign and day for January 2024 impressions. Finally, discuss edge cases and performance considerations.

Pro tip: Explicitly state that you would validate the attribution logic with a small sample or by checking for conversions with no eligible impression, and mention that you'd use a LEFT JOIN to retain all impressions even if they have no conversions.

1. Clarify requirements and schema

Ask about table structures, column names, and definitions (e.g., what constitutes an eligible impression, how to handle multiple conversions per impression). Confirm the time window and attribution rule.

2. Design attribution logic

Use a window function (e.g., ROW_NUMBER() OVER (PARTITION BY conversion_id ORDER BY impression_time DESC)) to select the most recent eligible impression per conversion, ensuring impression_time < conversion_time and within 7 days.

3. Aggregate metrics per campaign and day

Join the attributed conversions back to the impressions table, filter to January 2024 impressions, and group by campaign and impression date. Compute impression count, distinct users, attributed conversions, conversion rate, total spend, total attributed revenue, and average hours from impression to conversion.

4. Handle edge cases and validate

Consider conversions without eligible impressions (use LEFT JOIN to keep them as NULL), multiple conversions per impression (each conversion attributed independently), and timezone consistency. Validate results with sanity checks.

5. Optimize and discuss performance

Mention indexing on user_id, ad_id, and timestamps, and consider partitioning by date. Discuss trade-offs of using window functions vs. self-joins for large datasets.

Key Points to Mention

Use of window functions (e.g., ROW_NUMBER) to rank impressions per conversion and select the most recent eligible one.
Ensuring the impression occurs before the conversion and within 7 days (e.g., DATEDIFF(day, impression_time, conversion_time) <= 7).
Aggregation metrics: COUNT(DISTINCT user_id) for distinct users, SUM for spend and revenue, and AVG(DATEDIFF(hour, impression_time, conversion_time)) for average hours.
Conversion rate calculation: attributed conversions divided by impression count (or distinct users, depending on definition).
Handling conversions with no eligible impression: use LEFT JOIN and COALESCE to include them as zero or NULL.
Performance considerations: indexing, partitioning, and avoiding cross joins.

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