Quick Overview

This question evaluates SQL data manipulation skills including join semantics (LEFT vs INNER), deduplicated aggregations, time-window filtering, ranking/tie-breaking, and recommendation logic for computing unique viewers, CTRs, and post-level hashtag suggestions.

Write SQL for hashtag analytics and joins

Company: Meta

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

Assume today = 2025-09-01. Schema and small sample data are below. Use ANSI SQL; explain any dialect-specific functions you choose. Where asked, explain precisely why LEFT JOIN is required and what would break with INNER JOIN. Tables: - Users(user_id INT, join_date DATE) - Posts(post_id INT, author_id INT, created_at TIMESTAMP) - PostHashtags(post_id INT, hashtag VARCHAR) - Impressions(user_id INT, post_id INT, seen_at TIMESTAMP) -- each row = a user saw a post (and any hashtags on it) once - HashtagClicks(user_id INT, post_id INT, hashtag VARCHAR, clicked_at TIMESTAMP) Sample rows (minimal but illustrative): Users user_id | join_date 1 | 2025-06-01 2 | 2025-07-15 3 | 2025-08-20 Posts post_id | author_id | created_at 10 | 1 | 2025-08-28 10:00 11 | 2 | 2025-08-30 09:30 12 | 1 | 2025-08-25 12:00 PostHashtags post_id | hashtag 10 | travel 10 | food 11 | travel -- post 12 intentionally has no hashtags Impressions user_id | post_id | seen_at 1 | 11 | 2025-08-31 18:00 2 | 10 | 2025-08-29 08:00 2 | 11 | 2025-08-31 09:00 3 | 10 | 2025-08-29 09:00 3 | 12 | 2025-08-31 20:00 HashtagClicks user_id | post_id | hashtag | clicked_at 3 | 10 | travel | 2025-08-29 09:05 2 | 11 | travel | 2025-08-31 09:02 Tasks: 1) Top hashtags by unique viewers in the last 7 days (2025-08-26 to 2025-09-01 inclusive): Return hashtag, unique_viewers, and rank; break ties deterministically. Hint: viewers of a hashtag are users who saw a post containing that hashtag. Use date functions rather than hardcoding the window. 2) Hashtag CTR in the last 7 days: For each hashtag, compute CTR = distinct users who clicked that hashtag on any post ÷ distinct users who were exposed to that hashtag on any post. Ensure the denominator only counts exposures where that hashtag was present. Handle users who saw multiple posts with the same hashtag without double‑counting. 3) Post‑level recommendations: For each post created in the last 7 days, suggest up to 2 hashtags that its viewers have clicked most in the last 30 days (2025-08-03 to 2025-09-01), excluding hashtags already on the post. Write a query that: - Finds viewers of the post (from Impressions), - Aggregates their hashtag clicks across any posts in the 30‑day window, - Excludes hashtags already present on the target post via LEFT JOIN and IS NULL, - Returns at most 2 suggestions per post, with stable tie‑breaking. Then: Explain exactly what rows would be lost if you replaced the LEFT JOIN exclusion with an INNER JOIN, especially for posts that currently have no hashtags or when viewers never clicked anything. 4) Quality checks: Write a query to list posts with zero hashtags that still received more than 1 impression in the last 7 days. Explain why RIGHT JOIN is unnecessary here and how a LEFT JOIN against PostHashtags enables detection of missing‑child rows.

Overview: This question evaluates SQL data manipulation skills including join semantics (LEFT vs INNER), deduplicated aggregations, time-window filtering, ranking/tie-breaking, and recommendation logic for computing unique viewers, CTRs, and post-level hashtag suggestions.

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

Top hashtags by unique viewers (2025-05-26 to 2025-06-01)

Using the tables below and treating 2025-06-01 as "today", write an ANSI SQL query to find the top hashtags by unique viewers between 2025-05-26 and 2025-06-01 inclusive. A "viewer" of a hashtag is any user who saw a post containing that hashtag during that date range, based on Impressions.seen_at. Each user should be counted at most once per hashtag, even if they saw multiple posts with that hashtag. Return the following columns: - hashtag - unique_viewers (distinct users who saw at least one post with that hashtag in the date range) - hashtag_rank (1 = most viewers), breaking ties deterministically by sorting hashtags alphabetically. Use only the tables and sample data defined below.

Tables

Posts(post_id INT, author_id INT, created_at TIMESTAMP)

PostHashtags(post_id INT, hashtag VARCHAR(50))

Impressions(user_id INT, post_id INT, seen_at TIMESTAMP)

HashtagClicks(user_id INT, post_id INT, hashtag VARCHAR(50), clicked_at TIMESTAMP)

Hints

  1. Join Impressions to PostHashtags on post_id to know which hashtags each impression exposed.
  2. Use COUNT(DISTINCT user_id) and a window function (RANK or DENSE_RANK) to assign ranks by viewer count.

