SQL Interview Questions for Data Analyst and Analytics Engineer Roles (With Answers)

How SQL interviews are actually structured, and worked answers to the questions that come up again and again — from basic joins to window functions to "walk me through how you'd test this model."

By Rakesh Madala — 10 years in the data field

If you've sat on the other side of the table for SQL interviews — and I've done it more times than I can count over ten years — you develop a fast read on candidates within the first two or three questions. It's rarely about whether someone remembers exact syntax. It's about whether they can reason about data the way the job actually requires: turning a vague business question into a precise query, catching their own mistakes before you have to point them out, and explaining a decision instead of just producing an answer and going quiet.

This article is a working set of the SQL interview questions that come up again and again for data analyst and analytics engineer roles, with full worked answers — not just "the query," but the reasoning behind it, what a weak answer sounds like next to a strong one, and where I've watched candidates lose points on questions they technically got right. It also covers how these interviews are typically structured, the mistakes that repeat across candidates regardless of experience level, and a realistic way to prepare that doesn't involve memorizing a hundred solutions you'll never actually use on the job.

One honest note before we start: none of this is exotic knowledge. Every question below is answerable with fundamentals you can learn for free in a few weeks of consistent, deliberate practice — the kind covered in more depth in why SQL matters for data analysts. What actually separates candidates in the room isn't secret tricks. It's whether they've written enough real SQL against real, messy data to have internalized the fundamentals instead of memorized them the night before.

How SQL Interviews Are Actually Structured

Most SQL-heavy hiring processes for analyst and analytics engineer roles collapse into some version of the same three stages, even when different companies dress them up under different names. Knowing what each stage is actually testing changes how you prepare for it — cramming syntax for a stage that's really testing communication is wasted effort.

Stage 1 — the screening call

Usually 20 to 30 minutes, often with a recruiter or a hiring manager rather than someone who'll write SQL alongside you day to day. The SQL content here is typically light: a verbal question ("how would you find duplicate rows in a table?"), a request to describe a project you've done, or occasionally a very short shared-screen exercise with two or three tiny tables. This stage isn't measuring depth. It's a coarse filter to confirm you can talk about SQL fluently enough that a technical round is worth someone's time. Candidates who fumble basic vocabulary here — confusing a join with a union, or not being able to describe what GROUP BY does in plain language — often don't make it further, regardless of how strong their resume looks on paper.

Stage 2 — the live coding round

This is the core SQL assessment, usually 45 minutes to an hour, run against a real or synthetic schema in a shared editor or a tool like a collaborative notebook. You'll be given a handful of tables and asked to answer a sequence of business questions with actual queries, live, while the interviewer watches and often interrupts with follow-ups: "what happens if a customer has no orders at all?" or "can you rewrite that without the subquery?" This is where correctness matters, but so does narration — an interviewer who can't tell whether you're stuck or just thinking has no way to give you partial credit, and in a live round, partial credit is often the difference between a pass and a no.

Stage 3 — the take-home or case study

Especially common for analytics engineer roles, and increasingly common for senior analyst roles too. You're given a small dataset — sometimes a couple of CSVs, sometimes a sandbox warehouse — and asked to do something more open-ended over a few days: model the data, answer a set of analytical questions, write a short set of dbt models with tests, or produce a small write-up of findings. This stage evaluates something the live round genuinely can't: how you structure work when nobody's watching and there's no clock forcing you to skip steps. Sloppy column naming, undocumented assumptions, and untested edge cases show up here in a way they don't in a 45-minute sprint.

The relative weight of these stages shifts depending on which role you're interviewing for. Data analyst interviews tend to lean harder on stage 2 — can you get from a business question to a correct, efficient query quickly, under a bit of pressure. Analytics engineer interviews tend to lean harder on stage 3 — can you make defensible modeling decisions, write tests that would actually catch a real regression, and explain trade-offs rather than just produce a working answer. Neither format is testing whether you've memorized syntax. Both are testing whether your thinking holds up when someone experienced starts poking at it.

Foundational Questions: Joins, GROUP BY/HAVING, Aggregates

