Write SQL to flag coordinated fake accounts
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Onsite
Assume today is 2025-09-01. Schema and tiny samples:
users(user_id, created_at, country)
1 | 2025-07-01 | US
2 | 2025-08-10 | IN
3 | 2025-08-15 | US
4 | 2025-08-20 | RU
logins(user_id, login_time, ip, country, device_id)
1 | 2025-08-31 10:00 | 1.1.1.1 | US | d1
1 | 2025-08-31 22:00 | 2.2.2.2 | DE | d2
3 | 2025-08-31 10:05 | 3.3.3.3 | US | d3
4 | 2025-08-31 02:00 | 4.4.4.4 | RU | d4
4 | 2025-08-31 02:30 | 5.5.5.5 | BR | d5
friend_requests(sender_id, receiver_id, sent_time, accepted, accepted_time)
1 | 3 | 2025-08-10 | true | 2025-08-12
4 | 1 | 2025-08-25 | false | null
4 | 2 | 2025-08-25 | false | null
2 | 3 | 2025-08-26 | true | 2025-08-27
messages(sender_id, receiver_id, sent_time)
4 | 1 | 2025-08-31 02:05
4 | 2 | 2025-08-31 02:06
4 | 3 | 2025-08-31 02:07
posts(post_id, user_id, created_time)
10 | 1 | 2025-08-30 09:00
11 | 4 | 2025-08-31 02:10
devices(device_id, user_id, device_type)
d1 | 1 | iOS
d2 | 1 | Android
d3 | 3 | Web
d4 | 4 | Android
d5 | 4 | iOS
Task: Write a single SQL query (CTEs allowed) that outputs user_id and three feature flags for the last 7 days (2025-08-26 to 2025-09-01):
F1=1 if within any 24h window the user logs in from ≥3 distinct countries; F2=1 if within 5 minutes of account creation the user messages ≥3 distinct recipients; F3=1 if in the last 7 days the user sent ≥20 friend requests and acceptance rate <10%. Also output a risk_score = 0.5*F1 + 0.3*F2 + 0.2*F3. Return only users with risk_score ≥0.5.
Quick Answer: This question evaluates proficiency in SQL-based data manipulation, temporal windowing, event-table joins, aggregation and feature engineering for fraud detection, including computing weighted risk_score flags across user activity.