SQL Interview Questions for Data Engineers (2026)

Most sql interview questions for data engineers still look like analyst drills: second-highest salary, tidy JOINs, clean tables. Real loops hand you duplicates, late facts, and a follow-up that changes one join.

Interviewers do not ask you to recite ROW_NUMBER. They ask a business question. You must recognize the pattern, state the grain, write the query, then survive the follow-up.

This guide covers twelve patterns that show up across DE SQL screens. For each one: how to recognize it, working SQL, and what they ask next. Memorize patterns, not eighty one-off prompts. Most loops combine two or three patterns in one problem.

How data engineer SQL interviews differ from analyst screens

You still need fundamentals. The differentiator is production reasoning.

Sample schemas (reuse across questions)

-- events: raw clickstream (often duplicated)
-- customers: dimension with possible duplicate emails
-- orders: facts that may arrive late
-- customer_dim: SCD2 history when the prompt asks for it

Assume Postgres-style SQL unless the interviewer names BigQuery, Snowflake, or Spark SQL. Say your dialect out loud.

Q1: Grain before you write SQL

Prompt: "Here is an events table. Build daily active users for last week."

Recognize: This is not "count rows." This is grain: one row per user per day, then count users.

Write:

SELECT activity_date, COUNT(*) AS dau
FROM (
  SELECT DATE(event_ts) AS activity_date, user_id
  FROM events
  WHERE event_ts >= CURRENT_DATE - INTERVAL '7 days'
  GROUP BY 1, 2
) u
GROUP BY 1
ORDER BY 1;

Follow-up: "What if a user fires two event types on the same day?"
Grain stays one row per user per day. Event type belongs in a different metric.

Why DE screens care: Every later mistake (dedupe, joins, marts) starts with an unclear row definition.

Q2: Find duplicates

Prompt: "Which emails appear more than once in customers?"

Recognize: Aggregation + HAVING, not DISTINCT.

Write:

SELECT email, COUNT(*) AS row_count
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

Follow-up: "WHERE vs HAVING?"
WHERE filters rows before grouping. HAVING filters groups after aggregation.

Q3: Deduplicate and keep the latest row

Prompt: "Keep the most recent customer row per email."

Recognize: Top-1 per group → ROW_NUMBER() + filter rn = 1.

Write:

WITH ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY email
      ORDER BY updated_at DESC, customer_id DESC
    ) AS rn
  FROM customers
)
SELECT *
FROM ranked
WHERE rn = 1;

Follow-up: "Two rows tie on updated_at."
Add a tie-breaker. Interviewers want a deterministic rule.

Follow-up: "Why not GROUP BY email with MAX(updated_at)?"
You lose non-key columns unless you join back. Window keep-full-row is cleaner.

Q4: JOIN fan-out (revenue by region)

Prompt: "Join orders to customers and sum revenue by region."

Recognize: Correctness first, then prove the join did not multiply orders.

Write:

-- Sanity check before you trust the sum
SELECT COUNT(*) AS order_rows FROM orders;

SELECT COUNT(*) AS joined_rows
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

SELECT
  c.region,
  SUM(o.amount) AS revenue
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY c.region;

If joined_rows > order_rows, you have fan-out (duplicate customers or a bad key). Fix grain before you ship the metric.

Follow-up: "Some orders have no matching customer."
State whether you use LEFT JOIN and how you flag orphans.

Q5: Anti-join (customers who never ordered)

Prompt: "List customers with no orders."

Recognize: Anti-join → LEFT JOIN + IS NULL, or NOT EXISTS.

Write:

SELECT c.customer_id, c.email
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Follow-up: "NOT IN with NULLs?"
NOT IN breaks when the subquery returns NULL. Prefer NOT EXISTS or the anti-join above.

Q6: ROW_NUMBER vs RANK vs DENSE_RANK

Prompt: "Explain the three ranking functions. When do you use each?"

Recognize: This is the most common window trap. Ties behave differently.

Write (top product per category without gaps):

SELECT *
FROM (
  SELECT
    category,
    product_id,
    revenue,
    DENSE_RANK() OVER (
      PARTITION BY category
      ORDER BY revenue DESC
    ) AS rnk
  FROM product_revenue
) t
WHERE rnk <= 3;

Follow-up: "Find the second-highest salary per department with ties."
Clarify whether ties both count as second (DENSE_RANK) or whether you want a unique pick (ROW_NUMBER).

Q7: Top N per group

Prompt: "Top 3 products by revenue in each category."

Recognize: Partition + rank + filter. Same family as Q3 and Q6.

Write:

WITH ranked AS (
  SELECT
    category,
    product_id,
    revenue,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY revenue DESC
    ) AS rn
  FROM product_revenue
)
SELECT category, product_id, revenue
FROM ranked
WHERE rn <= 3;

Follow-up: "Should ties both appear in the top 3?"
If yes, switch to DENSE_RANK and explain the business rule.

