SQL Interview Questions 2026 — The Most Underrated Skill in AI Engineering

Stripe's data engineering interview has a round called "SQL Bug Squash." You receive four production queries that are subtly broken — wrong join type, off-by-one in a window frame, NULL comparison that silently drops rows, currency summed without conversion — and you have 45 minutes to find the bugs, explain why each one produces wrong results, and fix them surgically. No hints. No Google. Writing correct SQL isn't the bar anymore. Reading it, diagnosing it, and reasoning about edge cases is.

That shift reflects how companies actually use SQL in 2026. The SQL interview questions that separate candidates aren't testing whether you've memorized ROW_NUMBER() syntax. They're testing whether you understand why status <> 'paid' silently drops NULL rows, why adding a WHERE clause after a LEFT JOIN converts it into an INNER JOIN, and why the index you added is being ignored by the query planner.

This guide covers 20+ real SQL questions across the topics that actually show up at Flipkart, Swiggy, Razorpay, and Stripe — with full answers, realistic schemas, and the scoring rubric interviewers use but rarely state out loud.

What Interviewers Score Beyond Correctness

Most SQL prep materials focus entirely on getting the right answer. But in a senior data or ML engineering interview, the correct query gets you 60% of the available signal. The other 40% comes from four dimensions that interviewers are actively scoring:

NULL awareness. Does your query handle NULL values correctly, or does it silently produce wrong answers when NULL appears in the data? The single most common interview failure — candidates write a query that's technically executable and produces plausible-looking output, but is wrong because of NULL semantics. We cover all the traps in their own section below.

Query efficiency. Can you recognize when your query will do a full table scan instead of an index seek? Can you spot a cross join masquerading as a WHERE clause? Can you explain what the execution plan would look like? At companies like Flipkart with hundreds of millions of order rows, a query that runs in 2 seconds on 1M rows and 2 hours on 500M rows is not production-ready.

Edge case reasoning. What happens when the table is empty? When every row matches? When the partition has one row? Strong candidates state these assumptions out loud — "I'm assuming merchant_id is never NULL here; if it can be, I'd add COALESCE to the GROUP BY." That proactive edge case awareness is a direct signal of production experience.

Communication under pressure. Interviewers want to hear your reasoning as you write. Candidates who silently produce perfect SQL and say nothing are harder to evaluate than candidates who think out loud, catch their own mistakes, and explain why they chose one approach over another.

SQL Interview Questions: Quick Reference by Topic

The table below maps the 20+ questions in this guide to the skill they test. Use it to identify gaps before your interview.

| # | Question | Company | Primary Skill | Difficulty | |---|----------|---------|---------------|------------| | 1 | Rolling 7-day user retention | Flipkart | Window functions + date math | Hard | | 2 | Seller volume drop >30% WoW | Flipkart | LAG + percentage calculation | Medium | | 3 | Second-highest order value per city, handle ties | Flipkart | DENSE_RANK + partitioning | Medium | | 4 | Payment Success Rate by bank, flag outages | Razorpay | Conditional aggregation + HAVING | Hard | | 5 | Running total of payments per merchant | Razorpay | SUM window function | Easy | | 6 | Duplicate transaction detection | Stripe | LEAD/LAG + self-join | Hard | | 7 | Debug: LEFT JOIN silently dropping rows | Stripe | NULL + JOIN semantics | Hard | | 8 | Restaurant delivery time improvement slope | Swiggy | Rolling AVG + CTEs | Hard | | 9 | Top 5 customers by revenue in last 30 days | Swiggy | Aggregation + date filtering | Easy | | 10 | Org hierarchy traversal | Generic | Recursive CTE | Hard | | 11 | Find users active in Jan + Feb but not March | Generic | INTERSECT / EXCEPT | Medium | | 12 | Cohort retention by signup week | Generic | Multi-step CTE + window | Hard | | 13 | Point-in-time feature join (no data leakage) | ML role | Temporal join | Hard | | 14 | NOT IN with NULL subquery trap | Generic | NULL trap | Medium | | 15 | Index-ignoring predicate: function on column | Generic | SARGability | Medium | | 16 | BETWEEN with timestamps: off-by-one | Generic | Timestamp boundary | Medium | | 17 | Hash vs nested loop join choice | Generic | Execution plan | Hard | | 18 | Parameter sniffing: fast for one tenant, slow for another | Generic | Query planner | Hard | | 19 | Second-degree graph traversal | Generic | Recursive CTE | Hard | | 20 | FULL OUTER JOIN key selection trap | Generic | JOIN semantics | Medium |

