Quick Overview

This question evaluates advanced SQL analytics skills and competency in data modeling for social feed metrics, including relationship joins, viewer-day temporal aggregation, weighted interaction scoring, and per-user top-N ranking.

Write SQL for social feed metrics and ties

Company: Meta

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

You are given the following schema (PostgreSQL) and sample rows. Assume UTC timestamps and that friendships are static over the sample window. users(user_id INT, join_date DATE) friendships(user_id INT, friend_id INT) -- undirected; both directions present posts(post_id INT, author_id INT, created_at TIMESTAMP) feed_impressions(imp_id INT, user_id INT, post_id INT, impression_ts TIMESTAMP) interactions(int_id INT, user_id INT, post_id INT, type VARCHAR CHECK (type IN ('like','comment','share')), interaction_ts TIMESTAMP) Sample data: users +---------+------------+ | user_id | join_date | +---------+------------+ | 1 | 2025-06-01 | | 2 | 2025-06-15 | | 3 | 2025-07-01 | +---------+------------+ friendships +---------+-----------+ | user_id | friend_id | +---------+-----------+ | 1 | 2 | | 2 | 1 | +---------+-----------+ posts +---------+-----------+---------------------+ | post_id | author_id | created_at | +---------+-----------+---------------------+ | 10 | 2 | 2025-08-01 10:00:00 | | 11 | 3 | 2025-08-01 11:00:00 | | 12 | 2 | 2025-08-02 09:00:00 | | 13 | 3 | 2025-07-15 08:00:00 | +---------+-----------+---------------------+ feed_impressions +--------+---------+---------+---------------------+ | imp_id | user_id | post_id | impression_ts | +--------+---------+---------+---------------------+ | 100 | 1 | 10 | 2025-08-01 10:05:00 | | 101 | 1 | 11 | 2025-08-01 11:05:00 | | 102 | 1 | 12 | 2025-08-02 09:10:00 | | 103 | 2 | 11 | 2025-08-01 12:00:00 | | 104 | 1 | 13 | 2025-07-15 08:05:00 | +--------+---------+---------+---------------------+ interactions +--------+---------+---------+-------------+---------------------+ | int_id | user_id | post_id | type | interaction_ts | +--------+---------+---------+-------------+---------------------+ | 200 | 1 | 10 | like | 2025-08-01 10:06:00 | | 201 | 1 | 11 | comment | 2025-08-01 11:06:00 | | 202 | 1 | 12 | share | 2025-08-02 09:12:00 | | 203 | 2 | 11 | like | 2025-08-01 12:05:00 | | 204 | 1 | 13 | like | 2025-07-15 08:06:00 | +--------+---------+---------+-------------+---------------------+ Tasks (be explicit about the aggregation level; do not accidentally aggregate at the post level when the unit is viewer-day): 1) Classify each impression as 'friend' vs 'unconnected' by joining feed_impressions -> posts.author_id -> friendships relative to the viewer (user_id). Write SQL to compute, for each (user_id, impression_date), the fraction of impressions that are from friends vs unconnected authors. 2) Define a weighted social engagement score per viewer-day and content_source ('friend'/'unconnected') where weights are like=1, comment=3, share=5. Compute the score using interactions by the same viewer on the corresponding posts on that date; impressions with no interactions contribute 0. Return one row per (user_id, dt, content_source) with impressions, interactions_by_type, and weighted_score. 3) For date = '2025-08-01', return the top 2 posts per user by that user's weighted engagement score (same weights as above), breaking ties with dense_rank() so that all tied posts at the cutoff are included; if still tied, order by post_id ASC. Show the SQL. 4) Compute month-over-month percent change in weighted engagement per content_source between 2025-07 and 2025-08, aggregating across all users. Handle missing months by generating a month calendar CTE (2025-07 to 2025-08) and treating absent months as zero before applying LAG. Return: month, content_source, score, mom_pct_change. Explain any assumptions about multiple interactions on the same post and time-zone boundaries.

Overview: This question evaluates advanced SQL analytics skills and competency in data modeling for social feed metrics, including relationship joins, viewer-day temporal aggregation, weighted interaction scoring, and per-user top-N ranking.

Read the full Meta Data Scientist interview experience this question came from

Friend vs Unconnected Impression Fractions by Viewer-Day

You are given the tables below. Each feed impression is a (viewer, post, timestamp) event. Classify each impression as: - 'friend' if the post's author is a friend of the viewer (using friendships where both directions are present) - 'unconnected' otherwise Write SQL to compute, for each (user_id, impression_date), the number of friend impressions, the number of unconnected impressions, and the fraction of impressions from friends vs unconnected authors. The aggregation unit must be viewer-day (user_id, impression_date), not post-level. Return columns: (user_id, impression_date, friend_impressions, unconnected_impressions, total_impressions, friend_fraction, unconnected_fraction).

