Home › Blog › SQL for AI Company Interviews

SQL for AI Company Interviews — What OpenAI and Anthropic Actually Ask

The surprise in an AI-lab data interview is how little of it is about AI. You get asked who retained, which accounts went quiet, what counts as a session, and which 10% of users drive most of the load. Those are product-analytics questions, and they resolve to four SQL patterns. This guide covers all four with queries you can run right now.

Why usage data changes the questions

An ecommerce analyst asks what sold. An AI-lab analyst asks what happened — and the underlying table is an event log where one person might appear four hundred times in an hour and then vanish for a week. That shape changes which SQL matters.

Three consequences show up in interviews over and over:

• Counting rows is almost never the answer. "How many active users" needs a definition of active before it needs a COUNT.
• Time is the primary axis. Not calendar time — time relative to each account's own start. That is what a cohort is.
• The distribution is brutally skewed. Averages lie when the top decile drives most of the volume, so percentile logic replaces AVG.

Pattern 1 — Cohort retention

The question sounds like "do people stick around?" The SQL job is to anchor every account to its own starting point, then measure activity relative to that anchor — not to the calendar.

WITH first_seen AS (
  SELECT customer_id, MIN(order_date) AS cohort_day
  FROM orders GROUP BY customer_id
),
activity AS (
  SELECT o.customer_id, f.cohort_day, o.order_date,
         CAST(julianday(o.order_date) - julianday(f.cohort_day) AS INT) / 7 AS week_offset
  FROM orders o
  JOIN first_seen f ON f.customer_id = o.customer_id
)
SELECT cohort_day, week_offset, COUNT(DISTINCT customer_id) AS users
FROM activity
GROUP BY cohort_day, week_offset
ORDER BY cohort_day, week_offset

Two things make this an interview question rather than a syntax exercise. First, week_offset is computed per account, so week 1 means a different calendar week for everybody — that is the whole point. Second, COUNT(DISTINCT customer_id) and not COUNT(*): retention counts people, and someone who fired forty events on day three is still one retained person.

Pattern 2 — Sessionization

Raw events are not sessions. You create sessions by declaring a gap rule: if two consecutive events from the same account are further apart than the threshold, the second starts a new session. It is LAG plus a running sum, and it is asked constantly.

WITH ordered AS (
  SELECT customer_id, order_id, order_date,
         LAG(order_date) OVER (
           PARTITION BY customer_id ORDER BY order_date, order_id
         ) AS prev_date
  FROM orders
),
flagged AS (
  SELECT *,
    CASE WHEN prev_date IS NULL
           OR julianday(order_date) - julianday(prev_date) > 7
         THEN 1 ELSE 0 END AS is_new_session
  FROM ordered
)
SELECT customer_id, order_id, order_date,
       SUM(is_new_session) OVER (
         PARTITION BY customer_id ORDER BY order_date, order_id
       ) AS session_id
FROM flagged
ORDER BY customer_id, order_date

The mechanism worth being able to explain: a running SUM over a 0/1 flag turns "this row starts something new" into a stable group id. Every row inside the same session shares the number, so you can group by it afterwards. Change the 7 to any threshold your product justifies — 30 minutes for a web app, days for an API where bursts are normal.

Practice sessionization challenges →

Pattern 3 — Usage percentiles

API usage is a power law. The mean is a fiction — it sits above almost every account and below the handful that dominate. Interviewers ask for percentile splits to see whether you know that.

SELECT name, salary,
       NTILE(10) OVER (ORDER BY salary) AS decile
FROM employees
ORDER BY salary DESC, name

NTILE(10) splits the ordered rows into ten buckets of near-equal size — decile 10 is the heaviest. The natural follow-up, and the one that separates candidates: what share of total volume does the top decile account for? That is a windowed SUM over the whole table divided into a windowed SUM over the bucket, and being able to reach for it without hesitating reads as fluency.

Pattern 4 — Event dedup

Event pipelines deliver at least once, which means duplicates. Aggregating before deduping produces numbers that are quietly wrong — the failure mode most likely to reach a dashboard unnoticed.

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY order_id ORDER BY order_date
    ) AS rn
  FROM orders
)
SELECT * FROM ranked WHERE rn = 1

ROW_NUMBER rather than RANK, deliberately: ties must collapse to exactly one row. RANK would keep both duplicates at position 1 and defeat the purpose. That distinction is a common follow-up question.

Four mistakes that end interviews

1. Counting events when the question asks about people. COUNT(*) where COUNT(DISTINCT account_id) was meant. Silent, plausible, wrong.
2. Anchoring retention to the calendar. Cohorts are relative to each account's own start. Group by calendar week and you have measured seasonality, not retention.
3. Reporting an average of a skewed distribution. If the top decile drives most of the volume, the mean describes nobody. Reach for percentiles unprompted.
4. Aggregating before deduping. Duplicate events inflate every downstream number, and nothing errors. Dedup first, then aggregate.

Practice these patterns

Reading them is not the same as producing them cold in a shared editor. Each of these has a company-tagged practice set that runs in the browser, with an AI tutor that explains why a query is wrong instead of handing over the answer.