Write SQL to flag Venmo ATO

SQL case: You are a Decision Scientist on Venmo’s Fraud (ATO) team. Using the schema and sample data below, write a single Standard SQL query that returns candidate ATO events in the last 7 days (treat "today" as 2025-09-01 00:00:00 UTC; last 7 days window is [2025-08-25, 2025-09-01)). A candidate ATO event is defined as: (a) the user’s first successful login from a NEW device_id OR a NEW ip in the prior 30 days, and (b) within 2 hours after that login, the user sends a transfer > $500 to a FIRST-TIME recipient for that sender. Exclude users whose account age < 7 days at login time. If multiple transfers match for the same login, keep only the earliest transfer. Output columns: user_id, login_ts, device_id, ip, reason_code ("NEW_DEVICE", "NEW_IP", or "BOTH"), tx_id, tx_ts, amount_usd, first_time_recipient_flag. Also: 1) ensure no duplicate rows; 2) be explicit about time boundaries; 3) at the end, list the exact indexes you’d recommend to support this query on a PostgreSQL-like warehouse.

Schema:

• users(user_id INT PRIMARY KEY, created_at TIMESTAMP)
• logins(user_id INT, ts TIMESTAMP, ip STRING, device_id STRING, success BOOLEAN)
• transfers(tx_id BIGINT PRIMARY KEY, sender_id INT, recipient_id INT, ts TIMESTAMP, amount_usd DECIMAL(10,2), status STRING) -- status in ('completed','reversed')

Sample data (UTC):

users
+---------+---------------------+
| user_id | created_at |
+---------+---------------------+
| 1 | 2025-07-01 10:00:00 |
| 2 | 2025-08-28 09:00:00 |
| 3 | 2025-05-15 12:00:00 |
| 4 | 2025-08-01 08:30:00 |
+---------+---------------------+

logins
+---------+---------------------+---------------+-----------+---------+
| user_id | ts | ip | device_id | success |
+---------+---------------------+---------------+-----------+---------+
| 1 | 2025-08-20 09:00:00 | 1.1.1.1 | A | TRUE |
| 1 | 2025-08-25 10:10:00 | 2.2.2.2 | B | TRUE |
| 1 | 2025-08-25 10:40:00 | 2.2.2.2 | B | TRUE |
| 2 | 2025-08-29 21:00:00 | 3.3.3.3 | C | TRUE |
| 2 | 2025-08-30 01:00:00 | 4.4.4.4 | C | TRUE |
| 3 | 2025-08-31 15:00:00 | 5.5.5.5 | D | TRUE |
| 3 | 2025-08-31 16:30:00 | 5.5.5.5 | E | TRUE |
| 4 | 2025-09-01 00:10:00 | 6.6.6.6 | F | FALSE |
+---------+---------------------+---------------+-----------+---------+

transfers
+--------+-----------+--------------+---------------------+------------+-----------+
| tx_id | sender_id | recipient_id | ts | amount_usd | status |
+--------+-----------+--------------+---------------------+------------+-----------+
| 101 | 1 | 9 | 2025-08-25 11:15:00 | 650.00 | completed |
| 102 | 1 | 9 | 2025-08-26 09:00:00 | 50.00 | completed |
| 103 | 2 | 8 | 2025-08-30 01:45:00 | 800.00 | completed |
| 104 | 2 | 7 | 2025-08-27 10:00:00 | 20.00 | completed |
| 105 | 3 | 6 | 2025-08-31 16:45:00 | 700.00 | completed |
| 106 | 3 | 6 | 2025-09-01 01:00:00 | 10.00 | reversed |
| 107 | 3 | 5 | 2025-08-10 08:00:00 | 5.00 | completed |
| 108 | 4 | 5 | 2025-09-01 00:20:00 | 900.00 | completed |
+--------+-----------+--------------+---------------------+------------+-----------+

Definitions:

• NEW device/ip: not seen for that user in the 30 days BEFORE the login ts (lookback window is (login_ts - 30 days, login_ts)).
• FIRST-TIME recipient: no prior transfer from sender_id to recipient_id before tx_ts.

Deliverables: A) The SQL producing the specified output; B) Brief justification for your index choices.

Flag candidate Venmo ATO events from risky logins + high-value first-time transfers

You are a Decision Scientist on Venmo’s Account Takeover (ATO) team.

Using the tables and sample data below, write a single Standard SQL query that returns candidate ATO events for the last 7 days, where:

