Meta·Data Scientist·Technical Phone Screen·Senior

Senior

Jun 2026

Summary

SQL-heavy technical screen for a DS role at Meta. Two problems back to back, both ad-related, which makes sense given the business. The second one had a subtle FX join that I almost botched.

Questions Asked (2)

Given tables for ads, impressions, and conversions, write SQL to compare click-through rate during peak hours (6pm to 10pm UTC) versus all other hours, for active ads over the last 30 complete days. Return one row per time bucket with impression count, click count, and CTR.

Product Analytics & MetricsData Modeling

Author's notes

The peak/non-peak bucketing is the core of this one.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and definitions (e.g., how clicks are recorded, what 'active' means). Then write a SQL query that joins ads, impressions, and conversions (or clicks) appropriately, filters for active ads and the last 30 complete days, and buckets timestamps into peak (18:00-22:00 UTC) and off-peak. Finally, aggregate counts and compute CTR per bucket.

Pro tip: Explicitly state your assumptions about the data model (e.g., whether conversions table contains click events or only post-click conversions) and handle edge cases like ads with zero impressions. This shows you think about data quality and business logic, not just syntax.

1. Clarify requirements and schema

Ask about table structures, definitions of 'active ads', 'click', and 'conversion', and whether timestamps are in UTC. Confirm that 'last 30 complete days' means full days excluding today.

2. Filter and bucket data

Filter ads to active status and restrict impressions to the last 30 complete days. Create a time bucket column using CASE WHEN on the hour of the impression timestamp (UTC) to label peak vs. off-peak.

3. Join and aggregate

Join impressions with clicks (or conversions if clicks are derived) on ad_id and possibly impression_id. Aggregate counts of impressions and clicks per time bucket.

4. Compute CTR and format output

Calculate CTR as clicks divided by impressions (as a decimal or percentage). Ensure the final result has one row per time bucket with impression count, click count, and CTR.

Key Points to Mention

Definition of CTR: clicks / impressions, and whether to use NULLIF to avoid division by zero.
Time bucketing: using CASE WHEN with EXTRACT(HOUR FROM timestamp) to classify peak vs. off-peak.
Active ads filter: likely a status column or date range in the ads table.
Last 30 complete days: filtering impressions where date >= current_date - 30 AND date < current_date.
Join logic: how impressions relate to clicks (e.g., clicks table may have impression_id or ad_id and timestamp).
Handling ads with zero impressions: ensure they are excluded or included appropriately based on the question.

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

Given ad revenue data by country and currency, plus an FX rates table, write SQL to calculate US-only revenue and global revenue in USD for each day in January 2024.

Data ModelingProduct Analytics & Metrics

Author's notes

Almost forgot to handle the case where country_code is 'US' and you still need to join fx_rates even though the rate is 1.0.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, clarify the schema and assumptions (e.g., revenue is in local currency, FX rates are daily and may need to handle missing weekends/holidays). Then, join the revenue table with the FX rates table on date and currency, convert to USD, and aggregate to get US-only and global revenue per day. Use conditional aggregation or separate CTEs for US and global, and ensure all days in January 2024 are included even if no revenue exists.

Pro tip: Mention that you would handle missing FX rates by using the most recent available rate (e.g., last non-null value) and that you would validate the results by checking that global revenue equals the sum of all country revenues. Also, note that US-only revenue should be filtered by country = 'US' before conversion.

1. Clarify schema and assumptions

Ask about table structures: revenue table (date, country, currency, revenue_amount) and FX table (date, currency, rate_to_usd). Confirm that revenue is in local currency and FX rates are daily. Assume US revenue is already in USD or has a rate of 1.

2. Handle date and currency joins

Join revenue with FX rates on date and currency. For missing rates (e.g., weekends), use a forward-fill technique (e.g., last_value with ignore nulls or a subquery to get the most recent rate).

3. Convert to USD and aggregate

Multiply revenue_amount by rate_to_usd to get USD revenue. Then, aggregate by date: for US-only, filter country = 'US' and sum; for global, sum all countries. Use conditional aggregation or separate CTEs.

4. Ensure all dates in January 2024 are included

Generate a date series for January 2024 and left join the aggregated results to ensure no missing days. Fill missing revenue with 0.

5. Validate and present results

Check that global revenue >= US revenue for each day. Also, verify that the sum of global revenue matches the sum of all converted revenues. Present the final output with columns: date, us_revenue_usd, global_revenue_usd.

Key Points to Mention

Handling missing FX rates (e.g., weekends/holidays) by using the most recent available rate.
Ensuring US revenue is correctly identified (country = 'US') and converted if needed.
Using a date spine to include all days in January 2024, even with no revenue.
Conditional aggregation or separate CTEs to compute US-only and global revenue in one query.
Validating results by comparing global revenue to the sum of all country revenues.
Considering performance implications if tables are large (e.g., partitioning by date).

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