These show up in nearly every SQL interview regardless of seniority, because they're the load-bearing wall everything else stands on. Getting one of these visibly wrong is a bigger red flag than struggling on a genuinely hard window-function question later, precisely because there's no excuse for it — these are things you've had months or years to get comfortable with.

For all four questions below, assume a simple e-commerce schema: customers(customer_id, name, signup_date, region), orders(order_id, customer_id, order_date, status, order_total), order_items(order_item_id, order_id, product_id, quantity, unit_price), and products(product_id, product_name, category).

Q1. Write a query to find customers who placed an order in January 2026 but not in February 2026.

weak answer

A common weak answer reaches for two separate IN subqueries or a self-join on customer_id and then tries to filter out overlap with a NOT IN against a list that can silently include NULL. It often technically runs, but it's fragile: if the February order-id subquery ever returns a NULL (say, from a bad join elsewhere), NOT IN against a list containing NULL returns zero rows for every customer, and the whole query goes quietly, confidently wrong.

strong answer

SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.order_date >= '2026-01-01' AND o.order_date < '2026-02-01'
)
AND NOT EXISTS (
  SELECT 1 FROM orders o2
  WHERE o2.customer_id = c.customer_id
    AND o2.order_date >= '2026-02-01' AND o2.order_date < '2026-03-01'
);

EXISTS and NOT EXISTS are immune to the NULL trap that sinks NOT IN, because they're testing for the presence or absence of a matching row rather than comparing against a list of values. This is also a cleaner mental model to narrate out loud: "give me customers where a January order exists, and a February order does not." A candidate who reaches for EXISTS unprompted, and can explain why over NOT IN, is signaling real experience rather than textbook recall — I've asked this exact follow-up ("why not NOT IN?") in more interviews than I can count, specifically because so few people can answer it.

Q2. Explain the difference between INNER, LEFT, RIGHT, and FULL joins — and show me an example where picking the wrong one silently changes the answer.

Most candidates can recite the definitions. Fewer can demonstrate why the choice matters with a concrete case, which is really what's being tested. Take a query meant to report total order value per customer, including customers who've never ordered:

-- wrong: INNER JOIN silently drops customers with zero orders
SELECT c.customer_id, c.name, COALESCE(SUM(o.order_total), 0) AS lifetime_value
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name;

-- correct: LEFT JOIN keeps every customer, orders or not
SELECT c.customer_id, c.name, COALESCE(SUM(o.order_total), 0) AS lifetime_value
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name;

The first version isn't broken syntax — it runs fine and returns a plausible-looking table. That's exactly what makes it dangerous: it just quietly excludes every customer who has never placed an order, which for a report meant to answer "what's the lifetime value of our customer base" is a materially wrong answer, not a cosmetic one. This is the single most common real-world join bug I see in production code, not just interviews: someone needed an outer join to preserve rows with no match, used an inner join out of habit, and the query never errored — it just under-reported. A good candidate volunteers this distinction without being asked "and what if a customer has zero orders?" A strong candidate has already asked themselves that question before writing the join.

If joins specifically are shaky for you, it's worth spending focused time on SQL joins explained before anything else on this list — nearly everything downstream, including window functions and CTEs, assumes you're already fluent here.

Q3. Find every product category that has been purchased by more than 50 distinct customers.

where candidates lose points

The most common slip here isn't logic, it's grain: candidates count order_items rows instead of distinct customers, which over-counts anyone who bought the same category more than once. The fix is one word — DISTINCT inside the aggregate — but forgetting it produces a result that looks entirely plausible and is simply wrong.

SELECT p.category, COUNT(DISTINCT o.customer_id) AS distinct_buyers
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN products p ON p.product_id = oi.product_id
GROUP BY p.category
HAVING COUNT(DISTINCT o.customer_id) > 50
ORDER BY distinct_buyers DESC;

Worth narrating out loud in the interview: joining order_items to orders to products multiplies rows by however many line items each order has, so any aggregate that doesn't explicitly account for that — like a plain COUNT(*) instead of COUNT(DISTINCT ...) — is measuring "line items," not "customers," even though the column alias might claim otherwise. A candidate who says this out loud before running the query is showing exactly the kind of care that separates a pass from a maybe.

