Interview Prep I — SQL & Coding

The technical screen formats

"Technical screen" is an umbrella for a few different formats. For data engineering, the mix skews heavily toward SQL — but you should know what each one looks like so nothing surprises you on the day.

The SQL patterns that recur

Almost every live-SQL question is a remix of a small set of patterns. You already met the building blocks in Foundations Ch.05 (SQL) — here we map them to the shapes interviewers actually ask. Learn to recognise the pattern from the prompt and the query writes itself.

A quick reference for the window-function skeleton you'll reuse in every drill below:

SELECT
  some_columns,
  ROW_NUMBER() OVER (PARTITION BY group_col ORDER BY sort_col DESC) AS rn,
  SUM(value)   OVER (PARTITION BY group_col ORDER BY sort_col)      AS running_total,
  LAG(value)   OVER (PARTITION BY group_col ORDER BY sort_col)      AS prev_value
FROM some_table;
-- PARTITION BY = "restart for each group" (omit it to run over the whole table)
-- ORDER BY     = the sequence the function walks through

SQL drills

Three problems below. Try each one before opening the solution. Write it out — in your head isn't enough; the fluency lives in your fingers. Each solution comes with a one-line explanation of why it works.

-- (a) second-highest salary overall.
-- DENSE_RANK handles ties (two people tied for #1 still makes the next #2).
SELECT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 2
LIMIT 1;

-- (b) top 2 earners per department.
SELECT dept, name, salary
FROM (
  SELECT dept, name, salary,
         ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
  FROM employees
) ranked
WHERE rn <= 2
ORDER BY dept, salary DESC;

WITH monthly AS (
  SELECT date_trunc('month', order_date) AS mth,
         SUM(amount)                     AS revenue
  FROM orders
  GROUP BY date_trunc('month', order_date)
)
SELECT
  mth,
  revenue,
  SUM(revenue) OVER (ORDER BY mth)        AS running_total,
  LAG(revenue) OVER (ORDER BY mth)        AS prev_month,
  ROUND(
    100.0 * (revenue - LAG(revenue) OVER (ORDER BY mth))
          / LAG(revenue) OVER (ORDER BY mth), 1
  )                                       AS mom_pct
FROM monthly
ORDER BY mth;

-- Dedup: number each user's rows newest-first, keep #1.
WITH ranked AS (
  SELECT user_id, status, updated_at,
         ROW_NUMBER() OVER (PARTITION BY user_id
                            ORDER BY updated_at DESC) AS rn
  FROM events
)
SELECT user_id, status, updated_at
FROM ranked
WHERE rn = 1;

-- Stretch: users whose two most-recent events are both 'active'.
WITH ranked AS (
  SELECT user_id, status,
         ROW_NUMBER() OVER (PARTITION BY user_id
                            ORDER BY updated_at DESC) AS rn
  FROM events
)
SELECT COUNT(*) AS streaking_users
FROM (
  SELECT user_id
  FROM ranked
  WHERE rn <= 2
  GROUP BY user_id
  HAVING COUNT(*) = 2                       -- needs at least 2 events
     AND SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) = 2
) t;

Python & coding rounds

Many DE loops include a Python round. Good news: the bar is usually lower than a pure software-engineering interview. You're rarely asked to invert a binary tree or do dynamic programming. Instead you're tested on the thing you'll actually do on the job: pushing data around.

What level to prepare to: aim for comfortable on LeetCode easy and the more straightforward mediums — especially anything tagged hash-map, array, or string. If you can confidently group a list of dicts, count occurrences, deduplicate, and parse a messy line into clean fields, you're at the level most DE Python rounds expect.

Python drills

Two problems. Write a working version before opening the solution — ideally in a real REPL so you catch your own bugs.

from collections import defaultdict

orders = [
    {"region": "us", "amount": 120},
    {"region": "eu", "amount": 80},
    {"region": "us", "amount": 200},
    {"region": "eu", "amount": 95},
]

totals = defaultdict(float)
for o in orders:
    totals[o["region"]] += o["amount"]

# sort by revenue, highest first -> list of (region, total) tuples
result = sorted(totals.items(), key=lambda kv: kv[1], reverse=True)
print(result)   # [('us', 320.0), ('eu', 175.0)]

from collections import Counter

lines = [
    "alice, Alice@X.com ",
    "bob,bob@x.com",
    "alice2, alice@x.com",   # same email as the first row, different name
    "carol,carol@x.com",
    "bob3, BOB@x.com ",      # same email as bob
]

def email_of(line):
    _name, email = line.split(",", 1)   # split once; names never contain commas here
    return email.strip().lower()        # normalise: trim + lowercase

counts = Counter(email_of(line) for line in lines)
dupes = [email for email, n in counts.items() if n > 1]
print(dupes)   # ['alice@x.com', 'bob@x.com']

How to practice (so it actually sticks)

Grinding random problems for hours is low-yield. A handful of deliberate habits beat raw volume:

• Spaced repetition over cramming. Twenty minutes of SQL drills most days beats one six-hour binge. The patterns need to move into reflex, and reflex is built by frequency, not duration.
• Think out loud — always. In a live round, interviewers score your reasoning, not just the final query. Narrate: "I need top-N per group, so that's a ROW_NUMBER partitioned by category…" Practising silently trains the wrong muscle. Talk through every drill in this chapter as if someone's watching.
• Do timed mocks. Set a 30-minute timer and solve under pressure, ideally with a friend playing interviewer (or out loud to a rubber duck). The clock and the audience are the parts you can't simulate by reading.
• State assumptions and edge cases. Ask about NULLs, ties, duplicates, empty inputs. Strong candidates surface these before writing — it shows judgement.
• Review your misses. Keep a short log of every problem you fumbled and the pattern it belonged to. Re-drill those specific patterns next week.

