← Meta Interview Insights

Meta·Data Scientist·Technical Phone Screen·Senior

Senior
Jun 2023Remote

Summary

Meta DS interview with a pretty gnarly SQL plus streaming Python question built around a newsfeed impression log. The scenario was realistic enough that it felt less like a leetcode grind and more like something you'd actually debug at work, which I appreciated but also found stressful.

Questions Asked (2)

Q1

Given a table of post impression events with watch duration and screen coverage columns, write a SQL query that returns every post whose total watch duration across all views exceeds some threshold X and whose maximum screen coverage across those views exceeds threshold Y.

Product Analytics & MetricsData Modeling
Author's notes

The GROUP BY part was fine, I got that out fast.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Use a GROUP BY on post_id to aggregate watch duration and screen coverage across all views. Apply a HAVING clause to filter groups where SUM(watch_duration) > X and MAX(screen_coverage) > Y. This single-pass aggregation is efficient and directly answers the question.

Pro tip: Mention that you would verify the grain of the table (one row per impression) and consider whether watch duration should be summed only for valid views (e.g., excluding bot traffic or very short views). Also, discuss indexing on post_id for performance if the table is large.

1. Understand the table schema and grain

Identify the columns: post_id, watch_duration, screen_coverage, and possibly view_id or user_id. Confirm that each row represents a single impression/view event.

2. Aggregate metrics per post

Group by post_id and compute SUM(watch_duration) as total_watch_duration and MAX(screen_coverage) as max_screen_coverage.

3. Apply threshold filters

Use a HAVING clause to filter groups where total_watch_duration > X and max_screen_coverage > Y.

4. Select final output

Return post_id (and optionally the aggregated metrics) for posts that meet both conditions.

5. Consider edge cases and performance

Discuss handling NULLs, zero-duration views, and indexing strategies to optimize the query for large datasets.

Key Points to Mention

  • Use of GROUP BY post_id to aggregate across all views
  • SUM(watch_duration) for total watch duration and MAX(screen_coverage) for maximum coverage
  • HAVING clause for filtering on aggregated values (since WHERE cannot be used with aggregates)
  • Thresholds X and Y are parameters; ensure they are correctly applied with strict inequality if specified
  • Potential need to filter out invalid views (e.g., watch_duration = 0 or screen_coverage = 0) depending on business logic
  • Performance considerations: indexing on post_id, partitioning if table is huge

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

Q2

Using the same event data consumed as a stream, write Python code that groups events into sessions (where a session ends if no new event arrives within 30 seconds), then emits for each completed session the posts that meet the same effective-read criteria along with their total watch time and max screen coverage.

System DesignAlgorithms & Data StructuresTechnical Trade-offs
Author's notes

This is where things got messy for me.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Clarify the streaming framework and event schema, then design a stateful sessionization solution using a timer or watermark to detect 30-second inactivity. For each session, aggregate per-post watch time and max screen coverage, filter posts by effective-read criteria, and emit results when the session closes.

Pro tip: Emphasize that sessionization requires event-time processing with watermarks to handle out-of-order events and late data, and discuss how to scale state with keyed state and timers per user.

1. Clarify requirements and assumptions

Confirm the streaming framework (e.g., Flink, Spark Structured Streaming), event schema (user_id, post_id, timestamp, watch_time, screen_coverage), and the exact effective-read criteria (e.g., watch_time >= 10s and screen_coverage >= 50%).

2. Design sessionization logic

Key events by user_id, maintain session state with a timer set to 30 seconds after the last event. When the timer fires, close the session and emit its aggregated results.

3. Aggregate per-post metrics

Within each session, accumulate total watch time and max screen coverage per post. Use a dictionary or state object keyed by post_id.

4. Filter and emit completed sessions

When the session closes, filter posts that meet the effective-read criteria, and emit a record containing user_id, session_id, post_id, total_watch_time, and max_screen_coverage.

5. Handle late data and scaling

Use watermarks to allow for out-of-order events up to a bound, and discuss state management (e.g., RocksDB) and parallelism for scalability.

Key Points to Mention

  • Event-time processing with watermarks to handle out-of-order and late events
  • Stateful sessionization using keyed state and timers (e.g., Flink's KeyedProcessFunction)
  • Efficient per-post aggregation with incremental updates
  • Effective-read criteria definition and filtering logic
  • Scalability considerations: state size, checkpointing, and parallelism
  • Output semantics: exactly-once vs at-least-once and idempotent sinks

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