Q4. What's the difference between WHERE and HAVING — and what's wrong with this query?

SELECT region, COUNT(*) AS total_orders
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE COUNT(*) > 100
GROUP BY region;

This one won't even run — most engines will throw an error along the lines of "aggregate functions are not allowed in WHERE." The reasoning matters more than memorizing the rule: WHERE filters individual rows before any grouping or aggregation happens, so at the point WHERE is evaluated, COUNT(*) doesn't exist yet as a value tied to any single row — there's nothing to compare 100 against. HAVING filters after the GROUP BY has collapsed rows into groups, once aggregates like COUNT(*) actually have a value per group. The fix is simply swapping the clause:

SELECT region, COUNT(*) AS total_orders
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY region
HAVING COUNT(*) > 100;

The deeper point interviewers are actually probing for is whether you understand SQL's logical order of operations — FROM/JOIN, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY — because that order explains a whole category of "why doesn't this work" bugs beyond just this one. A candidate who can walk through that sequence from memory rarely struggles with the rest of the interview.

Intermediate Questions: Window Functions, CTEs vs. Subquery, NULL Handling, Deduplication

This is where interviews start separating people who've used SQL from people who've used SQL seriously. None of what follows is obscure — window functions in particular show up constantly in real analyst and analytics engineer work — but it's also the point where candidates who learned SQL purely from tutorials, without ever touching a genuinely messy dataset, start to visibly struggle.

Q1. Write a query to find the second-highest order value for each customer.

This is the canonical window-function question, and it's popular precisely because there are three plausible-looking approaches with different correctness properties, and picking the right one shows whether you actually understand ties.

weak answer

A weak answer uses a correlated subquery counting how many distinct order values are greater than the current row's — functionally workable, but slow at scale (it re-scans per row) and awkward to extend if the question changes to "third-highest" or "top 3." It's not wrong, but it signals someone who hasn't yet reached for window functions as a default tool.

strong answer

WITH ranked AS (
  SELECT
    customer_id,
    order_id,
    order_total,
    DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY order_total DESC) AS rnk
  FROM orders
)
SELECT customer_id, order_id, order_total
FROM ranked
WHERE rnk = 2;

The choice between ROW_NUMBER(), RANK(), and DENSE_RANK() is the actual crux of the question, and it's exactly where I've watched strong-looking candidates lose the round. ROW_NUMBER() assigns 1, 2, 3, 4 with no regard for ties — if two orders are tied for the highest value, one of them gets arbitrarily bumped to "second," which is rarely what a business question like this actually means. RANK() gives tied rows the same rank but then skips the next one (1, 1, 3, 4) — so "second highest" would return nothing if the top two orders are tied, since there's no rank 2. DENSE_RANK() gives tied rows the same rank with no gap (1, 1, 2, 3) — so "second highest" correctly returns the next distinct value after the tie. If the business question is "what's the second-highest distinct value," DENSE_RANK() is almost always the right tool, and being able to explain why the other two would give a wrong or empty answer under ties is what turns a correct-looking query into a demonstrably correct one.

Q2. When would you use a CTE instead of a subquery, and is one actually faster than the other?

A common weak answer treats this as purely stylistic — "CTEs are just easier to read" — and stops there. That's true but incomplete, and it misses the part of the question that's actually being tested: whether you understand how the optimizer treats each one, because that has real performance implications, not just readability ones.

In most modern engines — Postgres 12+, Snowflake, BigQuery, Redshift — a non-recursive CTE referenced once is typically inlined by the optimizer exactly the same way a subquery would be, so there's often no inherent performance difference for a straightforward case. Where it gets more interesting: some engines (Postgres historically, in older versions, by default) can materialize a CTE — actually run it once and store the result — which is a performance win if you reference the same CTE multiple times downstream, but can be a performance trap if it prevents the optimizer from pushing a filter down into it. The honest, senior answer sounds like: "I default to CTEs for readability and for breaking a complex transformation into named, testable steps, but if I've got a CTE referenced multiple times or one that's clearly not getting a filter pushed into it, I'll check the execution plan rather than assume." That last clause — checking rather than assuming — is the part that signals real production experience rather than tutorial-level familiarity.