Tables

users(user_id INT, join_date DATE)

friendships(user_id INT, friend_id INT)

posts(post_id INT, author_id INT, created_at TIMESTAMP)

feed_impressions(imp_id INT, user_id INT, post_id INT, impression_ts TIMESTAMP)

interactions(int_id INT, user_id INT, post_id INT, type VARCHAR(10), interaction_ts TIMESTAMP)

Hints

  1. Join feed_impressions -> posts to get author_id, then LEFT JOIN friendships on (viewer=user_id, friend_id=author_id).
  2. Aggregate by (user_id, DATE(impression_ts)) and compute fractions using a decimal cast and NULLIF to avoid division by zero.

Weighted Engagement Score by Viewer-Day and Content Source

Define content_source per impression as in Question 1 ('friend' vs 'unconnected'). Define a weighted engagement score per (user_id, dt, content_source) where weights are: - like = 1 - comment = 3 - share = 5 Compute the score using interactions made by the same viewer on the corresponding posts on that same date (UTC). Impressions with no interactions contribute 0. Return one row per (user_id, dt, content_source) with: - impressions (count of impressions) - likes, comments, shares (counts of interactions by type) - weighted_score Important: do not accidentally count impressions multiple times due to joining to multiple interactions (the unit is viewer-day-content_source).

Tables

users(user_id INT, join_date DATE)

friendships(user_id INT, friend_id INT)

posts(post_id INT, author_id INT, created_at TIMESTAMP)

feed_impressions(imp_id INT, user_id INT, post_id INT, impression_ts TIMESTAMP)

interactions(int_id INT, user_id INT, post_id INT, type VARCHAR(10), interaction_ts TIMESTAMP)

Hints

  1. Use COUNT(DISTINCT imp_id) for impressions so multiple interactions on the same impression don't multiply impression counts.
  2. Left join interactions on (user_id, post_id, date) so impressions with no interactions still appear with 0 score.

Top 2 Posts per User by Weighted Engagement (Dense Rank) on 2025-08-01

For date = '2025-08-01' (UTC), return the top 2 posts per user by that user's weighted engagement score on that post, using weights like=1, comment=3, share=5. Use DENSE_RANK() so that if there are ties at the cutoff (rank 2), all tied posts are included. For final display ordering, if multiple posts have the same score for a user, order by post_id ASC. Return columns: (user_id, post_id, weighted_score, engagement_rank).

Tables

users(user_id INT, join_date DATE)

friendships(user_id INT, friend_id INT)

posts(post_id INT, author_id INT, created_at TIMESTAMP)

feed_impressions(imp_id INT, user_id INT, post_id INT, impression_ts TIMESTAMP)

interactions(int_id INT, user_id INT, post_id INT, type VARCHAR(10), interaction_ts TIMESTAMP)

Hints

  1. First aggregate interactions to (user_id, post_id) for the target date, then rank.
  2. Use DENSE_RANK() and filter to rank <= 2 to include ties at the cutoff.

Month-over-Month % Change in Weighted Engagement by Content Source (2025-07 to 2025-08)

Compute month-over-month percent change in weighted engagement per content_source ('friend'/'unconnected') between months 2025-07 and 2025-08, aggregated across all users. Definitions: - Classify content_source relative to the viewer (the user who saw/interacted) by joining posts.author_id to friendships (as in earlier tasks). - Engagement is based on interactions on posts that the viewer saw in their feed on the same UTC date as the interaction. - Weights: like=1, comment=3, share=5. Requirements: 1) Use a month calendar CTE that generates months from 2025-07-01 to 2025-08-01 (inclusive). 2) Treat missing months as score=0 before applying LAG. 3) Return: month (as DATE month start), content_source, score, mom_pct_change. 4) Avoid double-counting if a user has multiple impressions of the same post on the same day (assume you should attribute interactions to that (user, post, day) at most once). Assumptions to use in your query (do not write prose, implement in SQL): - Timestamps are UTC; day boundaries use DATE(timestamp). - Multiple interactions are additive (each interaction row contributes its weight). - If the prior month's score is 0, mom_pct_change should be NULL (to avoid division by zero).

Tables

users(user_id INT, join_date DATE)

friendships(user_id INT, friend_id INT)

posts(post_id INT, author_id INT, created_at TIMESTAMP)

feed_impressions(imp_id INT, user_id INT, post_id INT, impression_ts TIMESTAMP)

interactions(int_id INT, user_id INT, post_id INT, type VARCHAR(10), interaction_ts TIMESTAMP)

Hints

  1. Build a month calendar and cross join it with the set of content sources, then left join actual scores and COALESCE to 0.
  2. To avoid double counting due to multiple impressions of the same (user, post, day), join interactions to a DISTINCT list of (user_id, post_id, dt, content_source).

Loading coding console...