SQL Interview Questions for Data Analyst Roles in 2026: 20 Questions With Model Answers

SQL interview questions for data analyst roles have narrowed sharply. Companies stopped asking for textbook definitions of normalisation and started handing candidates two small tables and a business question. That is good news, because a narrow test is a preparable test. Below are 20 questions that actually appear in Indian analyst screens in 2026, each with a model answer and the reasoning an interviewer is listening for. Treat it as a lab, not a reading list.

What SQL Interview Questions for Data Analyst Roles Actually Test

An analyst SQL round is usually 30 to 45 minutes, shared screen, two or three tables. The interviewer is not checking memorised syntax.

Notice what is absent. Nobody asks you to recite DELETE versus TRUNCATE any more; if a study list spends its first ten items on definitions, it is out of date. 360DT's live Data Analyst course covering Excel, SQL, Python and Power BI runs SQL as query drills against real tables for the same reason.

Set Up a Free Practice Database in About Ten Minutes

You cannot prepare for a hands-on round by reading answers. Build the tables. The fastest path is DuckDB, an in-process analytical database needing no server and no Docker. The official DuckDB announcement dated 22 July 2026 puts the current version at 1.5.5, in the 1.5 "Variegata" line that shipped 9 March 2026 with a reworked CLI and the new VARIANT type.

pip install duckdb==1.5.5
python -c "import duckdb; print(duckdb.__version__)"

Save this as seed.sql. Every query below runs against exactly these four customers and six orders, so you can verify your output line for line.

CREATE OR REPLACE TABLE customers (
  customer_id INTEGER, name VARCHAR, city VARCHAR, signup_date DATE
);

CREATE OR REPLACE TABLE orders (
  order_id INTEGER, customer_id INTEGER, order_date DATE,
  amount DECIMAL(10,2), status VARCHAR
);

INSERT INTO customers VALUES
  (1,'Aarav','Pune','2026-01-05'),
  (2,'Diya','Mumbai','2026-01-18'),
  (3,'Kabir','Pune','2026-02-02'),
  (4,'Meera','Chennai','2026-02-20');

INSERT INTO orders VALUES
  (101,1,'2026-02-10',2400.00,'paid'),
  (102,1,'2026-03-14',1800.00,'paid'),
  (103,2,'2026-02-21',5200.00,'paid'),
  (104,2,'2026-04-02', 900.00,'refunded'),
  (105,3,'2026-03-30',3100.00,'paid'),
  (106,1,'2026-05-11',2750.00,'paid');

import duckdb

con = duckdb.connect("interview.db")
with open("seed.sql") as f:
    for stmt in f.read().split(";"):
        if stmt.strip():
            con.execute(stmt)

con.sql("SELECT COUNT(*) AS orders FROM orders").show()

Meera has no orders and order 104 is refunded. Both facts are deliberate; most questions below hinge on them.

Prefer cloud scale? Google's BigQuery sandbox needs no credit card and includes the standard free tier of 1 TiB of query processing and 10 GiB of active storage per month, per Google Cloud's published pricing. For a self-managed server, PostgreSQL 18.6 was the current stable release as of 13 August 2026, with version 19 then in beta. Any of the three works; the SQL here ports with small date-function changes.

Round 1: Joins, NULLs and Aggregation

SQL query interview questions and answers: joins and NULLs

Question 1. Return every customer with their total paid order value, including customers who never ordered.

SELECT c.customer_id,
       c.name,
       COALESCE(SUM(o.amount), 0) AS paid_value
FROM customers c
LEFT JOIN orders o
       ON o.customer_id = c.customer_id
      AND o.status = 'paid'
GROUP BY c.customer_id, c.name
ORDER BY paid_value DESC;

The whole question lives in one line. The predicate o.status = 'paid' sits in the ON clause. Move it to WHERE and Meera disappears, because after the join her row carries a NULL status and NULL = 'paid' is not true: your LEFT JOIN has become an INNER JOIN. Say that out loud; it is the sentence that separates candidates. Expected output: Aarav 6950.00, Diya 5200.00, Kabir 3100.00, Meera 0.00.

Question 2. A colleague uses WHERE status <> 'paid' to count non-paid orders and gets a number that is too low. Why? Because NULL <> 'paid' evaluates to UNKNOWN, and WHERE keeps only TRUE. Two fixes, and knowing both is the point:

SELECT COUNT(*) FROM orders WHERE status IS DISTINCT FROM 'paid';

SELECT COUNT(*) FROM orders WHERE status <> 'paid' OR status IS NULL;

IS DISTINCT FROM is NULL-safe in PostgreSQL and DuckDB. MySQL spells the NULL-safe comparison <=>. Mentioning that portability difference is free credit.

Question 3. COUNT(*) versus COUNT(column) versus COUNT(DISTINCT column)? COUNT(*) counts rows; COUNT(column) counts rows where the column is not NULL; COUNT(DISTINCT column) counts distinct non-NULL values. On the seed data, SELECT COUNT(*), COUNT(DISTINCT customer_id) FROM orders returns 6 and 3, because Meera never appears in orders at all.