-- deeply nested subquery: hard to read, hard to debug one step at a time
SELECT region, avg_order_value
FROM (
  SELECT region, AVG(order_total) AS avg_order_value
  FROM (
    SELECT c.region, o.order_total
    FROM customers c
    JOIN orders o ON o.customer_id = c.customer_id
    WHERE o.status = 'completed'
  ) completed_orders
  GROUP BY region
) region_avgs
WHERE avg_order_value > 500;

-- same logic, expressed as named, readable steps
WITH completed_orders AS (
  SELECT c.region, o.order_total
  FROM customers c
  JOIN orders o ON o.customer_id = c.customer_id
  WHERE o.status = 'completed'
),
region_avgs AS (
  SELECT region, AVG(order_total) AS avg_order_value
  FROM completed_orders
  GROUP BY region
)
SELECT * FROM region_avgs WHERE avg_order_value > 500;

Both return identical results. The CTE version is the one you can actually debug mid-interview by selecting from completed_orders alone to sanity-check it before building on top — which is exactly the workflow you want to demonstrate live, because it shows you catching your own mistakes before the interviewer has to.

Q3. Why might COUNT(*) and COUNT(some_column) return different numbers, and how can a NULL in a join key quietly drop rows you expected to see?

The first half is usually answered correctly: COUNT(*) counts rows regardless of content, COUNT(column) counts only rows where that column is non-NULL. The second half — the join behavior — is where it gets more interesting and where I'd push a strong candidate.

NULL never equals anything in SQL's three-valued logic, including another NULL. That means a join condition like ON a.customer_id = b.customer_id will never match a row where a.customer_id is NULL, even against a row in b where customer_id is also NULL. In an inner join, those rows simply vanish with no error — the result set just quietly gets smaller than expected. This is a genuinely common real-world bug: a source system that occasionally writes a NULL foreign key (a guest checkout with no customer record, an order created before a customer record existed) will cause those orders to disappear entirely out of any inner-joined report, and unless someone explicitly reconciles row counts against the source table, nobody notices.

-- sanity check worth running before trusting a join's row count
SELECT
  (SELECT COUNT(*) FROM orders) AS total_orders,
  (SELECT COUNT(*) FROM orders WHERE customer_id IS NULL) AS orders_with_no_customer;

Naming that check unprompted — "before I trust this join's row count, I'd want to know how many orders have a null customer_id" — is a small thing that consistently reads as senior-level instinct rather than junior-level correctness. It's the difference between someone who writes queries that run and someone who writes queries they've actually verified.

Q4. Given a raw source table with duplicate customer records from a flaky upstream pipeline, write a query to keep only the most recent record per customer.

Deduplication questions test the same muscle as the second-highest-value question — partitioned window functions — applied to a genuinely common real-world cleanup task. Assume raw_customers(customer_id, email, name, region, updated_at) where the same customer_id can appear multiple times because of retries in an upstream ingestion job.

WITH deduped AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY updated_at DESC
    ) AS rn
  FROM raw_customers
)
SELECT customer_id, email, name, region, updated_at
FROM deduped
WHERE rn = 1;

ROW_NUMBER() is the right tool here rather than DENSE_RANK(), and it's worth explaining why if asked: you specifically want exactly one row per customer_id even if two duplicate rows happen to share the identical updated_at timestamp — ROW_NUMBER() guarantees a strict 1, 2, 3 ordering with no ties, so WHERE rn = 1 always returns exactly one row per partition. On engines that support it — Snowflake and BigQuery both do — a QUALIFY clause lets you skip wrapping the window function in a CTE entirely: SELECT * FROM raw_customers QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1. Mentioning QUALIFY when it's actually available on the platform you're interviewing for is a nice small signal that you know the specific engine, not just generic ANSI SQL — but it's worth being careful here too, since standard Postgres and MySQL don't support it, and confidently offering a QUALIFY clause on a Postgres interview is its own small flag in the other direction.

Advanced / Analytics-Engineer-Specific Questions: dbt Concepts, Incremental Models, SCD, Data Quality Testing