Window Functions: The Most Tested Advanced Topic

Window functions appear in virtually every senior data role interview. The failure pattern is consistent: candidates know they exist, know the name of ROW_NUMBER, but blank on ROWS BETWEEN syntax under pressure, or mix up when to use RANK vs DENSE_RANK and produce wrong answers for tie-handling questions.

The mental model that fixes this: window functions compute a result for each row using a set of related rows, without collapsing the result set. GROUP BY collapses. Window functions don't. That distinction determines when to use each.

Q1 (Flipkart): Find the rolling 7-day retention of users — for each day, what percentage of users who were active 7 days ago are active today?

Schema:

-- user_activity(user_id BIGINT, activity_date DATE, session_count INT)
-- One row per user per day they were active

Answer:

WITH daily_active AS (
  SELECT
    activity_date,
    user_id
  FROM user_activity
  GROUP BY activity_date, user_id
),
retention_base AS (
  SELECT
    t.activity_date AS current_date,
    t.user_id,
    -- Check if this user was also active 7 days ago
    CASE WHEN prev.user_id IS NOT NULL THEN 1 ELSE 0 END AS retained
  FROM daily_active t
  LEFT JOIN daily_active prev
    ON t.user_id = prev.user_id
    AND prev.activity_date = t.activity_date - INTERVAL '7 days'
)
SELECT
  current_date,
  COUNT(*) AS active_users,
  SUM(retained) AS retained_from_7d_ago,
  ROUND(100.0 * SUM(retained) / COUNT(*), 2) AS retention_rate_pct
FROM retention_base
GROUP BY current_date
ORDER BY current_date;

What interviewers check: Did you use a LEFT JOIN instead of INNER JOIN? (INNER JOIN drops users with no 7-day-ago record, undercounting the denominator.) Did you handle the INTERVAL syntax correctly? Did you mention the implicit assumption that activity_date is unique per user per day?

Q2 (Flipkart): Find sellers whose order volume dropped more than 30% week over week.

WITH weekly_orders AS (
  SELECT
    seller_id,
    DATE_TRUNC('week', order_date) AS order_week,
    COUNT(*) AS order_count
  FROM orders
  WHERE order_status = 'completed'
  GROUP BY seller_id, DATE_TRUNC('week', order_date)
),
with_prev_week AS (
  SELECT
    seller_id,
    order_week,
    order_count,
    LAG(order_count, 1) OVER (
      PARTITION BY seller_id
      ORDER BY order_week
    ) AS prev_week_count
  FROM weekly_orders
)
SELECT
  seller_id,
  order_week,
  order_count,
  prev_week_count,
  ROUND(100.0 * (order_count - prev_week_count) / prev_week_count, 1) AS pct_change
FROM with_prev_week
WHERE prev_week_count IS NOT NULL  -- exclude the first week with no prior data
  AND order_count < prev_week_count * 0.70  -- more than 30% drop
ORDER BY pct_change ASC;

The NULL guard on prev_week_count is what separates a correct answer from an almost-correct one. Without it, the first week for each seller (which has no prior week) would either cause a division-by-zero error or silently produce NULL results that the HAVING clause drops.

Q3 (Flipkart): Return the second-highest order value per city, handling ties correctly.

WITH ranked_orders AS (
  SELECT
    city,
    order_id,
    order_value,
    DENSE_RANK() OVER (
      PARTITION BY city
      ORDER BY order_value DESC
    ) AS value_rank
  FROM orders
  WHERE order_status = 'completed'
)
SELECT city, order_value, order_id
FROM ranked_orders
WHERE value_rank = 2
ORDER BY city;

The choice of DENSE_RANK over RANK is the point. With RANK, if three orders tie for first place, they all get rank 1 and rank 2 is skipped — there would be no second-highest result at all. With DENSE_RANK, those three orders get rank 1 and the next distinct value gets rank 2. If you use RANK() on a tie question, your answer is wrong even if the query runs.

Rolling calculations with ROWS BETWEEN:

The syntax most candidates blank on:

SELECT
  order_date,
  daily_revenue,
  AVG(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7day_avg,
  SUM(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM daily_revenue_summary
ORDER BY order_date;

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW means: include the current row plus the 6 rows before it — that's 7 rows total for a 7-day rolling average. UNBOUNDED PRECEDING means all rows from the beginning of the partition. Know these cold — they appear in every company that does time-series analytics.

CTEs, Recursive CTEs, and When to Use Each

CTEs (Common Table Expressions) exist for two reasons: readability and recursion. Interviewers use CTEs to test whether you can decompose a complex problem into legible named steps — which directly predicts whether you can write maintainable production SQL.

Q10: Traverse an org hierarchy to find all direct and indirect reports under a manager.

Schema: employees(employee_id BIGINT, name TEXT, manager_id BIGINT, department TEXT, level INT)

WITH RECURSIVE org_tree AS (
  -- Base case (anchor): start with the target manager
  SELECT
    employee_id,
    name,
    manager_id,
    department,
    0 AS depth
  FROM employees
  WHERE employee_id = 2047  -- Engineering VP
  
  UNION ALL
  
  -- Recursive case: add direct reports of employees already in the tree
  SELECT
    e.employee_id,
    e.name,
    e.manager_id,
    e.department,
    ot.depth + 1
  FROM employees e
  INNER JOIN org_tree ot ON e.manager_id = ot.employee_id
  WHERE ot.depth < 8  -- cycle guard: prevents infinite recursion on bad data
)
SELECT
  employee_id,
  name,
  department,
  depth AS levels_below_vp
FROM org_tree
ORDER BY depth, name;

The cycle guard (WHERE ot.depth < 8) is the production detail that distinguishes a strong answer. A database with a circular manager reference — employee A is manager of B, B is manager of A — will recurse infinitely without it. State this assumption: "I'm adding a depth limit as a cycle guard; in production I'd also add a path array to detect actual cycles."

Q11: Find users active in January and February but not March.

-- Clean approach using set operations
SELECT DISTINCT user_id FROM user_activity
WHERE DATE_TRUNC('month', activity_date) = '2026-01-01'

INTERSECT

SELECT DISTINCT user_id FROM user_activity
WHERE DATE_TRUNC('month', activity_date) = '2026-02-01'

EXCEPT

SELECT DISTINCT user_id FROM user_activity
WHERE DATE_TRUNC('month', activity_date) = '2026-03-01';

INTERSECT returns rows present in both result sets. EXCEPT removes rows present in the second set. This is cleaner than a multi-condition GROUP BY + HAVING and easier to extend. The alternative — three CTEs with a LEFT JOIN anti-pattern — is also acceptable and often more debuggable.

Q19: Find all users connected within 2 degrees (graph traversal).

Schema: connections(from_user BIGINT, to_user BIGINT, connected_at TIMESTAMP)

WITH RECURSIVE network AS (
  -- Base: direct connections of user 10491
  SELECT
    to_user AS connected_user,
    1 AS degree
  FROM connections
  WHERE from_user = 10491
    AND to_user <> 10491  -- exclude self-loop
  
  UNION
  
  -- Recursive: connections of connections (2nd degree)
  SELECT
    c.to_user AS connected_user,
    n.degree + 1
  FROM connections c
  INNER JOIN network n ON c.from_user = n.connected_user
  WHERE n.degree < 2  -- stop at 2 degrees
    AND c.to_user <> 10491  -- exclude the origin user
)
SELECT
  connected_user,
  MIN(degree) AS shortest_degree
FROM network
GROUP BY connected_user
ORDER BY shortest_degree, connected_user;

Using UNION (not UNION ALL) here deduplicates — a person connected at both 1 and 2 degrees (mutual friend of origin and also direct connection) gets their minimum degree. This is a detail worth stating.

Real Questions from Razorpay, Swiggy, and Stripe

Razorpay: Financial Transaction Analysis

Razorpay's SQL assessment runs three challenges focused on payment data — this is the domain. Expect schema involving payments, merchants, and refunds.

Q4: Monitor Payment Success Rate (PSR) by bank, flag outages where PSR drops below 75%.

Schema: payments(payment_id BIGINT, merchant_id BIGINT, bank_code TEXT, payment_method TEXT, status TEXT, created_at TIMESTAMP)

SELECT
  bank_code,
  COUNT(*) AS total_attempts,
  SUM(CASE WHEN status = 'captured' THEN 1 ELSE 0 END) AS successful_payments,
  ROUND(
    100.0 * SUM(CASE WHEN status = 'captured' THEN 1 ELSE 0 END) / COUNT(*),
    2
  ) AS success_rate_pct,
  CASE
    WHEN COUNT(*) < 100 THEN 'insufficient_sample'
    WHEN SUM(CASE WHEN status = 'captured' THEN 1 ELSE 0 END) * 1.0 / COUNT(*) < 0.75
      THEN 'outage_alert'
    ELSE 'healthy'
  END AS bank_status
FROM payments
WHERE payment_method = 'UPI'
  AND created_at >= NOW() - INTERVAL '1 hour'  -- real-time monitoring window
GROUP BY bank_code
HAVING COUNT(*) >= 10  -- minimum volume to even appear in the result
ORDER BY success_rate_pct ASC;

The sample-size threshold is the non-obvious requirement interviewers watch for. A bank with 3 transactions, 2 of which failed, shows a 33% success rate — not an outage, just low volume. Missing this produces false-positive alerts on low-volume bank corridors. The HAVING COUNT(*) >= 10 pre-filter keeps noisy small-sample banks out of the output entirely, while the 'insufficient_sample' case handles the intermediate range.

Q5: Running total of payments per merchant per day.

SELECT
  merchant_id,
  payment_date,
  daily_amount,
  SUM(daily_amount) OVER (
    PARTITION BY merchant_id
    ORDER BY payment_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,
  SUM(daily_amount) OVER (
    PARTITION BY merchant_id, DATE_TRUNC('month', payment_date)
    ORDER BY payment_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS monthly_running_total
FROM (
  SELECT
    merchant_id,
    DATE(created_at) AS payment_date,
    SUM(amount) AS daily_amount
  FROM payments
  WHERE status = 'captured'
  GROUP BY merchant_id, DATE(created_at)
) daily_aggregates
ORDER BY merchant_id, payment_date;

Stripe: SQL Bug Squash Patterns

Stripe's "SQL Bug Squash" round doesn't test query writing — it tests SQL reading and diagnosis. You're handed broken queries and must identify the exact bug, explain the wrong output it produces, and fix it. Here are the canonical bug categories with examples:

Q6: Detect duplicate transactions (same user, same amount, within 1 minute).

This is a common Stripe-style question — detecting fraud or double-charges:

WITH ordered_payments AS (
  SELECT
    payment_id,
    user_id,
    amount,
    currency,
    created_at,
    LAG(created_at) OVER (
      PARTITION BY user_id, amount, currency
      ORDER BY created_at
    ) AS prev_payment_at
  FROM payments
  WHERE status IN ('captured', 'pending')
)
SELECT
  payment_id,
  user_id,
  amount,
  currency,
  created_at,
  prev_payment_at,
  EXTRACT(EPOCH FROM (created_at - prev_payment_at)) AS seconds_gap
FROM ordered_payments
WHERE prev_payment_at IS NOT NULL
  AND EXTRACT(EPOCH FROM (created_at - prev_payment_at)) <= 60
ORDER BY user_id, created_at;

Note the currency in the PARTITION BY. Without it, a user charging $10 USD and ₹10 INR within a minute would be flagged as a duplicate — which the Stripe engineers explicitly test for. Currency mismatch in financial SQL is a Stripe-specific gotcha that separates candidates who've worked on payments from those who haven't.

Q7 (Stripe Bug Squash): Debug this broken LEFT JOIN.

-- BUGGY: This query is supposed to find all merchants and their total refunds
-- (including merchants with zero refunds), but it returns only merchants who have refunds.
-- Find the bug.

SELECT
  m.merchant_id,
  m.business_name,
  SUM(r.amount) AS total_refunded
FROM merchants m
LEFT JOIN refunds r ON m.merchant_id = r.merchant_id
WHERE r.status = 'processed'  -- THIS IS THE BUG
GROUP BY m.merchant_id, m.business_name;

-- FIXED:
SELECT
  m.merchant_id,
  m.business_name,
  COALESCE(SUM(r.amount), 0) AS total_refunded
FROM merchants m
LEFT JOIN refunds r
  ON m.merchant_id = r.merchant_id
  AND r.status = 'processed'  -- Filter moved into ON clause
GROUP BY m.merchant_id, m.business_name;

The bug: WHERE r.status = 'processed' runs after the LEFT JOIN and eliminates merchants with no refunds (those rows have NULL for all refund columns, so NULL = 'processed' evaluates to UNKNOWN, which fails the WHERE filter). Moving the condition into the ON clause keeps it as a join condition, preserving the unmatched merchant rows. Also add COALESCE(SUM(r.amount), 0) — merchants with no refunds will have NULL from the LEFT JOIN, and SUM(NULL) returns NULL rather than 0.

Swiggy: Delivery and Restaurant Analytics

Q8: Find the restaurant with the highest average delivery time improvement over 6 months.

Schema: deliveries(delivery_id BIGINT, restaurant_id BIGINT, delivery_time_minutes INT, delivered_at TIMESTAMP, city TEXT)

WITH monthly_avg AS (
  SELECT
    restaurant_id,
    DATE_TRUNC('month', delivered_at) AS month,
    AVG(delivery_time_minutes) AS avg_delivery_time,
    COUNT(*) AS delivery_count
  FROM deliveries
  WHERE delivered_at >= NOW() - INTERVAL '6 months'
    AND delivery_time_minutes IS NOT NULL  -- exclude cancelled or missing records
  GROUP BY restaurant_id, DATE_TRUNC('month', delivered_at)
  HAVING COUNT(*) >= 20  -- minimum volume for statistical reliability
),
with_prev_month AS (
  SELECT
    restaurant_id,
    month,
    avg_delivery_time,
    LAG(avg_delivery_time) OVER (
      PARTITION BY restaurant_id
      ORDER BY month
    ) AS prev_month_avg
  FROM monthly_avg
),
improvements AS (
  SELECT
    restaurant_id,
    AVG(prev_month_avg - avg_delivery_time) AS avg_monthly_improvement_minutes
  FROM with_prev_month
  WHERE prev_month_avg IS NOT NULL
  GROUP BY restaurant_id
  HAVING COUNT(*) >= 3  -- need at least 3 months of month-over-month data
)
SELECT
  i.restaurant_id,
  AVG(d.delivery_time_minutes) AS current_avg_delivery_time,
  i.avg_monthly_improvement_minutes
FROM improvements i
JOIN deliveries d ON i.restaurant_id = d.restaurant_id
  AND d.delivered_at >= NOW() - INTERVAL '30 days'
GROUP BY i.restaurant_id, i.avg_monthly_improvement_minutes
ORDER BY avg_monthly_improvement_minutes DESC
LIMIT 1;

This query is intentionally complex — it demonstrates that you can decompose a multi-step analytical problem into named CTE stages. Each CTE has a clear responsibility. Interviewers at Swiggy are evaluating whether you would write this as a single nested query (hard to maintain) or as a pipeline of named stages (production quality).

Q9: Top 5 customers by revenue in the last 30 days.

SELECT
  c.customer_id,
  c.name,
  c.city,
  SUM(o.order_value) AS total_revenue_30d,
  COUNT(o.order_id) AS order_count,
  ROUND(SUM(o.order_value) / COUNT(o.order_id), 2) AS avg_order_value
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_status = 'delivered'
  AND o.delivered_at >= NOW() - INTERVAL '30 days'
GROUP BY c.customer_id, c.name, c.city
ORDER BY total_revenue_30d DESC
LIMIT 5;

This is a "warm-up" question — if you take more than 3 minutes, you're signaling a depth issue. What makes this interview-ready vs amateur: using delivered_at for the filter (not created_at), including order_count and avg_order_value (shows you're thinking about the analyst's actual needs), and having the JOIN on customer_id rather than doing a subquery.

The NULL Traps That Kill Otherwise Correct Queries

NULL handling is the highest-signal section of any SQL interview. A candidate who writes technically correct logic but handles NULL incorrectly will produce wrong answers in production. These are the traps that appear most often:

Trap 1: NOT IN with a NULL in the subquery.

-- BROKEN: Find customers not in the fraud_list
-- If fraud_list contains even one NULL, this returns zero rows
SELECT customer_id FROM customers
WHERE customer_id NOT IN (
  SELECT customer_id FROM fraud_list
);

-- CORRECT: Use NOT EXISTS
SELECT c.customer_id FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM fraud_list f
  WHERE f.customer_id = c.customer_id
);

Why it breaks: NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL. The third condition is UNKNOWN for every possible x, making the entire AND expression UNKNOWN, which WHERE treats as FALSE. Every row is eliminated. The bug is invisible — the query runs, returns results (zero rows), and doesn't error.

Trap 2: status <> 'paid' silently drops NULL rows.

-- BROKEN: Intended to find all transactions that aren't paid
SELECT * FROM transactions WHERE status <> 'paid';
-- Rows where status IS NULL are silently excluded

-- CORRECT:
SELECT * FROM transactions
WHERE status <> 'paid' OR status IS NULL;

Trap 3: COUNT(column) vs COUNT(*).

SELECT
  merchant_id,
  COUNT(*) AS total_transactions,          -- counts every row
  COUNT(amount) AS transactions_with_amount, -- excludes NULL amounts
  AVG(amount) AS avg_amount                  -- also excludes NULL amounts
FROM payments
GROUP BY merchant_id;

If 500 of 1000 rows have NULL amounts, COUNT(*) returns 1000 but COUNT(amount) returns 500 and AVG(amount) averages only the 500 non-NULL rows. This matters when you're comparing SUM(amount) / COUNT(*) (which divides by all rows, treating NULLs as zeros) vs AVG(amount) (which divides only by non-NULL rows). In a financial context, this difference is material.

Trap 4: BETWEEN with timestamps.

-- BROKEN: Misses the last second of January 31
SELECT * FROM events
WHERE event_time BETWEEN '2026-01-01' AND '2026-01-31';

-- CORRECT: Half-open interval
SELECT * FROM events
WHERE event_time >= '2026-01-01'
  AND event_time < '2026-02-01';

BETWEEN '2026-01-01' AND '2026-01-31' on a TIMESTAMP column includes rows up to 2026-01-31 00:00:00 — it misses everything from 2026-01-31 00:00:01 onward. Use half-open ranges for timestamp windows.

Trap 5: FULL OUTER JOIN key selection.

-- BROKEN: Loses rows that only exist in payments (where orders.order_id is NULL)
SELECT
  o.order_id,  -- NULL for payment-only rows!
  p.payment_id,
  o.order_value,
  p.amount
FROM orders o
FULL OUTER JOIN payments p ON o.order_id = p.order_id;

-- CORRECT: Use COALESCE to get the non-NULL id from either side
SELECT
  COALESCE(o.order_id, p.order_id) AS order_id,
  p.payment_id,
  o.order_value,
  p.amount
FROM orders o
FULL OUTER JOIN payments p ON o.order_id = p.order_id;

Query Optimization and Execution Plans

Senior engineers are expected to think about performance, not just correctness. These questions show up in staff-level interviews and anywhere that runs queries over hundreds of millions of rows.

Q15: Why is this query ignoring the index on order_date?

-- BROKEN: Index on order_date is ignored due to function wrapping
SELECT * FROM orders
WHERE YEAR(order_date) = 2026;

-- CORRECT: SARGable predicate — database can use the index
SELECT * FROM orders
WHERE order_date >= '2026-01-01'
  AND order_date < '2027-01-01';

This is SARGability (Search ARGument Able). When you wrap an indexed column in a function (YEAR(), LOWER(), CAST(), date truncation functions), the database can no longer use the index to seek — it must scan the entire table, evaluate the function for every row, and filter. Rewrite predicates to apply functions to the constant, not the column. In a 500M-row orders table at Flipkart, this distinction is the difference between a 50ms query and a full table scan that takes 3 minutes.

Q17: When does the query planner choose hash join vs nested loop join?

This is asked at senior and staff level. The three physical join algorithms and when each wins:

| Algorithm | Time complexity | When planner chooses it | Memory requirement | |-----------|----------------|--------------------------|---------------------| | Nested Loop | O(M × N) with index: O(M × log N) | Outer table is small, inner has index on join key | Low — no hash table needed | | Hash Join | O(M + N) | Large equi-join, no pre-existing sort order | High — smaller table must fit in work_mem | | Merge Join | O(M log M + N log N) for sort + O(M + N) | Both sides already sorted or have sorted indexes, equi-join | Medium — needs sorted data |

The practitioner detail: hash join is usually the right choice for large analytical joins (> 1M rows, equi-condition). But if work_mem is too small to hold the hash table in memory, the planner spills to disk and performance degrades sharply. A query that's fast in a dev environment (small dataset) and slow in production (large dataset + same work_mem setting) is often a hash join spilling to disk. The fix is either to increase work_mem for that session or to pre-sort data and let merge join take over.

Q18: A query runs in 200ms for most tenants but times out for one.

This is parameter sniffing (SQL Server) or plan cache issues (PostgreSQL). The planner compiles a query plan based on statistics at the time of first execution. If the query was first compiled for a tenant with 100 orders, the plan is optimized for small data. A tenant with 50 million orders gets that same cached plan — which uses a nested loop join that's catastrophic at that scale.

-- PostgreSQL: force re-estimation for this query
-- (Do not run in production without testing — use Query Store/plan hints instead)

-- Option 1: Use EXPLAIN (ANALYZE, BUFFERS) to see actual vs estimated rows
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.order_id, p.payment_id
FROM orders o
JOIN payments p ON o.order_id = p.order_id
WHERE o.merchant_id = 98204;

-- If you see a large discrepancy between "rows=X" (estimated) and "actual rows=Y",
-- the statistics are stale. Run:
ANALYZE orders;
ANALYZE payments;

In an interview, the correct answer is: "(1) check EXPLAIN ANALYZE for estimated vs actual row counts, (2) if there's a big discrepancy, statistics are stale — run ANALYZE, (3) if the plan is correctly estimated but wrong for large tenants, consider a partial index on merchant_id for high-volume merchants."

SQL Patterns for ML Engineering Roles

ML engineering interviews add a layer of domain-specific SQL that most prep materials don't cover.

Q12: Cohort retention — what percentage of users from each signup week are still active 4 weeks later?

WITH signup_cohorts AS (
  SELECT
    user_id,
    DATE_TRUNC('week', signup_date) AS cohort_week
  FROM users
  WHERE signup_date >= '2026-01-01'
),
weekly_sessions AS (
  SELECT
    user_id,
    DATE_TRUNC('week', session_start) AS session_week
  FROM sessions
  GROUP BY user_id, DATE_TRUNC('week', session_start)
),
cohort_activity AS (
  SELECT
    c.cohort_week,
    c.user_id,
    s.session_week,
    -- Calculate weeks since signup as an integer
    (EXTRACT(EPOCH FROM (s.session_week - c.cohort_week)) / 604800)::INT AS weeks_since_signup
  FROM signup_cohorts c
  LEFT JOIN weekly_sessions s ON c.user_id = s.user_id
    AND s.session_week >= c.cohort_week
)
SELECT
  cohort_week,
  weeks_since_signup,
  COUNT(DISTINCT user_id) AS active_users,
  FIRST_VALUE(COUNT(DISTINCT user_id)) OVER (
    PARTITION BY cohort_week ORDER BY weeks_since_signup
  ) AS cohort_size,
  ROUND(
    100.0 * COUNT(DISTINCT user_id) /
    FIRST_VALUE(COUNT(DISTINCT user_id)) OVER (
      PARTITION BY cohort_week ORDER BY weeks_since_signup
    ),
    1
  ) AS retention_pct
FROM cohort_activity
WHERE weeks_since_signup IS NOT NULL
GROUP BY cohort_week, weeks_since_signup
ORDER BY cohort_week, weeks_since_signup;

Q13: Point-in-time correct feature join — no data leakage.

This is the ML-specific SQL question that almost nobody outside of feature engineering has seen. The problem: when joining features to labels for a training dataset, you must ensure every feature value was available at or before the label timestamp. Using future feature values introduces data leakage.

-- Schema:
-- churn_labels(user_id BIGINT, label_timestamp TIMESTAMP, churned BOOL)
-- user_features(user_id BIGINT, feature_timestamp TIMESTAMP,
--               login_count_7d INT, purchase_count_30d INT, support_tickets_open INT)

SELECT
  l.user_id,
  l.label_timestamp,
  l.churned,
  f.login_count_7d,
  f.purchase_count_30d,
  f.support_tickets_open,
  f.feature_timestamp AS features_as_of
FROM churn_labels l
LEFT JOIN LATERAL (
  -- For each label, find the most recent feature snapshot that predates the label
  SELECT *
  FROM user_features uf
  WHERE uf.user_id = l.user_id
    AND uf.feature_timestamp <= l.label_timestamp  -- critical: only past data
  ORDER BY uf.feature_timestamp DESC
  LIMIT 1
) f ON true
WHERE f.user_id IS NOT NULL;  -- exclude users with no feature history at all

The LATERAL join (PostgreSQL) or CROSS APPLY (SQL Server) allows the subquery to reference the outer row — enabling the "most recent feature snapshot before this specific label timestamp" lookup. Without LATERAL, you'd need a correlated subquery in the SELECT clause (which is legal but slower) or a window function with complex date logic.

If a candidate knows this pattern, it signals they've actually worked on training data pipelines. It's the highest-signal SQL question for ML engineering roles.

Joins at Scale: What Actually Goes Wrong

Joining tables with hundreds of millions of rows exposes failure modes that don't appear in small-data interviews. These are the production patterns:

Exploding cardinality from many-to-many joins.

-- orders(order_id, customer_id, order_date) — 200M rows
-- order_tags(order_id, tag) — 1.4 billion rows (avg 7 tags per order)
-- Joining these with GROUP BY without filtering first is catastrophic

-- WRONG: join everything, then aggregate
SELECT
  DATE_TRUNC('week', o.order_date) AS week,
  ot.tag,
  COUNT(DISTINCT o.order_id) AS tagged_order_count
FROM orders o
JOIN order_tags ot ON o.order_id = ot.order_id
GROUP BY 1, 2;
-- This materializes 1.4B rows before aggregating

-- BETTER: pre-aggregate on the large side first
WITH tag_order_counts AS (
  SELECT order_id, tag
  FROM order_tags
  WHERE tag IN ('high_value', 'return_risk', 'new_customer')  -- push predicates down
),
weekly_aggregates AS (
  SELECT
    DATE_TRUNC('week', o.order_date) AS week,
    t.tag,
    COUNT(DISTINCT o.order_id) AS tagged_order_count
  FROM orders o
  JOIN tag_order_counts t ON o.order_id = t.order_id
  GROUP BY 1, 2
)
SELECT * FROM weekly_aggregates ORDER BY week, tag;

Skewed joins — one key dominates the data.

A Flipkart catalog table joined with an order table on category_id, where "Electronics" has 40% of all orders, creates a data skew problem. In distributed SQL (BigQuery, Spark SQL), all "Electronics" rows end up on one executor node, which becomes the bottleneck. The pattern: add a salting column to distribute the skewed key, then sum the partial results.

-- BigQuery / distributed SQL: salt the join key for skew handling
WITH salted_orders AS (
  SELECT
    order_id,
    category_id,
    order_value,
    -- Add a random salt 0–9 to distribute skewed keys
    CONCAT(CAST(category_id AS STRING), '_', CAST(FLOOR(RAND() * 10) AS INT64)) AS salted_key
  FROM orders
),
salted_categories AS (
  SELECT
    c.category_id,
    c.category_name,
    CONCAT(CAST(c.category_id AS STRING), '_', salt_value) AS salted_key
  FROM categories c
  CROSS JOIN UNNEST(GENERATE_ARRAY(0, 9)) AS salt_value
)
SELECT
  sc.category_name,
  SUM(so.order_value) AS total_revenue
FROM salted_orders so
JOIN salted_categories sc ON so.salted_key = sc.salted_key
GROUP BY sc.category_name;

How to Build SQL Fluency Before the Interview

The gap between knowing SQL and performing under interview pressure is almost entirely about pattern fluency — the ability to produce correct syntax for window functions, recursive CTEs, and temporal joins without thinking about syntax, so your mental cycles go to the problem logic.

Three things that actually close this gap:

Write every query cold. No autocomplete, no IDE hints. Open a blank editor and write the full query from memory. The moment you blank on ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW in a live interview, you've lost 3 minutes and your composure. Write it 20 times until it's reflex.

Practice on realistic schemas. LeetCode SQL is useful for basic patterns but the schemas are unrealistically simple. Practice with payment schemas (merchants, transactions, refunds), e-commerce schemas (products, orders, inventory, sellers), and delivery schemas (restaurants, deliveries, drivers, customers). These are the actual schemas in interviews at Indian product companies.

Debug broken queries deliberately. Find 10 SQL queries with subtle bugs — wrong join type, NULL trap, off-by-one in window frame — and practice diagnosing them. This is the Stripe interview format, and it's increasingly common at companies that care about code quality over speed.

The SQL practice module on KnowEase covers all the patterns in this guide — window functions, CTEs, recursive queries, temporal joins, and the NULL traps — with timed practice problems using realistic schemas from e-commerce, payments, and delivery domains. The AI feedback surfaces exactly which clause is breaking your query and why, which is faster than trial-and-error debugging on your own. If your target role also requires data pipeline design, the SQL data engineering interview guide covers anti-join patterns, correlated subqueries, and top-N-per-group queries end-to-end.

The interviewers who built these rounds are not testing whether you know SQL. They already know you know SQL — that's why you got a screen. They're testing whether you know SQL well enough to trust your results on data that's never exactly what you expected. That's the bar. Write queries that handle NULLs correctly by default, not as an afterthought. Read execution plans until it's instinct. Practice debugging other people's broken SQL. Those three habits will carry you through every SQL round you'll face in 2026.