Q8: Running total and moving average

Prompt: "Running total of daily signups, and a 7-day moving average."

Recognize: Window aggregate with an explicit frame.

Write:

SELECT
  activity_date,
  signups,
  SUM(signups) OVER (
    ORDER BY activity_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_signups,
  AVG(signups) OVER (
    ORDER BY activity_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS avg_7d
FROM daily_signups
ORDER BY activity_date;

Follow-up: "ROWS vs RANGE?"
Know that frame choice changes ties and gaps. State which you intend.

Follow-up: "Missing days with zero signups?"
Say whether the calendar spine must be filled first.

Q9: LAG / LEAD (period over period)

Prompt: "Day-over-day change in revenue."

Recognize: Compare current row to previous → LAG.

Write:

SELECT
  activity_date,
  revenue,
  revenue - LAG(revenue) OVER (ORDER BY activity_date) AS dod_change
FROM daily_revenue
ORDER BY activity_date;

Follow-up: "First day is NULL."
Wrap with COALESCE or filter the first row. Say which the product wants.

Q10: CTEs for multi-step logic

Prompt: "For each region, return customers who ordered in the last 30 days and whose lifetime spend is above the region average."

Recognize: Multi-step transform → named CTEs, not nested subquery soup.

Write:

WITH recent AS (
  SELECT DISTINCT customer_id
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
),
lifetime AS (
  SELECT customer_id, SUM(amount) AS lifetime_spend
  FROM orders
  GROUP BY customer_id
),
region_avg AS (
  SELECT c.region, AVG(l.lifetime_spend) AS avg_spend
  FROM customers c
  JOIN lifetime l ON c.customer_id = l.customer_id
  GROUP BY c.region
)
SELECT c.customer_id, c.region, l.lifetime_spend
FROM customers c
JOIN recent r ON c.customer_id = r.customer_id
JOIN lifetime l ON c.customer_id = l.customer_id
JOIN region_avg a ON c.region = a.region
WHERE l.lifetime_spend > a.avg_spend;

Follow-up: "CTE vs subquery?"
Use a CTE when the step is reused or when the interviewer must follow your logic top to bottom.

Q11: SCD Type 2 (history that analysts lists skip)

Prompt: "Customer region changes over time. Past orders must keep the region that was true at order time."

Recognize: Slowly changing dimension Type 2. Not "update the row in place."

Write:

SELECT
  o.order_id,
  o.order_ts,
  d.region
FROM orders o
JOIN customer_dim d
  ON o.customer_id = d.customer_id
 AND o.order_ts >= d.effective_from
 AND o.order_ts < COALESCE(d.effective_to, DATE '9999-12-31');

Follow-up: "How do you load a region change?"
Close the current row (effective_to), insert a new version, keep surrogate keys stable for facts already loaded.

Many listicles never reach SCD2. Warehouse interviews still do.

Q12: Late-arriving facts and a simple quality check

Prompt: "Facts arrive days late. Your daily mart already ran. What do you do? Also find orders loaded yesterday with order_ts older than 7 days."

Recognize: Operational thinking + a data quality query.

Write (the check):

SELECT *
FROM orders
WHERE loaded_at::date = CURRENT_DATE - 1
  AND order_ts < CURRENT_DATE - INTERVAL '7 days';

Strong spoken answer:

1. Partition or filter by event date, not only load date, when the metric needs it.
2. Reprocess affected partitions or use an incremental lookback window.
3. Document accepted lateness and how you backfill.

Follow-up: "How do you make the load idempotent?"
Rerunning the same window should not double-count. Say how you delete/replace the partition or merge on natural keys.

Bonus: Query plans (after correctness)

Optimization usually comes after a working query.

Prompt: "This join is slow. What do you look at?"

Recognize: Row counts and join order first. Then EXPLAIN / EXPLAIN ANALYZE.

Talk track:

1. Confirm fan-out is not inventing billions of rows.
2. Look for sequential scans on large tables, nested loops on bad estimates, missing filters that block partition pruning.
3. Name indexes or clustering only for the engine they use.

Do not lead with indexes if the bug is a multiply join.

Pattern cheat sheet

If you get stuck mid-query

You do not need every SQL dialect cold. State Postgres, BigQuery, or Snowflake out loud. Ask when syntax differs. Note what is portable versus engine-specific.

LeetCode-style SQL helps with speed. Data engineer screens add dirty keys, grain, fan-out, and follow-ups. Practice both. Correctness and row-count sanity come first. Optimization is the second round.

If you stall, state your assumed grain, write a partial CTE, and ask one clarifying question. That mirrors real team work more than silence followed by a perfect query.

Practice the same patterns in a sandbox

Reading will not fix a bad join under a clock. Run timed warehouse-shaped SQL with tests. Wrong row counts teach faster than reading solutions.

Start practicing on Praxicraft: SQL tasks that mirror grain, dedupe, and join fan-out, with XP and a profile you can share.