These questions are less about raw SQL syntax and more about how you think about maintaining a data model over time — the questions a role that owns a transformation layer, rather than just querying one, actually needs answered. If you're interviewing purely for a data analyst role these may show up in lighter form or not at all; for analytics engineer roles, expect this to be a meaningful chunk of the interview.

Q1. What does a dbt model actually do, and what's the difference between the four materializations?

A dbt model, stripped down, is a SELECT statement saved as a .sql file that dbt compiles and runs against your warehouse — dbt itself doesn't move or store data outside your warehouse, it generates and executes SQL. The materialization setting controls what dbt does with that SELECT statement when it runs:

The follow-up worth being ready for: "how would you decide between table and incremental for a given model?" The honest answer is about run-time cost and data volume, not a fixed rule — a model that fully rebuilds in ninety seconds off a ten-million-row source doesn't need incremental logic and the added complexity it brings; a model that's scanning a multi-billion-row events table on every run absolutely does. Reaching for incremental prematurely on a small model, just because it sounds more sophisticated, is itself a minor red flag — it adds real complexity (and real risk, covered next) for no actual benefit.

Q2. Walk me through how an incremental dbt model actually works, and what can go wrong with late-arriving data.

On the first run, or whenever the model is rebuilt from scratch (dbt run --full-refresh), dbt runs the model's SELECT statement in full and materializes it as a table. On subsequent runs, dbt wraps your query with logic — driven by the is_incremental() macro — that filters the source down to only new or changed rows, typically using a high-watermark column like an updated timestamp:

-- models/fct_orders.sql
{{ config(materialized='incremental', unique_key='order_id') }}

SELECT order_id, customer_id, order_date, order_total, updated_at
FROM {{ source('ecommerce', 'orders') }}

{% if is_incremental() %}
  WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}

The {% if is_incremental() %} block only compiles into the query on incremental runs, not on the initial build — that's the mechanism, and being able to say that plainly, rather than gesturing vaguely at "it just knows," is what separates someone who's configured this once from someone who understands it.

The late-arriving data problem is the honest, meaty part of this question and it's where I'd expect a senior candidate to volunteer the risk unprompted. If your watermark is "rows updated since the last run's max updated_at," and a record arrives late with an updated_at that's earlier than your current watermark — a backfilled order, a delayed event from a mobile client that was offline — your incremental filter will never pick it up, because it's looking strictly forward from the last high-water mark. The row silently never makes it into the model. Two honest mitigations: build in a lookback window (filter on updated_at > max(updated_at) - interval '3 days' rather than a hard cutoff, accepting some reprocessing in exchange for safety), and schedule a periodic full-refresh as a backstop rather than trusting incremental logic to be correct forever. A candidate who says "incremental models are a trade-off — you're accepting some risk of missed late-arriving rows in exchange for run-time savings, and here's how I'd bound that risk" is answering at a level well past "I've used dbt before."

Q3. What is a Slowly Changing Dimension Type 2, and how would you implement it?

An SCD Type 2 is how you preserve history on a dimension table when an attribute changes — instead of overwriting a customer's region when they move, you keep the old row as history and insert a new row for the current value, so that any fact table joined to the dimension "as of" a past date still gets the value that was true at that time. This matters a lot more than it sounds: overwriting in place (SCD Type 1) silently rewrites history, which quietly corrupts any historical reporting that relies on "what was true then," not "what's true now."

A minimal Type 2 table needs a surrogate key, the natural key, the tracked attributes, and validity bounds:

-- dim_customer: customer_key is surrogate, customer_id is the natural/business key
CREATE TABLE dim_customer (
  customer_key    BIGINT,       -- surrogate key, unique per version of a customer
  customer_id     BIGINT,       -- natural key, repeats across versions
  region          VARCHAR,
  effective_start DATE,
  effective_end   DATE,        -- NULL, or a sentinel future date, while current
  is_current      BOOLEAN
);

-- when region changes for an existing customer: close out the old row, insert the new one
UPDATE dim_customer
SET effective_end = CURRENT_DATE, is_current = FALSE
WHERE customer_id = 4821 AND is_current = TRUE;