Hashtag CTR over a 7-day window

Using the same schema, compute hashtag CTR between 2025-05-26 and 2025-06-01 inclusive. For each hashtag that was exposed in that window: - The denominator (exposed_users) is the number of distinct users who saw at least one post containing that hashtag in that window, based on Impressions.seen_at and PostHashtags. - The numerator (clicking_users) is the number of distinct users who clicked that hashtag on any post in that window, based on HashtagClicks.clicked_at. Each user should be counted at most once per hashtag in both numerator and denominator, even if they saw or clicked multiple posts with that hashtag. Return: - hashtag - exposed_users - clicking_users - ctr = clicking_users / exposed_users (as a numeric value). Use only the tables and sample data defined below.

Tables

Posts(post_id INT, author_id INT, created_at TIMESTAMP)

PostHashtags(post_id INT, hashtag VARCHAR(50))

Impressions(user_id INT, post_id INT, seen_at TIMESTAMP)

HashtagClicks(user_id INT, post_id INT, hashtag VARCHAR(50), clicked_at TIMESTAMP)

Hints

  1. First compute exposures per hashtag from Impressions + PostHashtags, then clicks per hashtag from HashtagClicks.
  2. Join the two aggregates and use COUNT(DISTINCT user_id) in both; use COALESCE to handle hashtags with zero clicks.

Post-level hashtag recommendations using viewer click history

For each post created in the last 7 days (between 2025-05-26 and 2025-06-01 inclusive, based on Posts.created_at), suggest up to 2 hashtags that its viewers have clicked most in the last 30 days (from 2025-05-03 to 2025-06-01 inclusive, based on HashtagClicks.clicked_at). Use the following logic: 1. Identify the target posts: posts with created_at between 2025-05-26 and 2025-06-01. 2. For each target post, find its viewers from Impressions (users who saw that post, regardless of when they saw it). 3. For those viewers, gather all hashtag clicks they made on any post in the 30-day window 2025-05-03 to 2025-06-01. 4. Aggregate the clicks per (target_post_id, hashtag) to get total clicks by that post's viewers. 5. Exclude hashtags that are already present on the target post. Do this by LEFT JOIN-ing to PostHashtags on (post_id, hashtag) and filtering where the joined row is NULL. 6. Rank candidate hashtags per post by total clicks (descending), breaking ties deterministically by hashtag alphabetically, and return at most 2 suggestions per post. Return at least these columns: - post_id - hashtag (suggested hashtag) - total_clicks (number of clicks on that hashtag by viewers of that post in the 30-day window) - suggestion_rank (1 or 2 within each post) Then, in a separate explanation (not in SQL), describe exactly what recommendations would be lost if you replaced the LEFT JOIN used for excluding existing hashtags with an INNER JOIN instead, especially for posts that currently have no hashtags.

Tables

Posts(post_id INT, author_id INT, created_at TIMESTAMP)

PostHashtags(post_id INT, hashtag VARCHAR(50))

Impressions(user_id INT, post_id INT, seen_at TIMESTAMP)

HashtagClicks(user_id INT, post_id INT, hashtag VARCHAR(50), clicked_at TIMESTAMP)

Hints

  1. Build this in stages with CTEs: target posts, their viewers, viewer clicks in the 30-day window, then exclusion of existing hashtags, then ranking.
  2. Use a LEFT JOIN from viewer_clicks to PostHashtags and filter WHERE PostHashtags.post_id IS NULL to exclude hashtags already present while still keeping candidates for posts with no hashtags.

Quality check: posts with no hashtags but many impressions

Write an ANSI SQL query to list posts that have zero hashtags but still received more than 1 impression in the last 7 days, between 2025-05-26 and 2025-06-01 inclusive. Use these rules: - Count impressions based on Impressions.seen_at in that date range. - Identify posts with no hashtags by joining Posts to PostHashtags and checking for missing matches. Return at least: - post_id - author_id - impression_count (number of impressions in the 7-day window). Then, in a separate explanation (not in SQL), explain why a RIGHT JOIN is unnecessary here and how using a LEFT JOIN from Posts to PostHashtags lets you detect posts that are missing child hashtag rows.

Tables

Posts(post_id INT, author_id INT, created_at TIMESTAMP)

PostHashtags(post_id INT, hashtag VARCHAR(50))

Impressions(user_id INT, post_id INT, seen_at TIMESTAMP)

HashtagClicks(user_id INT, post_id INT, hashtag VARCHAR(50), clicked_at TIMESTAMP)

Hints

  1. Join Posts to Impressions to count impressions per post in the 7-day window.
  2. LEFT JOIN Posts to PostHashtags and filter WHERE PostHashtags.post_id IS NULL to find posts with no hashtags; a RIGHT JOIN is not needed because Posts is the natural driving table.

Loading coding console...