Quick Overview

This question evaluates proficiency in SQL analytics, specifically joins, filtering, aggregation (distinct counts), percentile functions, time-windowed grouping, handling soft-deleted records, and writing efficient queries via CTEs.

Write SQL for comment analytics

Company: Meta

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Onsite

You are given the following schema and tiny sample data. Schema: - users(user_id INT PRIMARY KEY, country VARCHAR, created_at DATE) - posts(post_id INT PRIMARY KEY, author_id INT, created_at DATE) - comments(comment_id INT PRIMARY KEY, post_id INT, user_id INT, created_at DATE, content VARCHAR, is_deleted BOOLEAN) Sample tables (minimal, not exhaustive): users user_id | country | created_at 1 | US | 2025-08-01 2 | US | 2025-08-05 3 | CA | 2025-08-10 4 | US | 2025-08-20 posts post_id | author_id | created_at 10 | 1 | 2025-08-25 11 | 2 | 2025-08-28 12 | 3 | 2025-08-30 comments comment_id | post_id | user_id | created_at | content | is_deleted 100 | 10 | 2 | 2025-08-26 | "Nice!" | false 101 | 10 | 3 | 2025-08-27 | "Great post" | false 102 | 11 | 1 | 2025-08-28 | "Thanks" | false 103 | 11 | 2 | 2025-08-30 | "I agree" | true 104 | 12 | 4 | 2025-08-31 | "Wow" | false 105 | 12 | 1 | 2025-09-01 | "Following" | false 106 | 12 | 3 | 2025-09-01 | "Awesome insights"| false 107 | 10 | 1 | 2025-09-01 | "Self" | false Assume "today" = 2025-09-01. For the 7-day window 2025-08-26 through 2025-09-01 (inclusive), write ANSI SQL (you may assume percentile_cont is available) to: (a) Return the top 3 posts by distinct commenter count, excluding deleted comments. Output: post_id, unique_commenters, distinct_commenter_countries. Break ties by newer posts (posts.created_at desc) then lower post_id. (b) For each post and day in the window, compute P50 and P90 of comment text length (use length(content)) over non-deleted comments. Output: day, post_id, p50_len, p90_len. Include days with at least 1 non-deleted comment. (c) List users who commented on their own posts in the window, with columns: user_id, self_comment_count, last_self_comment_at. Order by self_comment_count desc, user_id asc. Aim for readable, efficient queries using CTEs; avoid unnecessary scans.

Overview: This question evaluates proficiency in SQL analytics, specifically joins, filtering, aggregation (distinct counts), percentile functions, time-windowed grouping, handling soft-deleted records, and writing efficient queries via CTEs.

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

Top 3 posts by distinct commenter count in a 7-day window

Using the tables below, and considering only the 7-day window from 2025-08-26 through 2025-09-01 (inclusive), return the top 3 posts by distinct commenter count, excluding deleted comments. For each qualifying post, output: post_id, unique_commenters (COUNT of distinct users who commented on the post in the window), and distinct_commenter_countries (COUNT of distinct countries of those commenters). Break ties by newer posts first (posts.created_at DESC) and then lower post_id. Use readable ANSI SQL; you may assume percentile_cont is available, though it is not needed for this sub-question.

Tables

users(user_id INT, country VARCHAR(2), created_at DATE)

posts(post_id INT, author_id INT, created_at DATE)

comments(comment_id INT, post_id INT, user_id INT, created_at DATE, content VARCHAR(255), is_deleted BOOLEAN)

Hints

  1. First aggregate comments per post (COUNT DISTINCT user_id and DISTINCT country) over the 7-day window, excluding deleted comments.
  2. Use a window function like ROW_NUMBER() to rank posts and then filter to the top 3 with the specified tie-breakers.

Daily P50 and P90 comment length per post in a 7-day window

Using the same tables, and considering only non-deleted comments between 2025-08-26 and 2025-09-01 (inclusive), compute for each post and each day in that window the P50 and P90 of comment text length. Use LENGTH(content) for the comment length and percentile_cont for the percentiles. Output columns: day (the comment date), post_id, p50_len, p90_len. Include only (day, post_id) combinations that have at least one non-deleted comment. Aim for readable ANSI SQL.

Tables

users(user_id INT, country VARCHAR(2), created_at DATE)

posts(post_id INT, author_id INT, created_at DATE)

comments(comment_id INT, post_id INT, user_id INT, created_at DATE, content VARCHAR(255), is_deleted BOOLEAN)

Hints

  1. Filter to the 7-day window and non-deleted comments, then group by created_at (as day) and post_id.
  2. Use percentile_cont(0.5) and percentile_cont(0.9) WITHIN GROUP (ORDER BY LENGTH(content)) to compute P50 and P90.

Users who self-commented in a 7-day window

Using the same tables, find users who commented on their own posts between 2025-08-26 and 2025-09-01 (inclusive), considering only non-deleted comments. A self-comment is a comment where comments.user_id = posts.author_id for the commented post. For each such user, output: user_id, self_comment_count (number of their non-deleted self-comments in the window), and last_self_comment_at (the latest comment date in the window). Order the result by self_comment_count DESC, then user_id ASC.

Tables

users(user_id INT, country VARCHAR(2), created_at DATE)

posts(post_id INT, author_id INT, created_at DATE)

comments(comment_id INT, post_id INT, user_id INT, created_at DATE, content VARCHAR(255), is_deleted BOOLEAN)

Hints

  1. Join comments to posts and filter to rows where comments.user_id equals posts.author_id, within the date window and not deleted.
  2. Aggregate per user_id using COUNT(*) and MAX(created_at), then order by the requested columns.

Loading coding console...