29 questions · 10 topics
SQL interview questions for data analysts
Last reviewed
Basics & filtering
- Easy
In what order does SQL logically evaluate the clauses of a SELECT query?
Logically, SQL runs FROM and JOINs first, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY and finally LIMIT. That is why a column alias defined in SELECT cannot be used in WHERE, but can usually be used in ORDER BY. The engine may physically reorder work, but results must match this logical order.
Read the full answer → - Easy
How do BETWEEN, IN and LIKE behave, and what are their common pitfalls?
BETWEEN is inclusive at both ends, IN tests membership in a list, and LIKE matches patterns with % (any characters) and _ (one character). The classic traps are dates stored with a time part making BETWEEN miss the last day, case sensitivity differing between databases for LIKE, and NOT IN returning nothing when the list contains a NULL.
Read the full answer →
Joins
- Easy
Explain INNER, LEFT, RIGHT, FULL OUTER and CROSS joins.
An INNER JOIN keeps only rows that match on both sides. A LEFT JOIN keeps every row from the left table and fills NULLs where the right side has no match; RIGHT is the mirror image. FULL OUTER keeps unmatched rows from both sides. A CROSS JOIN pairs every row with every row, producing a Cartesian product.
Read the full answer → - Easy
How would you find customers who have never placed an order?
Use an anti-join: LEFT JOIN orders onto customers and keep rows where the order key IS NULL, or use NOT EXISTS with a correlated subquery. Both return customers with no matching order. NOT EXISTS is usually the clearest and is safe with NULLs, whereas NOT IN breaks if the subquery returns any NULL value.
Read the full answer → - Medium
In a LEFT JOIN, what is the difference between putting a filter in ON and in WHERE?
A condition in ON decides which right-side rows match, so unmatched left rows are still kept with NULLs. The same condition in WHERE runs after the join and discards those NULL rows, quietly turning the LEFT JOIN into an INNER JOIN. Put right-table filters in ON when you want to keep every left-side row.
Read the full answer → - Medium
How do you use a self-join to show each employee alongside their manager?
Join the employees table to itself under two aliases, matching the employee's manager_id to the manager's employee_id. Use a LEFT JOIN so the person at the top of the hierarchy, whose manager_id is NULL, is still listed. The same pattern compares rows within one table, such as employees earning more than their manager.
Read the full answer → - Medium
Why can joining tables inflate totals, and how do you prevent it?
When you join a one-row-per-order table to a many-rows-per-order table, each order repeats once per item, so any order-level value you sum or count gets multiplied. This is called fan-out. Prevent it by aggregating the many side first in a subquery or CTE, by counting DISTINCT keys, or by summing only columns that live at the joined grain.
Read the full answer →
Aggregation & GROUP BY
- Easy
What is the difference between WHERE and HAVING?
WHERE filters individual rows before they are grouped, so it cannot reference aggregates such as SUM or COUNT. HAVING filters groups after GROUP BY has run, so it can. Put row-level conditions in WHERE, which also reduces the work done, and use HAVING only for conditions on aggregated values like total revenue or order count.
Read the full answer → - Easy
What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?
COUNT(*) counts rows, whatever they contain. COUNT(column) counts only rows where that column is not NULL. COUNT(DISTINCT column) counts unique non-NULL values. Choosing the wrong one is a frequent source of quietly wrong metrics, especially after LEFT JOINs, where padded rows contain NULLs but still count towards COUNT(*).
Read the full answer → - Medium
How do you pivot rows into columns using conditional aggregation?
Wrap a CASE expression inside an aggregate: SUM(CASE WHEN condition THEN 1 ELSE 0 END) counts matching rows, and SUM(CASE WHEN condition THEN value END) totals them. One GROUP BY then produces several metrics side by side. It works in every SQL dialect, unlike PIVOT syntax, and avoids running separate queries for each category.
Read the full answer →
Subqueries & CTEs
- Medium
Find products priced above the average price of their own category. What is a correlated subquery?
A correlated subquery references a column from the outer query, so it is conceptually re-evaluated for every outer row. Here the subquery computes the average price for the current product's category, and the outer WHERE keeps products above it. The same result can be produced with a window function or a join to a pre-aggregated CTE, which often reads better.
Read the full answer → - Medium
What is a CTE and when would you use one instead of a subquery?
A Common Table Expression is a named, temporary result set defined with WITH at the start of a query. Use CTEs to break a long query into readable steps, to reuse the same intermediate result more than once, and for recursion. Logically they behave like subqueries; whether they are materialised or inlined depends on the database engine.
Read the full answer → - Hard
How would you list the full reporting chain of an organisation with a recursive CTE?
A recursive CTE has an anchor member, such as the employee with no manager, unioned with a recursive member that joins the CTE back to the table to fetch the next level down. It repeats until no new rows appear. Track a depth counter and a path string to show each person's level and chain of command.
Read the full answer →
Window functions
- Medium
What is the difference between ROW_NUMBER, RANK and DENSE_RANK?
All three number rows within a window in a given order; they differ only on ties. ROW_NUMBER gives unique numbers, breaking ties arbitrarily. RANK gives tied rows the same number and then skips, so 1, 1, 3. DENSE_RANK gives ties the same number without gaps, so 1, 1, 2. Choose based on how the business wants ties treated.
Read the full answer → - Medium
How do you calculate a running total of monthly revenue?
First aggregate revenue to one row per month, then apply SUM(revenue) OVER (ORDER BY month) to the result. With an ORDER BY inside OVER, the window runs from the first row up to the current row, giving a cumulative total. Add PARTITION BY to restart the running total for each year, region or category.
Read the full answer → - Medium
How would you compute month-over-month growth using LAG?
Aggregate to one row per month, then use LAG(revenue) OVER (ORDER BY month) to bring the previous month's value onto the current row. Growth is (current − previous) ÷ previous. Guard against division by zero with NULLIF, and remember the first month has no previous value, so its growth is NULL rather than zero.
Read the full answer → - Medium
How do you return the top two products by revenue within each category?
Compute revenue per product, rank products within each category using ROW_NUMBER or DENSE_RANK partitioned by category and ordered by revenue descending, then filter to rank two or less in an outer query. The filter must sit outside, because window functions are evaluated after WHERE. Use DENSE_RANK if tied products should both appear.
Read the full answer → - Medium
How do you get each customer's most recent order (or de-duplicate rows)?
Number each customer's orders with ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC, order_id DESC) and keep the rows numbered 1. The partition defines the group, the ORDER BY defines which row wins, and the extra tie-breaker makes the result deterministic. The same pattern removes duplicate records while keeping the newest copy.
Read the full answer →
Dates
- Easy
How do you group by month and measure time between two dates in SQL?
Truncate the date to a month and group on that: strftime('%Y-%m', d) in SQLite, DATE_TRUNC('month', d) in PostgreSQL. For durations, subtract dates as day numbers, for example julianday(end) − julianday(start) in SQLite or end − start in PostgreSQL. Always exclude rows where either date is NULL, or understand that the result will be NULL.
Read the full answer → - Medium
How would you find customers who have not ordered in the last 90 days?
Compute each customer's last order date with MAX(order_date), then keep customers whose last order is earlier than the reference date minus 90 days. Anchor the reference date explicitly, to a parameter or the dataset's latest date, rather than today's date, so the query is reproducible. Decide whether customers with no orders at all should be included.
Read the full answer →
NULL handling
- Medium
Why does WHERE column = NULL return nothing, and why can NOT IN silently return zero rows?
NULL means unknown, so any comparison with it, including NULL = NULL, evaluates to unknown rather than true, and WHERE keeps only true rows. Use IS NULL or IS NOT NULL instead. NOT IN expands to a chain of not-equal checks; if the list contains a NULL, every check becomes unknown and no row qualifies. Prefer NOT EXISTS.
Read the full answer → - Easy
How do COALESCE and NULLIF help when handling NULLs and division by zero?
COALESCE returns the first non-NULL argument, so it supplies defaults, for example labelling a missing referral source as 'Unknown' or treating no sales as zero. NULLIF(a, b) returns NULL when a equals b, which is mainly used as NULLIF(denominator, 0) so that division by zero produces NULL instead of an error or a misleading result.
Read the full answer →
Performance & indexing
- Medium
What is an index, how does it speed up queries, and what does it cost?
An index is a separate, sorted structure, usually a B-tree, that maps column values to row locations, letting the database seek directly to matching rows instead of scanning the whole table. It helps filters, joins and ordering on the indexed columns. The cost is extra storage and slower inserts, updates and deletes, because every index must be maintained.
Read the full answer → - Hard
A report query is slow. How would you go about optimising it?
Start by reading the execution plan rather than guessing. Then reduce the data early: filter before joining, select only needed columns, and aggregate before joins that fan out. Keep predicates index-friendly by not wrapping indexed columns in functions, add or adjust indexes for the real filter and join columns, and consider pre-aggregated summary tables for heavy dashboards.
Read the full answer →
Data modelling
- Medium
What is normalisation, and why are orders and order_items separate tables?
Normalisation organises data so each fact is stored once, reducing duplication and update anomalies. Orders and order items are separate because one order contains many products: order-level facts such as date and status live once in orders, while each product line lives in order_items with a foreign key back to its order. Analytics models sometimes denormalise deliberately for speed.
Read the full answer → - Medium
What are fact and dimension tables in a star schema?
A fact table records measurable events at a declared grain, such as one row per order line with quantity and amount, plus foreign keys. Dimension tables describe the context of those events: customer, product, date and channel attributes used for filtering and grouping. Arranging one fact around several dimensions forms a star, which keeps BI queries simple and fast.
Read the full answer →
Business case queries
- Hard
How would you measure customer retention by acquisition cohort?
Assign each customer to a cohort by the month or quarter of their first order, then check whether they ordered again within a fixed window, such as 90 days. Retention is returning customers divided by cohort size. Use a fixed window so older cohorts are not favoured simply because they have had more time, and exclude cohorts too recent to have completed the window.
Read the full answer → - Medium
How would you build a conversion funnel from session data?
Count sessions reaching each stage, from product view to cart, checkout and purchase, then compute two rates: step conversion, each stage divided by the previous one, and overall conversion, each stage divided by the top of the funnel. Breaking the funnel down by traffic source or device shows where users drop off and which channel is under-performing.
Read the full answer → - Hard
How would you compare revenue year over year by month?
Aggregate revenue by year and month, then place each month's value next to the same month of the previous year, either with conditional aggregation into side-by-side columns or with LAG over a partition by month number. YoY growth is (this year − last year) ÷ last year. Only compare periods present in both years, so partial months or years do not distort the result.
Read the full answer →