The SQL & Python interview guide: what to expect and how to prepare

What SQL and Python interviews look like

Companies mix a few formats. Knowing which one you face changes how you prepare, so ask the recruiter early: which database or dialect, can I run my code, and is anyone watching while I type?

A typical loop: recruiter screen, then an online assessment or a technical phone screen, then one or two live technical rounds (SQL, sometimes Python or a case), and finally a behavioural or team-fit conversation. Loops vary a lot between companies; treat this as a map, not a promise.

How it differs by role

None of these are hard rules. A backend interview can include window functions, and an analyst loop can include a schema question. Prepare the core for your role, then cover the neighbours lightly.

What interviewers evaluate

Most rubrics boil down to six things. A correct query that you cannot explain scores lower than a nearly correct one that you built carefully and tested out loud.

• Correctness. The result matches the question, including the cases that are easy to forget. Right grain, right columns, right order.
• Edge cases. NULLs, ties, duplicates, empty groups, date boundaries, “what if a user has no orders?”. Naming these before you are asked is the strongest signal at junior and mid level.
• Communication. You restate the problem, ask useful questions, narrate your plan, and say what you are unsure about.
• Structure. You build the answer in steps (CTEs with sensible names) instead of one 40-line nested query nobody can follow.
• Performance awareness. You can say roughly what the database will do, where an index would help and what changes at a hundred times the data. You do not need to optimise prematurely.
• Code clarity. Consistent aliases, explicit join conditions, no SELECT * in a final answer, one idea per CTE.

A step-by-step framework for solving an SQL question live

When the clock is running, a fixed routine keeps you from freezing. Use these five steps every time, even on easy questions, until they are automatic.

1. Clarify the questionAsk before typing. At minimum settle: the grain (one row per what?), NULLs (can the column be empty, and what should happen then?), ties (if two rows share the top value, do we show both?), duplicates (can the same event appear twice?), date bounds (inclusive or exclusive, which timezone, what is “last 30 days” relative to?), and definitions (“active”, “revenue”: which statuses count?).
2. Define the outputWrite down the column list, the grain and the sort order before any SELECT. Sketch two or three rows of expected output. If you cannot, you have not understood the question yet.
3. Build it step by stepStart with the smallest correct base (usually one join), look at it, then add one thing at a time as CTEs: filter, aggregate, rank, final select. Name each CTE for what it holds. After each step ask “how many rows should this have?”.
4. Check the edge casesWalk a tiny example through the query out loud. Test the empty group, the NULL, the tie, the boundary date and the duplicate. This is where most lost points are recovered.
5. Discuss performance and alternativesSay what the database does (scans, joins, sorts), which index would help, whether a window function or a self-join or an EXISTS would also work, and what you would change at a hundred times the data.

Worked example

The schema is invented for this guide. Dates are text in ISO format, money is stored in cents.

users(user_id, name, country, signed_up_at)            -- signed_up_at: 'YYYY-MM-DD'
payments(payment_id, user_id, amount_cents, status, paid_at)
                                                      -- status: 'paid' | 'failed' | 'refunded'
                                                      -- paid_at: 'YYYY-MM-DD HH:MM:SS'

1. Clarify

• Grain: one row per country. If two users tie for the top, do we return both? Assume yes, both.
• “First 30 days”: does a payment exactly 30 days after sign-up count? Assume not: the window is the sign-up day plus the next 29 days (days 0 to 29 after sign-up), so “less than sign-up date + 30 days”.
• “Successful”: only status = 'paid'; refunded and failed are ignored.
• Users with no payments, and countries where nobody paid: not shown.
• Amount: report in currency units, not cents, with two decimals.

2. Define the output

country, user_id, spend: one row per country (more only on a tie), sorted by country then user_id.

3. Build

WITH first_30 AS (                      -- payments inside each user's window
  SELECT u.user_id, u.country, p.amount_cents
  FROM users u
  JOIN payments p ON p.user_id = u.user_id
  WHERE p.status = 'paid'
    AND date(p.paid_at) >= u.signed_up_at
    AND date(p.paid_at) <  date(u.signed_up_at, '+30 days')
),
per_user AS (                           -- one row per user
  SELECT user_id, country, SUM(amount_cents) / 100.0 AS spend
  FROM first_30
  GROUP BY user_id, country
),
ranked AS (                             -- best spender first, ties share rank 1
  SELECT user_id, country, spend,
         RANK() OVER (PARTITION BY country ORDER BY spend DESC) AS rnk
  FROM per_user
)
SELECT country, user_id, printf('%.2f', spend) AS spend
FROM ranked
WHERE rnk = 1
ORDER BY country, user_id;

Each CTE answers one question: which payments count, how much each user spent, who is first in their country. SQLite syntax is shown; in PostgreSQL the window bound would be p.paid_at < u.signed_up_at + INTERVAL '30 days'. printf('%.2f', spend) prints exactly two decimals (as text); in PostgreSQL use ROUND(spend, 2).

4. Check the edge cases

With a handful of invented rows (Ana and Bo in KZ, Cy and Di in DE, Ed in FR. Ana signed up on 5 January: 30.00 on the sign-up day, 20.00 on day 29 (3 February) and 99.00 on day 30 (4 February); Bo: one 45.00 payment and one refunded 50.00; Cy and Di both with 40.00; Ed with no payments) the query returns:

Ana's day-30 payment is correctly left out (50.00, not 149.00), Bo's refund is ignored, the tie in DE shows both users, and Ed's country FR does not appear. Say each of these out loud as you check it.