INSERT INTO dim_customer (customer_key, customer_id, region, effective_start, effective_end, is_current)
VALUES (NEXTVAL('dim_customer_seq'), 4821, 'EU-West', CURRENT_DATE, NULL, TRUE);

In dbt specifically, this is what the built-in snapshot feature exists to automate — it detects row changes between runs (either by checking specified columns or a timestamp) and manages the dbt_valid_from/dbt_valid_to bookkeeping for you, so you rarely hand-roll the update/insert pattern above in a modern stack. Still, being able to explain what a snapshot is doing under the hood, in raw SQL terms, is exactly what separates "I've pointed dbt snapshot at a table" from actually understanding the mechanism — and interviewers will often ask you to explain it precisely because the tool makes it easy to use without understanding.

Q4. Walk me through how you'd test a dbt model — what's the difference between business and technical data quality checks?

dbt's built-in generic tests — unique, not_null, relationships, accepted_values — are the baseline, and any analytics engineer should be able to name and apply them without hesitation. They're what I'd call technical data quality: is the primary key actually unique, does a foreign key actually resolve to a row in the parent table, is a status column only ever one of the values the business logic expects. These catch structural breakage — a duplicate key, an orphaned foreign key, a null where the schema promises none.

Business data quality is a different, harder layer: is the number right, not just the shape. A fct_orders table can pass every uniqueness and not-null test and still report a 40% week-over-week revenue jump that's actually a currency conversion bug, or a double-count from a join that fans out. Those require singular tests — custom SQL assertions written specifically for the business logic of that model, not generic structural checks:

-- tests/assert_order_total_matches_line_items.sql
-- a singular dbt test: fails the build if it returns any rows
SELECT o.order_id, o.order_total, li.line_items_sum
FROM fct_orders o
JOIN (
  SELECT order_id, SUM(quantity * unit_price) AS line_items_sum
  FROM order_items
  GROUP BY order_id
) li ON li.order_id = o.order_id
WHERE ABS(o.order_total - li.line_items_sum) > 0.01;

The strongest version of this answer names both layers explicitly, gives a concrete example of each, and is honest about the fact that technical tests are necessary but nowhere near sufficient — a model can be structurally perfect and still be telling the business a lie. This distinction is worth understanding in real depth rather than as an interview soundbite; I've written a full breakdown of it in business vs. technical data quality testing in dbt, and it's a genuinely common interview follow-up once you've named the two categories: "give me an example of a test that would pass technically but still let a wrong number through."

"Explain This Query" and Query-Optimization Questions

A different flavor of question, common for analytics engineer and senior analyst interviews, hands you an already-written query — sometimes one that "works" and produces a correct result — and asks you to critique it, explain what it's doing step by step, or make it faster. This tests something the write-a-query-from-scratch questions don't: whether you can read someone else's SQL critically, which is most of what maintaining a real codebase actually involves.

Here's a representative example, styled after real queries I've been handed to fix:

SELECT DISTINCT c.*, o.order_id, o.order_total
FROM customers c, orders o
WHERE c.customer_id = o.customer_id
  AND YEAR(o.order_date) = 2026
ORDER BY o.order_total DESC;

Walking through this the way a strong candidate would out loud, top to bottom: the comma-style join (FROM customers c, orders o) is old implicit-join syntax rather than an explicit JOIN ... ON — it works, but it's easy to accidentally turn into a cross join by forgetting the WHERE predicate, and it hides the join condition away from where a reader expects to find it, which is a maintainability problem more than a correctness one here. SELECT DISTINCT c.* is a bigger flag: DISTINCT is very often a band-aid over a fan-out — the join to orders multiplies each customer row by however many orders they have, and pulling every customer column while also pulling per-order columns forces DISTINCT to do expensive work de-duplicating a result set that was structurally wrong to build in the first place. The real fix is usually to be honest about the grain: either this query is "one row per order" (drop the customer *, just pull the specific customer columns needed) or it's "one row per customer" (aggregate the order data instead of listing every order). DISTINCT papering over that decision is a smell, not a solution.