Question 4. Find duplicate rows.

SELECT customer_id, order_date, amount, COUNT(*) AS copies
FROM orders
GROUP BY customer_id, order_date, amount
HAVING COUNT(*) > 1;

Question 5. Why can you not use a SELECT alias inside WHERE? Evaluation order. The engine processes FROM, JOIN, WHERE, GROUP BY, HAVING, SELECT, then ORDER BY. WHERE runs before SELECT exists, so the alias is undefined. ORDER BY runs after, which is why sorting by an alias works. That single sentence answers about four different interview questions.

Question 6. Show cities where more than one customer has placed a paid order, with the city average.

SELECT c.city,
       COUNT(DISTINCT c.customer_id) AS paying_customers,
       ROUND(AVG(o.amount), 2)       AS avg_order
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'paid'
GROUP BY c.city
HAVING COUNT(DISTINCT c.customer_id) > 1
ORDER BY avg_order DESC;

Here the status filter genuinely belongs in WHERE, because this is an inner join and refunded rows should go before aggregation. Knowing when the ON clause matters beats applying a rule blindly. Only Pune qualifies.

SQL Window Functions Interview Questions: The Six Patterns That Get Asked

If time is short, spend it here. Most candidates can define window functions but cannot write one under pressure. Six patterns cover nearly every variant: top-N per group, ranking with ties, running totals, LAG or LEAD comparisons, percent of total, and gap detection.

Question 7. Return the most recent order per customer

WITH ranked AS (
  SELECT o.*,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id ORDER BY order_date DESC
         ) AS rn
  FROM orders o
)
SELECT customer_id, order_id, order_date, amount
FROM ranked
WHERE rn = 1;

You cannot filter on rn in the same SELECT, because window functions evaluate after WHERE. That is why the CTE exists, and interviewers ask exactly that follow-up. Expected: order 106 for Aarav, 104 for Diya, 105 for Kabir.

Question 8. Show a running revenue total and each order's percent of total

SELECT order_date,
       amount,
       SUM(amount) OVER (
         ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total,
       ROUND(100.0 * amount / SUM(amount) OVER (), 1) AS pct_of_total
FROM orders
WHERE status = 'paid'
ORDER BY order_date;

The empty OVER () is the whole trick for percent of total. Write the frame clause explicitly on the running total even though it is the default, because the behaviour changes the moment you use RANGE instead of ROWS with duplicate sort keys.

Question 9. Two orders tie for the highest amount. What does each ranking function return?

ROW_NUMBER gives them 1 and 2 in arbitrary order, so re-running the query can change the answer. RANK gives both 1 and makes the next row 3. DENSE_RANK gives both 1 and makes the next row 2. The interviewer is checking whether you would ship a non-deterministic business metric. If ties must break predictably, add a tiebreaker: ORDER BY amount DESC, order_id.

Advanced SQL Interview Questions: CTEs, Self Joins and Gaps

Question 10. Find the second highest paid order amount without LIMIT or OFFSET.

WITH d AS (
  SELECT amount,
         DENSE_RANK() OVER (ORDER BY amount DESC) AS r
  FROM orders
  WHERE status = 'paid'
)
SELECT DISTINCT amount FROM d WHERE r = 2;

DENSE_RANK, not ROW_NUMBER. If two orders tie for highest, ROW_NUMBER would call one of them "second highest", which is wrong. Explaining that choice is the answer; the query is incidental.

Question 11. Which customers ordered in consecutive months?

WITH m AS (
  SELECT DISTINCT customer_id,
         DATE_TRUNC('month', order_date) AS order_month
  FROM orders
  WHERE status = 'paid'
),
gapped AS (
  SELECT customer_id, order_month,
         LAG(order_month) OVER (
           PARTITION BY customer_id ORDER BY order_month
         ) AS prev_month
  FROM m
)
SELECT DISTINCT customer_id
FROM gapped
WHERE DATE_DIFF('month', prev_month, order_month) = 1;

Only Aarav qualifies: February then March. His May order breaks the streak, exactly the detail an interviewer plants on purpose. This shape generalises into the gaps and islands family, and into churn and sessionisation work later, including in Microsoft Fabric and DP-700 pipelines.

Question 12. This query is slow. What do you check? Give a sequence, not a guess: read the plan first with EXPLAIN ANALYZE, look for a sequential scan where you expected an index seek, check whether a function wrapped around a column is preventing index use, then compare estimated against actual rows to spot stale statistics. Rewriting WHERE YEAR(order_date) = 2026 as WHERE order_date >= DATE '2026-01-01' AND order_date < DATE '2027-01-01' is the fix candidates most often miss.

Question 13. When would you not use a CTE? When the optimiser treats it as a fence and stops pushing a filter down, or when you reference it repeatedly and the engine materialises it each time. PostgreSQL materialised CTEs by default until version 12 and now inlines them unless you write MATERIALIZED. Naming that history is a strong signal.

Question 14, Worked End to End: Monthly Cohort Retention

Final rounds rarely ask for a single function. They ask a business question and watch you decompose it. The ask: group customers by the month of their first paid order, then show how many of each cohort returned in each later month. Step one, find each customer's first paid month. Step two, label every paid order with its offset from that month. Step three, count distinct customers per cohort and offset.

WITH first_order AS (
  SELECT customer_id,
         DATE_TRUNC('month', MIN(order_date)) AS cohort_month
  FROM orders
  WHERE status = 'paid'
  GROUP BY customer_id
),
activity AS (
  SELECT o.customer_id,
         f.cohort_month,
         DATE_DIFF('month',
                   f.cohort_month,
                   DATE_TRUNC('month', o.order_date)) AS month_offset
  FROM orders o
  JOIN first_order f ON f.customer_id = o.customer_id
  WHERE o.status = 'paid'
)
SELECT cohort_month,
       month_offset,
       COUNT(DISTINCT customer_id) AS customers
FROM activity
GROUP BY cohort_month, month_offset
ORDER BY cohort_month, month_offset;

Now the part that earns the offer. Offset 2 is missing from the February cohort, not because retention was zero and the query recorded it, but because no row existed to count. The grid has a hole rather than a zero. Chart this and month 2 will not plot as a dip; it will vanish, the line will connect month 1 to month 3, and retention will look better than it was. The fix is a calendar spine: generate the full set of offsets and LEFT JOIN the counts onto it. Say that unprompted and you have shown what the round is for, which is knowing that a correct query can still produce a wrong chart.

Interviewers usually pivot straight from here to "and how would you present it". The PL-300 aligned analyst programme runs SQL and Power BI in the same build so that handoff does not stay two separate skills; our PL-300 certification guide covers the exam side, where Microsoft's published requirement is a scaled score of 700 out of 1000.

Rapid Fire Round: Six More Questions Worth Having Ready

These arrive as quick checks between longer problems. One or two sentences is the right length.

Question 15. UNION or UNION ALL? UNION removes duplicates, forcing a sort or hash across the whole result and costing real time on large tables. UNION ALL concatenates. Default to UNION ALL and reach for UNION only when you need de-duplication.

Question 16. What does a self join solve? Comparing rows within one table, such as an employee table where manager_id points at employee_id. Add that many classic self join questions are now cleaner with LAG or LEAD, which shows you know both.

Question 17. Your result set is far larger than either input table. What happened? Almost always an accidental cartesian product: a missing join condition, or a join key that is not unique on either side. Check COUNT(*) on each table and on the join key before blaming the data.

Question 18. Pivot order status into columns without a PIVOT clause. Conditional aggregation, which is portable everywhere:

SELECT customer_id,
       SUM(CASE WHEN status = 'paid'     THEN amount ELSE 0 END) AS paid,
       SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END) AS refunded
