Meta·Data Scientist·Technical Phone Screen·Senior

Senior

Sep 2025Remote

Summary

Meta data scientist interview with a heavy SQL focus. The main question was a multi-part fraud detection problem that required chaining several CTEs together and outputting a composite risk score. Technically dense and not the kind of thing you can wing.

Questions Asked (1)

Given tables for users, logins, friend requests, messages, posts, and devices, write a single SQL query that computes three fraud feature flags for the past 7 days and returns only users whose weighted risk score is at least 0.5. The flags are: login from 3+ distinct countries within any rolling 24-hour window, messaging 3+ distinct recipients within 5 minutes of account creation, and sending 20+ friend requests with an acceptance rate under 10%.

Product Analytics & MetricsData ModelingRoot Cause Analysis

Author's notes

This wrecked me a little.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by breaking the problem into three independent feature computations using CTEs, each with its own time-window logic and aggregation. Then join the flags back to the user dimension, apply the weighted risk score threshold, and filter to the past 7 days. Emphasize correctness of window functions and clear assumptions about schema and weights.

Pro tip: Explicitly state your assumptions about the schema (e.g., timestamps, country fields, acceptance status) and the risk weights, and mention that you would validate the query on a small sample before running it at scale. This shows production maturity and avoids silent errors.

1. Clarify schema and define flags

Restate the table structures and precisely define each flag in SQL terms, including time windows and thresholds. Confirm the risk weights and the 7-day lookback window.

2. Compute login country flag

Use a self-join or window function to find users with 3+ distinct countries within any rolling 24-hour window in the past 7 days.

3. Compute messaging and friend request flags

For messaging, join messages to users and count distinct recipients within 5 minutes of account creation. For friend requests, aggregate requests and acceptances per user, then flag those with 20+ requests and acceptance rate < 10%.

4. Combine flags and apply risk score

Join the three flag CTEs on user_id, compute the weighted risk score using the given weights, and filter to users with score >= 0.5.

5. Finalize query and discuss optimizations

Write the final SQL with clear CTEs, ensure only users from the past 7 days are included, and mention potential performance improvements like indexing or partitioning.

Key Points to Mention

Use of window functions (e.g., RANGE BETWEEN INTERVAL '24 hours' PRECEDING) for rolling windows
Handling of time zones and timestamp precision in window calculations
Definition of 'acceptance rate' and how to compute it from friend requests and acceptances
Weighted risk score formula and threshold application
Efficiency considerations: CTEs, indexing, and avoiding cross joins
Assumptions about schema (e.g., country field in logins, recipient_id in messages, status in friend_requests)

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