← Meta Interview Insights

Meta·Data Scientist·Technical Phone Screen·Senior

Senior
Jul 2023

Summary

Meta Data Scientist interview with a meaty SQL and stats question built around three ad performance tables. The whole thing was one big multi-part problem and it went deeper than I expected.

Questions Asked (3)

Q1

Given three tables (ads, ads_impressions, ads_conversions), write a query to compute the overall click-through rate per ad for the last 7 days, then return the top 3 advertisers ranked by conversion rate in that same period.

Product Analytics & MetricsData Modeling
Author's notes

The joins themselves weren't the hard part.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the table schemas and metric definitions (e.g., CTR = clicks/impressions, conversion rate = conversions/clicks). Then write a query that filters the last 7 days, aggregates impressions, clicks, and conversions per ad, computes CTR and conversion rate, and finally ranks advertisers by conversion rate to return the top 3.

Pro tip: Always confirm whether 'last 7 days' means the last 7 complete days or includes today, and whether conversion rate should be based on clicks or impressions—these details can change the query and the results significantly.

1. Clarify schemas and definitions

Ask about the columns in each table (e.g., ad_id, advertiser_id, date, impressions, clicks, conversions) and confirm the exact formulas for CTR and conversion rate.

2. Filter and aggregate data

Use a WHERE clause to restrict to the last 7 days, then aggregate impressions, clicks, and conversions per ad_id (and advertiser_id if needed).

3. Compute metrics per ad

Calculate CTR as clicks divided by impressions (or as given) for each ad, ensuring to handle division by zero.

4. Aggregate to advertiser level

Sum the metrics across ads for each advertiser, then compute the overall conversion rate for the advertiser.

5. Rank and return top 3

Order advertisers by conversion rate descending, limit to 3, and return the advertiser identifier along with the conversion rate.

Key Points to Mention

  • Define CTR and conversion rate precisely (e.g., CTR = clicks/impressions, conversion rate = conversions/clicks).
  • Handle division by zero using NULLIF or CASE statements to avoid errors.
  • Use date filtering with a dynamic function like CURRENT_DATE - INTERVAL '7 days' rather than hardcoding dates.
  • Consider whether to aggregate at the ad level first or directly at the advertiser level, and ensure the correct join keys.
  • Mention the importance of indexing or partitioning by date for performance on large datasets.
  • Discuss potential data quality issues, such as missing impressions or conversions, and how to handle them (e.g., using LEFT JOINs).

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

Q2

For each hour of the day, compare CTR during peak hours (18:00 to 22:00) versus non-peak hours, and output the p-value for a difference-in-proportions test.

A/B Testing & ExperimentationProduct Analytics & Metrics
Author's notes

This is where I kind of froze.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, clarify the data structure and define peak vs. non-peak hours. Then, for each hour, compute the CTR and perform a two-proportion z-test, outputting the p-value. Consider multiple testing correction and practical significance.

Pro tip: When comparing many hours, adjust for multiple comparisons (e.g., Bonferroni or FDR) to avoid false positives. Also, check if the difference is practically significant, not just statistically significant.

1. Clarify the data and definitions

Confirm the dataset has click and impression counts per hour. Define peak hours as 18:00-22:00 and non-peak as all other hours.

2. Aggregate data per hour

For each hour, sum clicks and impressions across all days to get overall CTR for that hour. Ensure data is clean and no missing values.

3. Perform difference-in-proportions test

For each hour, conduct a two-proportion z-test comparing CTR during peak vs. non-peak. Calculate the p-value for each hour.

4. Adjust for multiple comparisons

Apply a correction like Bonferroni or Benjamini-Hochberg to control the false discovery rate across the 24 tests.

5. Interpret and present results

Report the p-values, noting which hours show significant differences. Discuss effect sizes and practical implications.

Key Points to Mention

  • Two-proportion z-test assumptions: independent observations, large sample size
  • Multiple testing correction (e.g., Bonferroni, FDR)
  • Effect size (difference in CTR) and confidence intervals
  • Potential confounding factors (e.g., day of week, seasonality)
  • Data aggregation level (hourly vs. daily)
  • Practical significance vs. statistical significance

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

Q3

What additional data or tests would you suggest to confirm the hypothesis that ads shown during peak hours perform better than those shown at non-peak hours?

A/B Testing & ExperimentationRoot Cause Analysis
Author's notes

My answer was decent.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the hypothesis and the current evidence, then propose additional data sources and tests that address confounding factors and strengthen causal inference. Structure your answer around validating the hypothesis through experimental design, observational data, and robustness checks.

Pro tip: Emphasize the importance of defining 'peak hours' precisely and considering user-level randomization to avoid confounding; also mention the need to check for novelty effects and ensure sufficient power.

1. Clarify the hypothesis and current evidence

Ask what data was used to form the hypothesis and how 'peak hours' and 'perform better' are defined. This ensures you understand the baseline and can identify gaps.

2. Propose additional data sources

Suggest gathering data on user engagement metrics (CTR, conversion), ad characteristics, user demographics, and contextual factors like device, location, and time zone to control for confounders.

3. Design a controlled experiment

Recommend an A/B test where users are randomly assigned to see ads during peak or non-peak hours, ensuring that other variables are held constant. Discuss randomization unit and potential interference.

4. Analyze observational data with causal methods

If experimentation is not feasible, suggest using techniques like propensity score matching, difference-in-differences, or instrumental variables to estimate causal effects from observational data.

5. Validate and stress-test findings

Propose robustness checks such as subgroup analyses, sensitivity analyses, and testing for novelty effects. Also suggest replicating the study across different time periods or markets.

Key Points to Mention

  • Define peak hours clearly (e.g., based on traffic, user activity, or revenue) and ensure consistent measurement.
  • Control for confounding variables such as user demographics, ad creative, placement, and day of week.
  • Use randomization at the user level to avoid contamination and ensure valid causal inference.
  • Consider external validity: test across different regions, seasons, and platforms to generalize findings.
  • Check for statistical power and practical significance, not just p-values.
  • Address potential novelty effects and long-term impact by running the experiment for sufficient duration.

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