Coding questions · Meta
Write SQL to flag coordinated fake accounts
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.
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.5F1 + 0.3F2 + 0.2*F3. Return only users with risk_score ≥0.5.
Write SQL to flag coordinated fake accounts
Assume the current date is 2025-06-01.
You are given the following tables:
- users(user_id, created_at, country)
- logins(user_id, login_time, ip, country, device_id)
- friend_requests(sender_id, receiver_id, sent_time, accepted, accepted_time)
- messages(sender_id, receiver_id, sent_time)
- posts(post_id, user_id, created_time)
- devices(device_id, user_id, device_type)
Using these tables, write a single SQL query (CTEs allowed) that outputs, for each user, the fields user_id, F1, F2, F3, and risk_score, where:
• Consider the 7-day period from 2025-05-26 to 2025-06-01 (inclusive).
• F1 = 1 if, using only login rows with login_time between '2025-05-26 00:00:00' and '2025-06-01 23:59:59', there exists at least one 24-hour window in which that user logs in from 3 or more distinct countries; otherwise F1 = 0.
• F2 = 1 if, within 5 minutes of the account creation time (created_at), the user sends messages to 3 or more distinct recipients (receiver_id); otherwise F2 = 0. (This condition is independent of the 7-day window; it uses all messages relative to created_at.)
• F3 = 1 if, considering only friend_requests with sent_time between '2025-05-26 00:00:00' and '2025-06-01 23:59:59', the user (as sender_id) sends at least 20 friend requests and their acceptance rate in that period is less than 10% (acceptance rate = accepted requests / total requests); otherwise F3 = 0.
Compute a risk_score for each user as: risk_score = 0.5 * F1 + 0.3 * F2 + 0.2 * F3
Return only users with risk_score >= 0.5.
Write a single SQL query that produces this result.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY, NOT NULL |
| created_at | TIMESTAMP | NOT NULL |
| country | VARCHAR(2) | NOT NULL |
Sample data:
| country | created_at | user_id |
|---|---|---|
| US | 2025-05-01 10:00:00 | 1 |
| IN | 2025-05-27 09:00:00 | 2 |
| US | 2025-05-30 11:00:00 | 3 |
| RU | 2025-05-31 02:00:00 | 4 |
Table logins
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| login_time | TIMESTAMP | NOT NULL |
| ip | VARCHAR(45) | NOT NULL |
| country | VARCHAR(2) | NOT NULL |
| device_id | VARCHAR(10) | NOT NULL |
Sample data:
| country | device_id | ip | login_time | user_id |
|---|---|---|---|---|
| US | d1 | 1.1.1.1 | 2025-05-30 10:00:00 | 1 |
| DE | d2 | 2.2.2.2 | 2025-05-30 18:00:00 | 1 |
| FR | d1 | 3.3.3.3 | 2025-05-31 09:00:00 | 1 |
| IN | d3 | 4.4.4.4 | 2025-05-29 09:30:00 | 2 |
| US | d3 | 5.5.5.5 | 2025-05-29 15:00:00 | 2 |
| DE | d4 | 6.6.6.6 | 2025-05-30 08:00:00 | 2 |
| US | d5 | 7.7.7.7 | 2025-05-27 10:00:00 | 3 |
| US | d5 | 8.8.8.8 | 2025-05-31 10:00:00 | 3 |
| RU | d6 | 9.9.9.9 | 2025-05-31 02:00:00 | 4 |
| BR | d7 | 10.10.10.10 | 2025-05-31 02:30:00 | 4 |
Table friend_requests
| column | type | constraints |
|---|---|---|
| sender_id | INT | NOT NULL |
| receiver_id | INT | NOT NULL |
| sent_time | TIMESTAMP | NOT NULL |
| accepted | BOOLEAN | NOT NULL |
| accepted_time | TIMESTAMP |
Sample data:
| accepted | accepted_time | receiver_id | sender_id | sent_time |
|---|---|---|---|---|
| true | 2025-05-27 09:00:00 | 3 | 1 | 2025-05-27 08:00:00 |
| false | NULL | 1 | 4 | 2025-05-31 02:05:00 |
| false | NULL | 1 | 2 | 2025-05-26 10:00:00 |
| false | NULL | 3 | 2 | 2025-05-26 11:00:00 |
| false | NULL | 4 | 2 | 2025-05-26 12:00:00 |
| false | NULL | 1 | 2 | 2025-05-27 09:15:00 |
| false | NULL | 3 | 2 | 2025-05-27 09:30:00 |
| false | NULL | 4 | 2 | 2025-05-27 09:45:00 |
| false | NULL | 1 | 2 | 2025-05-28 10:00:00 |
| false | NULL | 3 | 2 | 2025-05-28 10:15:00 |
| false | NULL | 4 | 2 | 2025-05-28 10:30:00 |
| false | NULL | 1 | 2 | 2025-05-29 11:00:00 |
| false | NULL | 3 | 2 | 2025-05-29 11:15:00 |
| false | NULL | 4 | 2 | 2025-05-29 11:30:00 |
| false | NULL | 1 | 2 | 2025-05-30 12:00:00 |
| false | NULL | 3 | 2 | 2025-05-30 12:15:00 |
| false | NULL | 4 | 2 | 2025-05-30 12:30:00 |
| false | NULL | 1 | 2 | 2025-05-31 13:00:00 |
| false | NULL | 3 | 2 | 2025-05-31 13:15:00 |
| false | NULL | 4 | 2 | 2025-05-31 13:30:00 |
| true | 2025-06-01 10:00:00 | 1 | 2 | 2025-06-01 09:00:00 |
| false | NULL | 3 | 2 | 2025-06-01 09:30:00 |
Table messages
| column | type | constraints |
|---|---|---|
| sender_id | INT | NOT NULL |
| receiver_id | INT | NOT NULL |
| sent_time | TIMESTAMP | NOT NULL |
Sample data:
| receiver_id | sender_id | sent_time |
|---|---|---|
| 1 | 4 | 2025-05-31 02:01:00 |
| 2 | 4 | 2025-05-31 02:02:00 |
| 3 | 4 | 2025-05-31 02:03:00 |
| 1 | 2 | 2025-05-27 10:30:00 |
Table posts
| column | type | constraints |
|---|---|---|
| post_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| created_time | TIMESTAMP | NOT NULL |
Sample data:
| created_time | post_id | user_id |
|---|---|---|
| 2025-05-30 09:00:00 | 10 | 1 |
| 2025-05-31 02:10:00 | 11 | 4 |
Table devices
| column | type | constraints |
|---|---|---|
| device_id | VARCHAR(10) | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| device_type | VARCHAR(20) | NOT NULL |
Sample data:
| device_id | device_type | user_id |
|---|---|---|
| d1 | iOS | 1 |
| d2 | Android | 1 |
| d3 | Web | 2 |
| d4 | Android | 2 |
| d5 | Web | 3 |
| d6 | Android | 4 |
| d7 | iOS | 4 |
Expected output
For the sample data, users 1 and 2 meet the risk threshold. User 1 has F1=1 (3 countries in a 24-hour login window) and F2=0, F3=0, giving risk_score=0.5. User 2 has F1=1 and F3=1 (at least 20 friend requests with <10% acceptance), F2=0, giving risk_score=0.7. Users 3 and 4 do not reach risk_score >= 0.5.
| F1 | F2 | F3 | risk_score | user_id |
|---|---|---|---|---|
| 1 | 0 | 0 | 0.5 | 1 |
| 1 | 0 | 1 | 0.7 | 2 |
Hints
- For F1, consider a self-join on the logins table (restricted to the 7-day window) to count distinct countries in a rolling 24-hour window per user.
- For F3, compute acceptance rate as AVG(CASE WHEN accepted THEN 1.0 ELSE 0.0 END) over friend_requests in the 7-day window, and then apply the count and rate thresholds.