✓ Check yourself

Answer from memory before opening each one.

1. employees holds four salaries: 100, 100, 90, 80. You rank them by salary DESC and filter to rank = 2 to get the second-highest salary. What comes back with ROW_NUMBER, with RANK, and with DENSE_RANK — and why must the filter sit in an outer query?

   Show answer

   ROW_NUMBER returns 100 (the tie gets numbers 1 and 2), RANK returns no rows (it numbers them 1, 1, 3, 4 — there is no 2), and only DENSE_RANK returns 90, because ties share a place without leaving a gap. A window function cannot appear in WHERE, so you rank in a subquery or CTE and filter outside it. See Drill 1.

2. You run the month-over-month query from Drill 2 on an orders table with revenue of 200 in January, 300 in February, nothing in March and 150 in April. What does the April row report as mom_pct, and why is that misleading?

   Show answer

   It reports −50.0, comparing April with February. LAG fetches the previous row in the window's order, not the previous calendar month, and the GROUP BY produces no row for a month with no orders. Say so out loud and ask whether gaps are possible; the fix is to join onto a complete list of months first. See Drill 2.

3. The final drill states that logins has at most one row per user per day. Suppose a user logged in on March 1, 2, 3, 5 and 6, and the March 2 row is accidentally loaded twice. What does the streak query return for them, and what one-line change makes it safe?

   Show answer

   It returns 4, though the true longest streak is 3. The duplicate pushes every later row number up by one, so login_date − rn stops being constant within the first run and, by coincidence, the March 5–6 rows land on the same key as March 1–2 — four rows in one "island". De-duplicate first (SELECT DISTINCT user_id, login_date) before numbering; this is why you ask about duplicates before writing. See the final drill and stating assumptions.

4. In Drill 5 a candidate tallies the emails with Counter but skips the strip().lower() step. What does their code return for the sample lines, and what does that tell you about where the difficulty in this kind of problem sits?

   Show answer

   An empty list: " Alice@X.com ", " alice@x.com", "bob@x.com" and " BOB@x.com " are four different strings, so nothing is counted twice. The counting is trivial; the parsing is the trap — normalise before comparing, and mention malformed lines and empty emails even if the prompt did not. See Drill 5.

5. A friend has solved 150 SQL problems silently, alone, untimed, and can reproduce most of the solutions from memory. In a live round the interviewer renames two columns and adds a tie-breaking rule, and your friend freezes. Which two practice habits would have prevented this?

   Show answer

   Internalising patterns rather than memorising answers — "this is top-N per group, so rank in a window, then filter" survives any schema change, where a memorised query does not. And practising out loud under a timer: interviewers score the reasoning as well as the query, and the clock and the audience are exactly what silent solo practice never rehearses. See How to practice.

6. Your loop next week has a live SQL round and a Python round, and you have one weekend to prepare. How do you split it, and what would change the split?

   Show answer

   Mostly live SQL, and within it window functions: it is the most common and most filtering round, and joins and GROUP BY are table stakes that will not set you apart. The Python bar is lower — group, count, dedup and parse at LeetCode-easy level. The split reverses if window functions are already reflex for you but you cannot yet group a list of dicts without looking up syntax, and if the recruiter says the screen is a take-home, practise a timeboxed clean-model-answer run instead. See The technical screen formats and Python & coding rounds.

WITH numbered AS (
  SELECT user_id, login_date,
         ROW_NUMBER() OVER (PARTITION BY user_id
                            ORDER BY login_date) AS rn
  FROM logins
),
grouped AS (
  -- Subtracting the row number from the date is constant
  -- for any run of consecutive days -> that constant IS the group key.
  SELECT user_id,
         login_date - (rn * INTERVAL '1 day') AS streak_key
  FROM numbered
)
SELECT user_id, MAX(streak_len) AS longest_streak
FROM (
  SELECT user_id, streak_key, COUNT(*) AS streak_len
  FROM grouped
  GROUP BY user_id, streak_key
) runs
GROUP BY user_id
ORDER BY user_id;

Takeaway

• Live SQL is the most common and most filtering round for data engineers, and its questions are a small set of patterns in new clothes.
• Joins and GROUP BY are table stakes, and window functions (ROW_NUMBER, RANK, LAG, SUM() OVER) are what separate candidates who pass.
• A window function cannot sit in WHERE, so rank in a subquery or CTE and filter in the outer query; use DENSE_RANK when ties share a place and ROW_NUMBER when you want exactly one.
• One row per group is ROW_NUMBER() OVER (PARTITION BY entity ORDER BY recency DESC) = 1.
• For month-over-month questions, roll the rows up to one per month in a CTE first, then layer the running total and LAG on top.
• The Python round is usually easier than a software engineering bar: group, count, deduplicate and parse with defaultdict, Counter, csv and json, and normalise messy input before you compare.
• Learn the pattern, not the answer, and say your assumptions about NULLs, ties and duplicates out loud before you write.

You can clear the SQL and coding screens, and the final rounds test how you reason about systems and how you tell your story. → Interview Prep II — System Design & Behavioral

Further reading

• PostgreSQL tutorial: Window functions — PostgreSQL docs, accessed 2026. The clearest short explanation of OVER, PARTITION BY and window ordering.
• PostgreSQL: Window functions — PostgreSQL docs, accessed 2026. The reference list for row_number, rank, dense_rank, lag and lead.
• PostgreSQL: WITH queries — PostgreSQL docs, accessed 2026. Common table expressions, the structure for layering window functions.
• Python: collections — Python docs, accessed 2026. Counter and defaultdict, the two classes behind most grouping answers.
• Python: csv — Python docs, accessed 2026. Parsing delimited lines with the standard library.