• Treat “today” as 2025-09-01 00:00:00 UTC.
• The last-7-days window is [2025-08-25 00:00:00, 2025-09-01 00:00:00).

A candidate ATO event is defined as:

1. A successful login in the last-7-days window that is the user’s first successful login from a NEW device_id and/or NEW ip, where NEW is defined as not seen for that user in the 30 days BEFORE that login timestamp. The lookback window is (login_ts - 30 days, login_ts).
2. Within 2 hours after that login (i.e., [login_ts, login_ts + 2 hours)), the user sends a transfer with amount_usd > 500 and status = 'completed' to a FIRST-TIME recipient for that sender.
   • FIRST-TIME recipient means there is no prior completed transfer from the same sender_id to the same recipient_id with ts < tx_ts.

Exclude any login where the user’s account age is < 7 days at the login timestamp.

If multiple transfers match for the same login, keep only the earliest matching transfer.

Output columns (one row per qualifying login):

• user_id
• login_ts
• device_id
• ip
• reason_code: 'NEW_DEVICE', 'NEW_IP', or 'BOTH'
• tx_id
• tx_ts
• amount_usd
• first_time_recipient_flag (boolean)

Additional requirements:

• Ensure the query returns no duplicate rows.
• Be explicit about all time boundaries.
• At the end of your SQL (as SQL comments), list the exact indexes you would recommend for a PostgreSQL-like warehouse to support this query (include brief justification per index in comments).

Table users

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
created_at	TIMESTAMP	NOT NULL

Sample data:

created_at	user_id
2025-07-01 10:00:00	1
2025-08-28 09:00:00	2
2025-05-15 12:00:00	3
2025-08-01 08:30:00	4

Table logins

column	type	constraints
user_id	INT	NOT NULL
ts	TIMESTAMP	NOT NULL
ip	VARCHAR(45)	NOT NULL
device_id	VARCHAR(64)	NOT NULL
success	BOOLEAN	NOT NULL

Sample data:

device_id	ip	success	ts	user_id
A	1.1.1.1	true	2025-08-20 09:00:00	1
B	2.2.2.2	true	2025-08-25 10:10:00	1
B	2.2.2.2	true	2025-08-25 10:40:00	1
C	3.3.3.3	true	2025-08-29 21:00:00	2
C	4.4.4.4	true	2025-08-30 01:00:00	2
D	5.5.5.5	true	2025-08-31 15:00:00	3
E	5.5.5.5	true	2025-08-31 16:30:00	3
F	6.6.6.6	false	2025-09-01 00:10:00	4

Table transfers

column	type	constraints
tx_id	BIGINT	PRIMARY KEY, NOT NULL
sender_id	INT	NOT NULL
recipient_id	INT	NOT NULL
ts	TIMESTAMP	NOT NULL
amount_usd	DECIMAL(10,2)	NOT NULL
status	VARCHAR(20)	NOT NULL

Sample data:

amount_usd	recipient_id	sender_id	status	ts	tx_id
650	9	1	completed	2025-08-25 11:15:00	101
50	9	1	completed	2025-08-26 09:00:00	102
800	8	2	completed	2025-08-30 01:45:00	103
20	7	2	completed	2025-08-27 10:00:00	104
700	6	3	completed	2025-08-31 16:45:00	105
10	6	3	reversed	2025-09-01 01:00:00	106
5	5	3	completed	2025-08-10 08:00:00	107
900	5	4	completed	2025-09-01 00:20:00	108

Expected output

Rows for successful logins in [2025-08-25 00:00:00, 2025-09-01 00:00:00) where the login is from a new device and/or IP (not seen in the prior 30 days), the user is at least 7 days old at login time, and the earliest matching completed transfer > $500 to a first-time recipient occurs within 2 hours after the login.

amount_usd	device_id	first_time_recipient_flag	ip	login_ts	reason_code	tx_id	tx_ts	user_id
650.00	B	true	2.2.2.2	2025-08-25 10:10:00	BOTH	101	2025-08-25 11:15:00	1
700.00	D	true	5.5.5.5	2025-08-31 15:00:00	BOTH	105	2025-08-31 16:45:00	3
700.00	E	true	5.5.5.5	2025-08-31 16:30:00	NEW_DEVICE	105	2025-08-31 16:45:00	3

Hints

1. Use NOT EXISTS against prior successful logins for new-device and new-IP checks.
2. Use ROW_NUMBER to keep the first high-value first-time-recipient transfer after each risky login.