Account takeover detection in SQL

What account takeover actually looks like

Imagine it is Monday morning at a payments company. The trust and safety lead pings you on Slack: a customer with a 4-year-old account just lost 8,000 USD over the weekend. Logs show a successful login from a new IP in a country the user has never logged in from, an email change two minutes later, a new card added inside the hour, and a series of payouts that drained the wallet. Your job is not to write a long retrospective. Your job is to build a SQL query that would have flagged this account in the first 60 seconds, before the email change locked the real owner out.

That is what account takeover (ATO) detection is. An attacker has obtained credentials — through credential stuffing, phishing, an infostealer log sold on a dark web market, or a SIM-swap that compromised the SMS second factor — and signs in as the legitimate user. The window between the first malicious login and the first irreversible action (a wire transfer, a crypto withdrawal, a marketplace payout) is usually 5 to 30 minutes. Detection latency, not detection accuracy, is what separates a 200 USD chargeback from an 8,000 USD loss.

Fraud analyst interviews at Stripe, Airbnb, DoorDash, and Uber lean on this exact pattern. You are handed a schema with login_events, email_changes, password_changes, payment_methods, and tfa_events, and asked to write the query that produces a per-user, per-login risk score within a fixed time window. The interviewer is checking three things: do you join the right tables on the right keys, do you choose a defensible scoring scheme instead of a vague threshold, and do you understand that some signals are far stronger than others. Get those three right and you pass.

The signals that matter

Not every anomaly is an ATO. Users buy new phones, travel, and forget their passwords. The trick is to weight signals by how unlikely they are in a benign session and how directly they map to the attacker's playbook. The attacker's playbook is short: get in, lock the owner out, monetize, leave. Each step has a database fingerprint, and the strongest signals are the ones that map to step two — locking the owner out.

A login from a new device, a new IP, and a new country in the same session is suspicious but not damning. A new device alone is benign — phones break. A new country alone is benign — people travel. The combination is rarer. Layer onto that an email change within ten minutes of the login, a password change in the same window, a new card added inside the hour, and a 2FA factor disabled in the first thirty minutes, and you are looking at behavior that legitimate users almost never produce. The 2FA disable is the single strongest signal in the stack: it has almost no benign explanation in a freshly-logged-in session and it is exactly what an attacker does before initiating the cashout.

Building the ATO score in SQL

The query below is what you write on the whiteboard. It assumes Postgres dialect — most fraud teams run on Snowflake, BigQuery, or Postgres, and the syntax differences are cosmetic. The structure is two CTEs (one for login enrichment, one for post-login actions joined to each login) and a final SELECT that produces an additive score per login event. Additive scoring is not the most sophisticated approach, but it is defensible in an interview and it is what most production rules engines use as a baseline before a model is trained on top.

WITH login_events_enriched AS (
    SELECT
        l.user_id,
        l.login_timestamp,
        l.device_id,
        l.ip,
        l.country,
        EXISTS (
            SELECT 1 FROM user_devices ud
            WHERE ud.user_id = l.user_id
              AND ud.device_id = l.device_id
              AND ud.first_seen_at < l.login_timestamp - INTERVAL '7 days'
        ) AS known_device,
        EXISTS (
            SELECT 1 FROM user_ips ui
            WHERE ui.user_id = l.user_id
              AND ui.ip = l.ip
        ) AS known_ip,
        EXISTS (
            SELECT 1 FROM user_countries uc
            WHERE uc.user_id = l.user_id
              AND uc.country = l.country
        ) AS known_country
    FROM login_events l
    WHERE l.login_timestamp >= NOW() - INTERVAL '24 hours'
      AND l.outcome = 'success'
),
post_login_actions AS (
    SELECT
        l.user_id,
        l.login_timestamp,
        EXISTS (
            SELECT 1 FROM email_changes ec
            WHERE ec.user_id = l.user_id
              AND ec.changed_at BETWEEN l.login_timestamp
                                    AND l.login_timestamp + INTERVAL '10 minutes'
        ) AS email_changed,
        EXISTS (
            SELECT 1 FROM password_changes pc
            WHERE pc.user_id = l.user_id
              AND pc.changed_at BETWEEN l.login_timestamp
                                    AND l.login_timestamp + INTERVAL '10 minutes'
        ) AS password_changed,
        EXISTS (
            SELECT 1 FROM payment_methods pm
            WHERE pm.user_id = l.user_id
              AND pm.added_at BETWEEN l.login_timestamp
                                  AND l.login_timestamp + INTERVAL '1 hour'
        ) AS new_card,
        EXISTS (
            SELECT 1 FROM tfa_events te
            WHERE te.user_id = l.user_id
              AND te.action = 'disabled'
              AND te.changed_at BETWEEN l.login_timestamp
                                    AND l.login_timestamp + INTERVAL '30 minutes'
        ) AS tfa_disabled
    FROM login_events_enriched l
)
SELECT
    le.user_id,
    le.login_timestamp,
    (CASE WHEN NOT le.known_device  THEN 20 ELSE 0 END) +
    (CASE WHEN NOT le.known_ip      THEN 15 ELSE 0 END) +
    (CASE WHEN NOT le.known_country THEN 10 ELSE 0 END) +
    (CASE WHEN pl.email_changed     THEN 30 ELSE 0 END) +
    (CASE WHEN pl.password_changed
          AND pl.email_changed      THEN 25 ELSE 0 END) +
    (CASE WHEN pl.new_card          THEN 20 ELSE 0 END) +
    (CASE WHEN pl.tfa_disabled      THEN 40 ELSE 0 END) AS ato_score
