← Meta Interview Insights

Meta·Data Scientist·Technical Phone Screen·Senior

Senior
May 2026

Summary

SQL-heavy technical screen for a DS role at Meta. Two questions, both on the harder side of what I expected, and the second one had enough edge cases to make me second-guess myself the whole way through.

Questions Asked (2)

Q1

Given a messages table with sender and receiver IDs, write a SQL query to return all users who chatted with more than 5 distinct other users on a given date. 'Chatted with' means at least one message exchanged in either direction.

Product Analytics & MetricsAlgorithms & Data Structures
Author's notes

This one felt manageable once I remembered to union the sender and receiver sides before doing the distinct count.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, normalize the messages table into a user-pair format by using a UNION of sender-receiver and receiver-sender pairs, then filter for the given date. Next, count distinct chat partners per user and filter for counts greater than 5. Finally, return the user IDs.

Pro tip: Clarify whether 'chatted with' requires bidirectional exchange or just any message; in either case, using a UNION of both directions ensures you capture all interactions. Also, consider performance by filtering on date before the UNION to reduce data volume.

1. Clarify requirements and edge cases

Confirm the definition of 'chatted with' (any message vs. bidirectional exchange) and whether the date is based on message timestamp. Discuss handling of duplicate messages and self-chats.

2. Normalize interactions into user pairs

Use a UNION of two SELECT statements: one with sender as user and receiver as partner, and one with receiver as user and sender as partner. This ensures each interaction is represented from both users' perspectives.

3. Filter by date and count distinct partners

Apply a WHERE clause to filter messages on the given date, then group by user and count distinct partners. Use COUNT(DISTINCT partner_id) to avoid counting the same partner multiple times.

4. Apply threshold and return results

Use a HAVING clause to filter users with more than 5 distinct partners. Return the user IDs as the final result.

Key Points to Mention

  • Use of UNION (not UNION ALL) to deduplicate pairs if needed, or UNION ALL if duplicates are acceptable before distinct count.
  • Filtering on date before the UNION to improve performance.
  • Using COUNT(DISTINCT partner_id) to ensure distinct users are counted.
  • Handling of self-chats (sender = receiver) by excluding them if necessary.
  • Consideration of bidirectional communication: if required, ensure both directions exist by using INTERSECT or self-join.
  • Indexing on date and sender/receiver columns for query optimization.

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 find all senders who received a reply within 60 seconds of a message they sent on a given date. A reply must come from the original receiver back to the original sender, and only the earliest opposing-direction message after each sent message qualifies.

Algorithms & Data StructuresData Modeling
Author's notes

This wrecked me a bit.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Use a self-join on the messages table to pair each sent message with the earliest opposing-direction reply within 60 seconds, ensuring the reply is from the original receiver to the original sender. Filter by the given date and use a window function or correlated subquery to select only the earliest reply per sent message.

Pro tip: Clarify the definition of 'reply' and 'opposing direction' upfront, and consider edge cases like multiple replies within 60 seconds or messages sent near midnight. Also, discuss indexing on (sender, receiver, timestamp) for performance.

1. Understand the schema and requirements

Identify the messages table with columns like sender_id, receiver_id, message_id, and timestamp. Clarify that a reply must be from the original receiver to the original sender, and only the earliest such reply within 60 seconds counts.

2. Filter messages by date

Restrict the analysis to messages sent on the given date using a WHERE clause on the timestamp (e.g., DATE(timestamp) = 'given_date').

3. Self-join to find potential replies

Join the messages table to itself: m1 for sent messages, m2 for replies. Conditions: m2.sender_id = m1.receiver_id, m2.receiver_id = m1.sender_id, m2.timestamp > m1.timestamp, and m2.timestamp <= m1.timestamp + INTERVAL 60 SECOND.

4. Select the earliest reply per sent message

Use a window function like ROW_NUMBER() OVER (PARTITION BY m1.message_id ORDER BY m2.timestamp) and filter for row_number = 1, or use a correlated subquery with MIN(m2.timestamp).

5. Return distinct senders

Select DISTINCT m1.sender_id from the result to list all senders who received a qualifying reply.

Key Points to Mention

  • Self-join to match sent messages with replies in the opposite direction.
  • Time window condition: reply timestamp between sent timestamp and sent timestamp + 60 seconds.
  • Use of window functions (ROW_NUMBER) or correlated subquery to get the earliest reply.
  • Filtering by the given date on the sent message timestamp.
  • Handling potential ties or multiple replies within 60 seconds by selecting only the earliest.
  • Performance considerations: indexing on (sender_id, receiver_id, timestamp) and avoiding unnecessary columns.

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