The last line worth flagging is YEAR(o.order_date) = 2026. Wrapping an indexed column in a function like this is one of the most common, most avoidable performance mistakes in production SQL: most query planners can't use an index on order_date when the column is wrapped in a function, because the index is built on the raw column values, not on YEAR() applied to them — so the engine falls back to scanning every row and computing YEAR() on each one just to check the filter. The fix is a sargable range predicate instead:

SELECT o.order_id, o.customer_id, o.order_total
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date >= '2026-01-01' AND o.order_date < '2027-01-01'
ORDER BY o.order_total DESC;

When an interviewer says "how would you check whether this is actually faster," the honest answer is: run EXPLAIN (or EXPLAIN ANALYZE where available) on both versions and compare — specifically look for whether the plan shows an index scan or range scan on order_date in the rewritten version versus a full sequential scan in the original, and compare estimated versus actual row counts if the engine surfaces both, since a large gap between them usually means the table's statistics are stale rather than that the query itself is wrong. Guessing at performance without looking at a plan, or worse, asserting confidently that something is "definitely faster" without ever having run it, is a bigger red flag in this line of questioning than not immediately spotting every issue in the query. I go into the mechanics of reading execution plans and indexing decisions in a lot more depth in SQL and dbt query optimization techniques — worth a read before an interview if this is an area you're shaky on. If you want to sanity-check formatting or get a plain-English read on an unfamiliar query before an interview, the SQL explainer tool on tools.techedge.in is built for exactly that.

Case-Study / Data-Modeling Walkthrough Questions

"Design a schema for X" questions are deliberately open-ended, and that's the point — there's rarely a single correct answer, and what's being evaluated is whether you have a repeatable process for turning an ambiguous prompt into a defensible structure, rather than freezing or diving straight into table definitions before you understand the problem.

Take a representative prompt: "design a data model for a subscription-based SaaS product's billing data, supporting monthly revenue reporting and churn analysis." A weak response starts sketching CREATE TABLE statements within the first thirty seconds. A strong response starts by asking questions and stating assumptions, because the prompt is genuinely underspecified — and an interviewer who watches you jump straight to syntax without establishing the grain has already learned something about how you'd behave on a real ambiguous ticket.

A structure that holds up under interview pressure

Clarify the grain first. "Revenue reporting" could mean monthly recurring revenue, or it could mean cash actually collected — those are different numbers with different definitions of what a "row" represents. Ask, or state your assumption explicitly and move on: "I'll assume we're modeling recognized monthly recurring revenue per subscription, and treat cash collection as a separate concern." Naming the assumption out loud is what matters here — an interviewer would rather hear a slightly wrong assumption stated clearly than watch someone quietly guess and never say so.

Separate facts from dimensions. The natural fact table here is something like fct_subscription_events — one row per meaningful event (subscription started, plan changed, subscription cancelled) with a timestamp, an amount, and foreign keys out to dimensions. The dimensions are dim_customer and dim_plan, and it's worth flagging unprompted that dim_customer is a strong candidate for SCD Type 2 treatment — a customer's plan tier or account status changing over time needs to be preserved for accurate historical churn analysis, not overwritten in place, which ties directly back to the SCD question above. Bringing that connection up yourself, rather than waiting to be asked, is exactly the kind of synthesis that reads as senior judgment rather than rehearsed answers to isolated questions.

Define churn precisely before modeling it. "Churn" sounds simple and almost never is — is a downgrade churn, or only a full cancellation? Does a customer who cancels and resubscribes within thirty days count as churned at all, or as a pause? A strong candidate states a working definition, builds toward it, and explicitly flags that this is a business decision, not a technical one, that would need sign-off from whoever owns the metric in production. Modeling a metric without ever naming its definition is one of the fastest ways to build something technically correct and practically useless.

Talk about grain of the fact table and how it affects downstream aggregation. If fct_subscription_events is event-grain (one row per state change), computing "MRR at the end of each month" requires a snapshot-style query that reconstructs state as of a point in time — not a simple SUM. It's worth naming that trade-off directly: an event-grain fact table is more flexible and audit-friendly, but it pushes complexity into every downstream query that wants a point-in-time balance; a separate pre-aggregated monthly snapshot table trades some flexibility for much simpler reporting queries. Neither is objectively correct — being able to state the trade-off, rather than picking one and asserting it's obviously right, is what the question is actually testing.

