← Meta Interview Insights

Meta·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
Oct 2023Remote

Summary

Meta DS interview with two SQL questions back to back, both pulling from the same info stream schema. Nothing crazy on the surface but the filtering logic tripped me up more than I expected.

Questions Asked (2)

Q1

Write a SQL query to find the number of distinct posts that had at least one view longer than 60 seconds from 'Unconnected' viewers within the last 7 days.

Product Analytics & MetricsData Modeling
Author's notes

The filtering part is straightforward but I kept second-guessing the date logic.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and definitions (e.g., tables for posts, views, and viewers). Then build the query by filtering views to the last 7 days, joining with viewer data to identify 'Unconnected' viewers, applying the >60 second condition, and finally counting distinct post IDs.

Pro tip: Mention that you would verify the definition of 'Unconnected' with stakeholders and consider using a subquery or CTE to filter views before joining to avoid performance issues on large datasets.

1. Clarify requirements and schema

Confirm the definitions of 'Unconnected' viewers, 'view longer than 60 seconds', and the time window. Identify relevant tables and columns (e.g., posts, views, viewers).

2. Filter views by time and duration

Select views from the last 7 days where the view duration exceeds 60 seconds. Use a WHERE clause on the view timestamp and duration.

3. Join with viewer data

Join the filtered views with the viewers table to filter for viewers with connection status 'Unconnected'.

4. Count distinct posts

Use COUNT(DISTINCT post_id) to get the number of unique posts that meet the criteria.

5. Optimize and validate

Consider using CTEs or subqueries for readability and performance. Validate results with edge cases (e.g., no views, multiple views per post).

Key Points to Mention

  • Use of COUNT(DISTINCT post_id) to ensure unique posts are counted.
  • Filtering views by timestamp within the last 7 days (e.g., using CURRENT_DATE - INTERVAL '7 days').
  • Condition on view duration > 60 seconds.
  • Joining with viewer table to filter for 'Unconnected' status.
  • Potential need to handle NULLs or missing data in joins.
  • Performance considerations: indexing, partitioning, or using CTEs to reduce data scanned.

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

Q2

Write a SQL query to compute the average number of reactions per post, broken out separately for 'Friend' viewers and 'Unconnected' viewers, over the last 7 days.

Product Analytics & MetricsData Modeling
Author's notes

This one requires joining the reactions table back to the views table to get the relationship column, which I did not think about right away.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Clarify the schema and definitions (e.g., what counts as a reaction, how viewer type is determined, and the time window). Then write a query that joins posts, reactions, and viewer information, filters to the last 7 days, groups by viewer type, and computes the average reactions per post. Use a subquery or CTE to first aggregate reactions per post, then average across posts for each viewer type.

Pro tip: Always confirm whether 'average reactions per post' means averaging over all posts (including those with zero reactions) or only posts that have at least one reaction. This distinction significantly impacts the result and shows you understand product nuances.

1. Clarify requirements and schema

Ask about the table structures, how to identify 'Friend' vs 'Unconnected' viewers, and the exact definition of a reaction. Confirm the time window (last 7 days from current date) and whether to include posts with zero reactions.

2. Aggregate reactions per post

Write a subquery or CTE that counts reactions for each post, filtered to the last 7 days. Ensure you include the viewer type associated with each reaction (or post-viewer relationship).

3. Compute average per viewer type

Using the aggregated data, calculate the average number of reactions per post for each viewer type. If including zero-reaction posts, ensure the aggregation accounts for them (e.g., by left joining from posts).

4. Finalize and validate

Write the final SQL query with proper grouping and ordering. Consider edge cases like posts with no reactions and verify that the date filter is correctly applied.

Key Points to Mention

  • Definition of 'reaction' and how it's stored in the schema (e.g., reactions table with post_id and user_id).
  • How to determine viewer type: likely a relationship between the viewer and the post author (e.g., friendship status).
  • Time window: filter reactions (or posts) to the last 7 days using a date column.
  • Handling posts with zero reactions: decide whether to include them in the average (requires left join from posts).
  • Grouping by viewer type and using AVG() on the reaction count per post.
  • Potential need to deduplicate or handle multiple reactions from the same user.

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