FROM login_events_enriched le
JOIN post_login_actions    pl USING (user_id, login_timestamp);

Read the scoring weights as a story. Twenty points for a new device is roughly the prior probability that a brand-new device on a session of an established user reflects fraud rather than a phone upgrade. Fifteen for the new IP reflects how noisy IPs are in the era of carrier-grade NAT and VPNs. The compound bonus for password-and-email change in the same window is there because each in isolation is sometimes legitimate, but the pair is almost never produced by an honest user. The 40 points on 2FA disable inside thirty minutes is the heaviest weight in the table because it is the single highest-precision signal you will find in a transactional system. A score of 50 or above is your action threshold — lock the session, freeze withdrawals, and start the recovery flow.

The 30-minute danger window

The cleanest variant of the ATO signature is not any single field but the rate of high-impact actions in a tight window. Attackers do not pause. They open the session, change the recovery email, change the password, disable the second factor, and pull a card-add or a withdrawal — and they do it in under five minutes because they know fraud teams batch their checks. The query below is the one you write second, after the scoring query, when the interviewer says: "now show me how you would catch the ones we missed."

SELECT
    user_id,
    COUNT(*) AS critical_actions_30m,
    MIN(event_at) AS first_action_at,
    MAX(event_at) AS last_action_at
FROM (
    SELECT user_id, changed_at AS event_at
        FROM email_changes
    UNION ALL
    SELECT user_id, changed_at FROM password_changes
    UNION ALL
    SELECT user_id, added_at   FROM payment_methods
    UNION ALL
    SELECT user_id, changed_at FROM tfa_events
        WHERE action = 'disabled'
) all_critical_actions
WHERE event_at >= NOW() - INTERVAL '30 minutes'
GROUP BY user_id
HAVING COUNT(*) >= 3
ORDER BY critical_actions_30m DESC;

Three or more critical actions inside a half-hour window is, in most consumer products, a near-certain ATO. The false-positive rate from this rule alone, at companies the size of Netflix or Notion, sits between 0.5% and 2% of flagged accounts. The remaining 98% are real attacks. That is the kind of precision you can act on without a human-in-the-loop review for every case.

Recovery flow and routing

A score is useless if it does not route to an action. The pattern most fraud teams converge on is a three-tier response: monitor, soft-confirm, and hard-block. Soft-confirm means an email to the previously-verified address with a one-click "yes that was me" button. Hard-block means force-logout of all sessions, freeze the account, and a phone callback. The SQL is trivial — the engineering work is the recovery flow on the product side, which has to refuse to let the attacker change the routing destination during the recovery itself.

SELECT
    user_id,
    login_timestamp,
    ato_score,
    CASE
        WHEN ato_score >= 60 THEN 'force_logout_and_callback'
        WHEN ato_score >= 30 THEN 'email_confirmation_to_old_address'
        ELSE                       'monitor'
    END AS recommended_action
FROM ato_scoring
WHERE ato_score >= 30
ORDER BY ato_score DESC, login_timestamp DESC;

The non-obvious rule that interviewers love to probe: the recovery email must go to the email address verified at least thirty days before the suspicious login, never to the email on file right now. If the attacker already changed the address, the email-on-file is the attacker's inbox. Junior candidates miss this almost every time.

Common pitfalls

The first trap is treating a new device as a sole signal worth acting on. Devices roll over constantly — phone upgrades, browser cache clears, app reinstalls, fresh laptops. A single new-device login per quarter is normal for a healthy user. Block on this alone and you generate enough false positives to drown the fraud team and frustrate paying customers. The fix is to require co-occurrence: new device plus new IP plus an action signal within the post-login window before any block.

The second trap is undercounting 2FA-disable events. Some teams give 2FA-off the same weight as a password change. They should not. A 2FA disable inside thirty minutes of a fresh login is one of the highest-precision adversarial signals in the entire authentication stack, and the right weight is roughly twice what most teams initially assign. If you give it a low weight, you will miss the attackers who skip the email change and go straight to the cashout — which is the pattern most professional ATO crews now use.

The third trap is a slow detection cadence. If your detection job runs hourly, you have already lost. Attackers know batch windows and time their work to fit inside one. Real fraud teams run the scoring on every login event in near real time, either through a streaming pipeline (Kafka into Flink or into the warehouse with a five-second flush) or with a row-level trigger that produces a score on insert. The SQL above is written for batch clarity, but in production it lives behind a streaming engine or a materialized view that refreshes on demand.