The meta-skill across every case-study prompt, regardless of the specific domain the interviewer picks, is the same four-step shape: clarify the grain and any ambiguous terms, separate what's a fact from what's a dimension, name the trade-offs of whatever structure you propose rather than presenting it as the only option, and flag which decisions are actually business calls that need a stakeholder, not a database design choice. Candidates who memorize "star schema" as a keyword to drop into any modeling question, without being able to reason about grain or trade-offs underneath it, get caught out fast by even one good follow-up question.

Common Mistakes Candidates Make

These repeat across experience levels — I've watched candidates with genuinely strong SQL skills lose points on process mistakes that have nothing to do with whether they know the syntax.

How to Actually Prepare

Preparing well for SQL interviews looks less like memorizing question banks and more like putting in enough real reps that the fundamentals stop requiring conscious thought, freeing up your attention for the actual business logic of whatever you're asked.

Practice on real questions, not just tutorials

Tutorial data is clean by design, and clean data doesn't build the instincts this article has spent most of its length on — NULL handling, fan-out from joins, tie-breaking in window functions. Platforms like StrataScratch and LeetCode's database question set are built specifically around realistic interview-style prompts rather than toy examples, and working through even twenty or thirty of them, actually writing the SQL yourself rather than reading a solution, builds real pattern recognition for the shapes these questions take. Mode's free interactive SQL tutorial is a solid on-ramp if joins and aggregation still feel shaky before you get to that stage.

Build one small, real project instead of ten tutorial exercises

A focused portfolio project beats a long list of completed courses, because it forces you to make and defend the kinds of decisions covered in the case-study section above, rather than just answering questions someone else already scoped for you. Pick a public dataset in a domain you understand, set up a small dbt project with a staging layer and a couple of mart models, write both generic and singular tests for at least one of them, and be ready to explain every modeling decision out loud — why this grain, why this materialization, why this test and not another one. That last part matters more than the project itself: being able to defend a decision under a follow-up question is exactly what the interview is going to demand of you.

Practice explaining your SQL out loud, not just writing it silently

This is the single most underrated form of practice, and almost nobody does it deliberately. Writing a correct query alone at your desk and writing the same query while narrating your reasoning to another person are genuinely different skills, and the interview only tests the second one. Find a peer, a study partner, or even just record yourself talking through a problem — the goal isn't a rehearsed script, it's getting comfortable thinking out loud without it costing you focus on the actual SQL. A short, honest mock interview with someone who'll actually push back on your answers is worth more than another week of solving problems silently.

Know the difference between what you should memorize and what you should understand

Syntax for window function frames, the exact argument order of a less common function, the specific keyword for a platform-specific feature — these are fine to look up, and no reasonable interviewer expects perfect recall of them. What you can't look up in the moment is judgment: knowing to ask about grain before writing a join, knowing why NOT EXISTS is safer than NOT IN against a nullable column, knowing that a business metric needs a stated definition before it can be modeled. Spend your prep time disproportionately on the second category. It's also worth keeping a formatter handy while you practice — the SQL formatter on tools.techedge.in is a fast way to clean up a messy query you've written under time pressure so you can actually read your own logic back before deciding it's done.

Wrapping Up

Nothing in this article is secret knowledge, and that's deliberate — the questions that come up again and again in SQL interviews for analyst and analytics engineer roles are, almost without exception, testing fundamentals rather than trivia. Joins, grouping, window functions, NULL handling, and a working sense of how to model and test a dataset over time cover the overwhelming majority of what you'll actually be asked, in some combination or another, regardless of which company or which specific format you end up sitting in front of.

What separates a strong answer from a technically-correct-but-forgettable one, at every level of this article, is the same handful of habits: clarifying before writing, narrating instead of going quiet, naming the edge cases before you're asked about them, and being able to explain why a choice was made rather than just that it works. Those habits are learnable, and they're built the same way SQL itself is — through enough real, deliberate repetition that they stop being effortful and start being how you naturally approach a problem.

— This article is part of an ongoing skills series on techedge.in. If a question here tripped you up, that's useful information, not a bad sign — drop a comment about which one, I read every one.