FROM orders
GROUP BY customer_id;

Question 19. View or CTE? A CTE lives for one statement. A view is a stored, named query any statement can reference, so it wins when logic is reused across reports. Neither stores data unless you use a materialised view.

Question 20. A date column is stored as text in mixed formats. How do you clean it? Do not silently cast. Profile first to find how many distinct formats exist, convert each explicitly, and route failures to a quarantine table rather than to NULL. An analyst who turns bad dates into NULLs has hidden a data quality problem instead of reporting it.

How to Prepare for SQL Interview Questions for Data Analyst Roles in Three Weeks

Three weeks is enough if you spend it writing queries rather than reading them.

Two habits matter more than volume. Write the query before running it, on paper if necessary, because the interview gives you no autocomplete and no error message until you commit. And narrate: a candidate who says "I will start from customers so unmatched ones survive, then aggregate orders in a CTE to avoid fan-out" has already passed most of the round.

Build practice questions yourself by asking business questions of the seed schema above, and supplement with public datasets in the BigQuery sandbox. If you want structure and feedback instead of self study, a free 360DT webinar is the cheapest way to test whether a live cohort suits you, and a demo class lets you sit in on a real session first.

Mistakes That Cost Candidates the Offer

Each of these has a visible symptom, so you can catch it yourself while practising.

One more that is not technical: do not claim tools you have not used. If your CV says Snowflake and you have opened it once, an interviewer will find out in ninety seconds.

Once you clear the query round, the stack around SQL shapes the next move. If pipelines interest you more than dashboards, our guide on moving from data analyst to data engineer maps the gap. If model assisted querying interests you, text to SQL is a retrieval problem before it is a generation problem, which puts it in AI engineering and RAG territory, with tool design and structured outputs covered in the CCDV-F developer prep course. Cloud credentials on AWS or Azure matter more once you own the warehouse rather than query it, and the full certifications overview shows how the tracks connect. Still deciding whether analytics is the right move? Start with our switch plan for becoming a data analyst without prior experience.

Frequently asked questions

About this guide. 360 Digital Transformation is an independent training provider. We are not affiliated with the certification bodies, vendors or open source projects mentioned, and our courses are exam preparation rather than official training. Tools and versions change quickly; commands and figures cited were checked on 10 September 2026.