The fourth trap is blocking without notifying the legitimate user. A force-logout with no email back to the verified address simply locks the real owner out of their account at the same moment the attacker is also locked out — and the legitimate owner now thinks the platform is broken. The fix is to always pair a block with a notification to the previously-verified email and, for high-value accounts, a phone call within the first hour. The recovery flow is part of the detection product, not an afterthought.

The fifth trap is not feeding confirmed cases back into the weights. Every confirmed ATO is a labelled positive example. If you treat the weights as static numbers carved into a config file, you will be using last quarter's playbook against this quarter's attackers. Even without a full ML model, simple monthly re-weighting based on precision and recall on confirmed cases keeps the score honest. Teams that do this routinely outperform teams that ship a model and never retrain it.

Optimization tips

The bottleneck is almost always the EXISTS subqueries in the post-login CTE. Each one is a correlated lookup; on a large email_changes or tfa_events table they will be slow without indexes on (user_id, changed_at) and (user_id, added_at) respectively. Add a composite index on (user_id, event_timestamp) to every action table — this is the single biggest win and the one most fraud platforms forget until they are doing on-call.

For warehouses (Snowflake, BigQuery, Databricks), partition the action tables on the date of the event and cluster on user_id. The 24-hour and 30-minute windows are then served by a single partition rather than a full table scan, and clustered keys make the per-user join nearly free. Materialize the login_events_enriched CTE if your warehouse re-computes it on every read — most fraud dashboards refresh once per minute, and you do not want to repeat the enrichment join on every refresh.

If your action volume is in the millions of events per day, refactor the post-login CTE into a single union-all action stream joined to logins in one pass, rather than four separate EXISTS subqueries. The optimizer can hash-join the union once and reuse the result, which on Postgres is roughly four times faster than four separate semijoins on the same plan.

Related reading

• SQL window functions interview questions
• A/B testing peeking mistake
• Why are you leaving your job — interview answer

If you want to drill exactly this style of fraud analytics SQL question every day, NAILDD is launching with hundreds of trust-and-safety and risk-scoring problems built around real interview patterns.

FAQ

What is the typical ATO rate in a B2C SaaS or marketplace product?

In a healthy consumer SaaS without a financial component, you will see between 0.01% and 0.1% of accounts hit by an ATO attempt over a 12-month window. In a fintech, marketplace with payouts, or crypto product, the rate climbs to between 0.5% and 2% per year because the cashout value is so much higher. The rates also spike after a major credential dump — when an unrelated company is breached and the dump hits the dark web, credential-stuffing volume against every other site rises for the following 30 to 60 days. Fraud teams plan their on-call rotation around those windows.

Is 2FA the only defence that actually works?

It is by far the strongest single layer, but the form of 2FA matters enormously. SMS-based codes are vulnerable to SIM-swap attacks and have been compromised in every large-scale ATO wave of the past five years. TOTP apps such as Authy or Google Authenticator are much better. Phishing-resistant factors — WebAuthn passkeys or hardware security keys — are the only category that resists the realtime-phishing kits that proxy a real login session. If your product handles money, default new users into WebAuthn or a passkey, not SMS.

Why never send the recovery email to the current address on file?

Because the attacker may have already changed it. The whole point of the email change in the first ten minutes is to redirect recovery to an inbox the attacker controls. If your recovery flow sends to "the email on file" at the moment of recovery, you have handed the attacker the keys to the recovery itself. Always anchor recovery to an address verified at least 30 days before the suspicious event, and store that "stable recovery address" as a separate column from the editable contact email.

Can you auto-lock accounts on a very high score?

Yes, above roughly 80 points in the scoring scheme above, an auto-lock with immediate notification to the stable recovery address is industry standard. Below that threshold, the risk-versus-friction tradeoff tips the other way and you want either a step-up challenge (re-authenticate with the second factor) or a soft email confirmation. The single biggest source of customer complaints in trust-and-safety is over-blocking with no recovery path, so the threshold for fully automated blocks should be set where false-positive rates fall under 1%.

Should the legitimate customer always be notified?

Yes, and ideally before any disruptive action. The notification serves two purposes: it lets the real owner say "no, that was not me" and triggers a fast manual review, and it creates a clear audit trail for any later dispute. The email should never describe the suspected attacker's actions in detail (do not say "someone tried to change your email to attacker@example.com") because if the recipient turns out to be the attacker, you have just leaked your detection logic. A neutral message — "we detected unusual activity on your account; please confirm by clicking below" — is the right pattern.

How do you measure whether the detection is actually working?

Three metrics. Precision (of accounts flagged at score 50+, what fraction are confirmed ATOs after manual review) should sit above 90% in a mature system. Time-to-detect (from the malicious login to the first protective action) should sit under 60 seconds for high-score cases. Dollar loss per confirmed ATO is the business outcome; well-tuned systems hold this under 100 USD per case for retail products and under 2,000 USD for finance products, because the rapid-response flow catches the attack before the largest withdrawal completes. Report these three numbers every month, and the team will know whether weight changes and new signals are helping or hurting.