5. Discuss

• Ties: RANK keeps both tied users. If the business wants exactly one, switch to ROW_NUMBER() with ORDER BY spend DESC, user_id so the result is deterministic.
• Performance: an index on payments(user_id, paid_at) supports the join, and the date filter can use it only if paid_at is compared raw: p.paid_at >= u.signed_up_at AND p.paid_at < date(u.signed_up_at, '+30 days'), with no function wrapped around the column as in the query above. On large data I would write it that way.
• Alternative: skip the ranking and compare each spend to MAX(spend) per country. Same answer, but the window version extends naturally to “top 3 per country”.

Python + databases: topics and how to answer

These come up for backend and data-engineering roles, usually as a mix of “write it” and “explain it”. Each topic below lists what to know, how to talk about it, and problems to practise on. Examples use sqlite3 from the standard library, the same setup the QueryGym Python track uses.

# wrong: the input becomes part of the SQL
conn.execute(f"SELECT * FROM products WHERE name LIKE '%{term}%'")

# right: the value travels separately
conn.execute("SELECT * FROM products WHERE name LIKE ?", (f"%{term}%",))

13 classic mistakes (and the problems that train them)

These cost people points again and again. Each one has an example and problems where it actually bites. Keep this list as your pre-submit checklist.

WHERE end_date IS NULL
WHERE status IS DISTINCT FROM 'x'   -- standard SQL (PostgreSQL, SQLite 3.39+); older SQLite: status IS NOT 'x'

WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)

SELECT d.name, COUNT(e.employee_id) AS headcount   -- not COUNT(*)
FROM departments d LEFT JOIN employees e ON e.department_id = d.department_id
GROUP BY d.department_id, d.name;

SELECT customer_id, COUNT(*) AS n
FROM orders
WHERE status = 'completed'      -- rows
GROUP BY customer_id
HAVING COUNT(*) > 3;           -- groups

-- loses plans with no active subscription
FROM plans p LEFT JOIN subscriptions s ON s.plan_id = p.plan_id
WHERE s.status = 'active'

-- keeps every plan
FROM plans p LEFT JOIN subscriptions s ON s.plan_id = p.plan_id AND s.status = 'active'

ROUND(100.0 * completed / NULLIF(total, 0), 1)

ORDER BY order_date DESC, order_id DESC LIMIT 10 OFFSET 20

WHERE paid_at >= '2024-12-01' AND paid_at < '2025-01-01'

WITH r AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY revenue DESC, product_id) AS rn
  FROM product_revenue)
SELECT * FROM r WHERE rn <= 2;

Communication: how to think out loud

Interviewers hire people they can work with. Silence reads as being stuck, even when you are not.

Thinking aloud

• Restate the task in your own words: “So I need one row per country, the top spender in their first 30 days. Is that right?”
• Announce the plan before the code: “I will first filter the payments to each user's window, then total per user, then rank within country.”
• Narrate decisions, not keystrokes: “I will use a LEFT JOIN here because customers with no orders must still appear.”
• Flag what you are unsure about instead of hiding it: “I do not remember if this function exists in this dialect; I will write it the way I think it works and we can adjust.”

Clarifying questions worth asking

• What is one row in the result? What is the grain of each table?
• Can this column be NULL? Can a user have no orders?
• If two rows tie, should I return both or pick one?
• Are there duplicate rows, or is this key unique?
• Are date ranges inclusive? Which time zone? Relative to what “today”?
• Which statuses count toward revenue or “active”?
• How big are the tables, and do I need to worry about performance?
• Which dialect am I writing for, and can I run the query?

When you are stuck

1. Say where you are: “I know I need the latest order per customer, and I am deciding between a window function and a correlated subquery.”
2. Shrink the problem: solve it for one customer, then generalise.
3. Write the sub-query you are sure about and look at its output.
4. Try a tiny example by hand and see which row you would pick and why.
5. After a minute or two of no progress, ask for a nudge: “Could you confirm I should be looking at window functions?” Asking early costs less than silence.

Testing your query out loud

Do not just run it and say “looks right”. Pick three or four rows and trace them: “Ana signed up on 5 January, her payment on 6 January is inside the window, the one on 4 February is day 30, so it is out.” Then name the edge case you are checking each time.

Taking hints well

• A hint is not a failure; it is the interviewer helping you show what you can do. Thank them and use it.
• Restate the hint in your own words, then apply it. That shows you understood it instead of copying it.
• Do not argue defensively. If you disagree, say why, calmly, with an example.
• If you got it wrong, say what you would do differently, then fix it. Recovering well is a strong signal.

Day-before and day-of checklist

Practising with the AI tutor

The QueryGym tutor works best as a patient mentor and as a mock interviewer. It sees the problem, your code and the check results, so you do not have to paste anything. It can answer in English or Russian, which is handy if you will be interviewed in a language other than the one you study in. You need to be signed in to use it, and there is a limit of 20 messages per day, so make each question count.

Ask for hints, not answers

• Try first. Spend 10 to 15 minutes alone, then ask.
• Be specific: “Why does this return 12 rows when I expect 9?” is better than “help”.
• Ask for the smallest nudge: “Give me a hint without the query” or “What should I check first?”. Hints are graded, so ask for the next one only when you need it.
• Use the check button to verify, not the chat. When the check passes, ask “why does this work?” and explain it back.

The two interviewer actions

After a mock interview

When a mock interview ends, you get a report and can ask the tutor for feedback on the session: what went well, where you lost time or points, and what to practise next. Feed that into your mistake log and your next plan day.