← Meta Interview Insights

Meta·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
Jul 2026

Summary

Meta data scientist SQL round, one question about conditional aggregation for a shop-level analytics metric. Pretty focused, no fluff.

Questions Asked (1)

Q1

Given a table of shop events with impression and view event types, write a SQL query that returns daily impressions, views, and a visibility ratio (views divided by impressions) for each shop, ordered by date and shop ID.

Product Analytics & MetricsData Modeling
Author's notes

The core of the question is conditional aggregation, which I knew, but I fumbled a bit deciding between FILTER and CASE WHEN syntax.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the table schema and event types, then write a query that aggregates daily counts per shop using conditional aggregation. Compute the visibility ratio as views divided by impressions, handling division by zero, and order the results by date and shop ID.

Pro tip: Mention that you'd validate the ratio by checking for anomalies like ratios >1, which could indicate data issues or multiple views per impression. Also, consider using NULLIF or CASE to avoid division by zero errors.

1. Understand the data

Identify the table structure, event types (impression, view), and relevant columns (shop_id, event_type, event_date). Confirm the granularity of the data.

2. Aggregate daily counts

Use conditional aggregation (e.g., SUM(CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END)) to count impressions and views per shop per day.

3. Compute visibility ratio

Calculate views divided by impressions, using NULLIF or CASE to handle division by zero. Ensure the ratio is computed after aggregation.

4. Order and format results

Order by date and shop_id ascending. Select the date, shop_id, impressions, views, and visibility ratio as columns.

5. Validate and optimize

Check for edge cases (e.g., zero impressions, missing dates) and consider indexing or partitioning for performance if the table is large.

Key Points to Mention

  • Use conditional aggregation to pivot event types into columns.
  • Handle division by zero with NULLIF or CASE to avoid errors.
  • Ensure the ratio is computed after aggregation, not before.
  • Order by date and shop_id as specified.
  • Consider data quality checks, such as ratios exceeding 1.
  • Mention potential performance optimizations like indexing on date and shop_id.

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