SQL Interview Questions

Q1. What is SQL?

SQL (Structured Query Language) is the standard language for working with relational databases. It is used to read data (SELECT), change it (INSERT, UPDATE, DELETE), define its structure (CREATE, ALTER) and control access. SQL is declarative: you describe the result you want and the database decides how to produce it.

Q2. What is a relational database? Explain tables, rows and columns.

A relational database stores data in tables. Each table describes one kind of thing, each row is one record and each column is one attribute with a data type. Tables are linked through keys. In the SQLabHub practice database, products has 120 rows — one per product:

SELECT id, name, category, price
FROM products
ORDER BY id
LIMIT 3;

▶ Run this query in the SQL Playground

Q3. What are DDL, DML, DQL, DCL and TCL?

They are the categories of SQL commands. DDL (data definition) changes structure: CREATE, ALTER, DROP, TRUNCATE. DML (data manipulation) changes rows: INSERT, UPDATE, DELETE. DQL (data query) reads: SELECT. DCL (data control) manages permissions: GRANT, REVOKE. TCL (transaction control) manages transactions: COMMIT, ROLLBACK, SAVEPOINT.

Q4. What is a primary key?

A primary key is the column (or set of columns) that uniquely identifies each row in a table. It cannot contain NULL and cannot repeat, and a table has only one. In the practice database every table uses an integer id as its primary key.

Q5. What is a foreign key?

A foreign key is a column that refers to the primary key of another table, which is how tables are related. orders.user_id refers to users.id, so each order belongs to one customer and a customer can have many orders. A foreign key constraint stops you inserting an order for a customer that does not exist. See SQL JOINs for how the relationship is used in queries.

Q6. What are constraints in SQL?

Constraints are rules the database enforces on a column or table: NOT NULL (a value is required), UNIQUE (no duplicates), PRIMARY KEY (unique and not null), FOREIGN KEY (must match a row in another table), CHECK (must satisfy a condition) and DEFAULT (value used when none is given). This is how the practice users table is declared:

CREATE TABLE users (
  id         INTEGER PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name  TEXT NOT NULL,
  email      TEXT UNIQUE NOT NULL,
  city       TEXT,
  status     TEXT DEFAULT 'active'
);

Q7. What is NULL, and why does = NULL not work?

NULL means "no value / unknown". It is not zero and not an empty string. Any comparison with NULL using = or <> evaluates to unknown rather than true, so the row is filtered out. Use IS NULL and IS NOT NULL instead:

SELECT
  (SELECT COUNT(*) FROM employees WHERE manager_id IS NULL) AS with_is_null,
  (SELECT COUNT(*) FROM employees WHERE manager_id = NULL)  AS with_equals_null;

▶ Run this query in the SQL Playground

10 employees have no manager, but = NULL finds 0 of them.

Q8. How do you filter rows? Explain SELECT and WHERE.

SELECT lists the columns to return, FROM names the table and WHERE keeps only the rows that satisfy a condition. Conditions can be combined with AND, OR and NOT. More in the SQL WHERE lesson.

SELECT first_name, last_name, salary
FROM employees
WHERE department = 'Engineering'
  AND salary > 150000
ORDER BY salary DESC;

▶ Run this query in the SQL Playground

Q9. What does DISTINCT do?

SELECT DISTINCT removes duplicate rows from the result. With several columns it keeps each unique combination. The 200 orders have only six different statuses. See SELECT DISTINCT.

SELECT DISTINCT status
FROM orders;

▶ Run this query in the SQL Playground

Q10. How does ORDER BY work with more than one column?

Rows are sorted by the first column; the second column only decides the order of rows that tie on the first. Each column can be ASC (default) or DESC. Without ORDER BY the row order is not guaranteed. See ORDER BY.

SELECT name, category, price
FROM products
ORDER BY price DESC, name
LIMIT 5;

▶ Run this query in the SQL Playground

Q11. What is the difference between LIMIT, TOP and FETCH FIRST?

All three return only the first N rows; the syntax depends on the database. MySQL, PostgreSQL and SQLite: LIMIT 5. SQL Server: SELECT TOP 5 …. Oracle 12c+, SQL Server, PostgreSQL (standard SQL): OFFSET 0 ROWS FETCH FIRST 5 ROWS ONLY. Always pair it with ORDER BY, otherwise "first" is arbitrary. See LIMIT, TOP and FETCH.

Q12. What is an alias?

An alias is a temporary name given with AS. A column alias names an output column (SUM(total) AS revenue); a table alias shortens a table name (orders o) and is required when a table is joined to itself. An alias exists only for that query. See aliases.

Q13. What are aggregate functions?

Aggregate functions take many rows and return one value: COUNT, SUM, AVG, MIN and MAX. All except COUNT(*) ignore NULLs. See aggregate functions.

SELECT COUNT(*)             AS orders,
       ROUND(SUM(total), 2) AS revenue,
       ROUND(AVG(total), 2) AS avg_order,
       MIN(total)           AS smallest,
       MAX(total)           AS largest
FROM orders;

▶ Run this query in the SQL Playground

Q14. What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?

COUNT(*) counts rows. COUNT(column) counts rows where that column is not NULL. COUNT(DISTINCT column) counts the different non-NULL values.

SELECT COUNT(*)                AS all_rows,
       COUNT(shipped_date)     AS with_ship_date,
       COUNT(DISTINCT user_id) AS distinct_customers
FROM orders;

▶ Run this query in the SQL Playground

200 orders, of which 103 have a ship date, placed by 114 different customers.

Q15. What does GROUP BY do?

GROUP BY puts rows with the same value into groups and returns one row per group, usually with an aggregate calculated for each. See SQL GROUP BY.

SELECT status,
       COUNT(*) AS orders
FROM orders
GROUP BY status
ORDER BY orders DESC;

▶ Run this query in the SQL Playground

Q16. What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping and cannot contain aggregate functions. HAVING filters groups after aggregation. Here WHERE keeps successful payments and HAVING keeps the methods that collected more than 20,000. See the SQL HAVING clause.

SELECT method,
       ROUND(SUM(amount), 2) AS collected
FROM payments
WHERE status = 'success'
GROUP BY method
HAVING SUM(amount) > 20000
ORDER BY collected DESC;

▶ Run this query in the SQL Playground

Q17. What is a JOIN?

A JOIN combines rows from two tables using a related column. An INNER JOIN returns only the rows that have a match in both tables. See INNER JOIN.

SELECT o.id AS order_id,
       u.first_name,
       u.last_name,
       o.total
FROM orders o
INNER JOIN users u ON u.id = o.user_id
ORDER BY o.total DESC
LIMIT 5;

▶ Run this query in the SQL Playground

Q18. What is a CASE expression?

CASE is SQL's if/else. It checks conditions in order and returns the value for the first one that is true, or the ELSE value (NULL if there is no ELSE). See CASE WHEN.

SELECT CASE
         WHEN price < 500  THEN 'budget'
         WHEN price < 1500 THEN 'mid-range'
         ELSE 'premium'
       END      AS price_band,
       COUNT(*) AS products
FROM products
GROUP BY price_band
ORDER BY products DESC;

▶ Run this query in the SQL Playground

Grouping by the alias price_band works in SQLite, MySQL and PostgreSQL. SQL Server requires the whole CASE expression to be repeated in GROUP BY.

Q19. What do BETWEEN, IN and LIKE do?

BETWEEN a AND b matches a range and includes both ends. IN (…) matches any value in a list. LIKE matches a text pattern, where % is any number of characters and _ is exactly one. See WHERE and IN and LIKE.

SELECT name, category, price
FROM products
WHERE name LIKE '%Coffee%'
  AND price BETWEEN 100 AND 2000
ORDER BY price DESC;

▶ Run this query in the SQL Playground

Q20. What is the difference between DELETE, TRUNCATE and DROP?

DELETE removes the rows that match a WHERE clause, one at a time, and can be rolled back. TRUNCATE removes all rows at once, is faster, usually resets identity counters and cannot take a WHERE clause. DROP removes the table itself, including its structure. SQLite has no TRUNCATE; a DELETE without WHERE is used instead.

Q21. In what order is a SELECT statement logically processed?

FROM and JOIN → WHERE → GROUP BY → HAVING → SELECT (including window functions) → DISTINCT → ORDER BY → LIMIT. This explains why a column alias cannot be used in WHERE and why an aggregate cannot be used in WHERE. It is the logical order; the optimizer may execute the work differently as long as the result is the same.

Q22. What is the difference between CHAR and VARCHAR?

CHAR(n) stores exactly n characters and pads shorter values with spaces; VARCHAR(n) stores up to n characters and uses only the space needed. Use CHAR for fixed-length codes and VARCHAR for everything else. SQLite, which the Playground runs, treats both as TEXT and does not enforce the length. See SQL data types.

Q23. What is the difference between INNER JOIN and LEFT JOIN?

An INNER JOIN returns only matching rows. A LEFT JOIN returns every row from the left table, with NULLs in the right-hand columns where there is no match. The 150 customers and 200 orders show the difference in row counts. See LEFT JOIN.

SELECT
  (SELECT COUNT(*) FROM users u INNER JOIN orders o ON o.user_id = u.id) AS inner_join_rows,
  (SELECT COUNT(*) FROM users u LEFT JOIN  orders o ON o.user_id = u.id) AS left_join_rows;

▶ Run this query in the SQL Playground

The LEFT JOIN returns 36 extra rows: one for each customer who has never ordered.

Q24. What are RIGHT JOIN and FULL OUTER JOIN?

A RIGHT JOIN keeps every row of the right table; it is a LEFT JOIN with the tables swapped. A FULL OUTER JOIN keeps unmatched rows from both sides. PostgreSQL and SQL Server support both; SQLite supports them from version 3.39 (the Playground qualifies); MySQL has RIGHT JOIN but no FULL OUTER JOIN, which is emulated with a LEFT JOIN UNION a RIGHT JOIN. See RIGHT JOIN and FULL OUTER JOIN.

SELECT COUNT(*) AS full_join_rows
FROM users u
FULL OUTER JOIN orders o ON o.user_id = u.id;

▶ Run this query in the SQL Playground

Every order here has a customer, so the FULL OUTER JOIN returns the same rows as the LEFT JOIN in the previous question.

Q25. What is a SELF JOIN? Show each employee with their manager.

A self join joins a table to itself, using two aliases so the same table can play two roles. employees.manager_id points to another row of employees. See SELF JOIN.

SELECT e.first_name || ' ' || e.last_name AS employee,
       m.first_name || ' ' || m.last_name AS manager
FROM employees e
JOIN employees m ON m.id = e.manager_id
ORDER BY e.id
LIMIT 5;

▶ Run this query in the SQL Playground

|| joins strings in SQLite, PostgreSQL and Oracle. MySQL uses CONCAT() and SQL Server uses + or CONCAT().

Q26. What is a CROSS JOIN?

A CROSS JOIN returns every combination of rows from two tables (a Cartesian product), so the row count is the product of the two sizes. It is useful for generating combinations such as every department with every status. See CROSS JOIN.

SELECT COUNT(*) AS combinations
FROM departments
CROSS JOIN (SELECT DISTINCT status FROM orders) AS s;

10 departments × 6 statuses = 60 rows. This output is from SQLite; the SQLabHub Playground rejects CROSS JOIN as too expensive for its sandbox, so there is no run link.

Q27. Why can a JOIN produce duplicate rows and inflate a SUM?

When one row matches several rows in the other table, it is repeated once per match. Joining orders to their line items repeats each order, so summing an order-level column afterwards counts it several times:

SELECT
  (SELECT ROUND(SUM(total), 2) FROM orders) AS true_revenue,
  (SELECT ROUND(SUM(o.total), 2)
   FROM orders o
   JOIN order_items oi ON oi.order_id = o.id) AS inflated_revenue;

▶ Run this query in the SQL Playground

The joined total is 2.5× too high. Fix it by aggregating at the right level — sum the line items, or aggregate each table before joining.

Q28. In a LEFT JOIN, what is the difference between a condition in ON and in WHERE?

A condition in ON decides which right-hand rows match; unmatched left rows are still kept. A condition in WHERE runs after the join, and a test on a right-hand column removes the NULL rows, silently turning the LEFT JOIN into an INNER JOIN.

SELECT
  (SELECT COUNT(DISTINCT u.id) FROM users u
   LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'delivered') AS filter_in_on,
  (SELECT COUNT(DISTINCT u.id) FROM users u
   LEFT JOIN orders o ON o.user_id = u.id
   WHERE o.status = 'delivered') AS filter_in_where;

▶ Run this query in the SQL Playground

With the filter in ON all 150 customers remain; in WHERE only the 65 with a delivered order do.

Q29. How do you find rows in one table with no match in another?

Use an anti-join: LEFT JOIN and keep the rows where the right-hand key is NULL, or use NOT EXISTS. Both find the customers who have never placed an order.

SELECT COUNT(*) AS customers_without_orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

▶ Run this query in the SQL Playground

Q30. How do you join three tables?

Chain the joins, each with its own ON condition. Revenue per category needs order_items (the amounts), products (the category) and orders (the status):

SELECT p.category,
       ROUND(SUM(oi.subtotal), 2) AS delivered_revenue
FROM order_items oi
JOIN products p ON p.id = oi.product_id
JOIN orders o   ON o.id = oi.order_id
WHERE o.status = 'delivered'
GROUP BY p.category
ORDER BY delivered_revenue DESC
LIMIT 5;

▶ Run this query in the SQL Playground

Q31. How do you group by more than one column?

List the columns in GROUP BY; one group is created for each unique combination. Here, orders per year and status:

SELECT strftime('%Y', order_date) AS year,
       status,
       COUNT(*) AS orders
FROM orders
GROUP BY strftime('%Y', order_date), status
ORDER BY year, status
LIMIT 6;

▶ Run this query in the SQL Playground

strftime is SQLite's date function. MySQL uses YEAR(order_date) and PostgreSQL EXTRACT(YEAR FROM order_date) — see SQL date functions.

Q32. How do you find customers with more than N orders?

Group by the customer and filter the groups with HAVING. Customers with four or more orders:

SELECT user_id,
       COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) >= 4
ORDER BY order_count DESC, user_id;

▶ Run this query in the SQL Playground

Q33. Can you select a column that is not in GROUP BY and not aggregated?

Not portably. PostgreSQL, SQL Server and MySQL (with its default ONLY_FULL_GROUP_BY mode) reject the query, because a group has many values for that column. SQLite accepts it and returns the value from an arbitrary row of each group, which is rarely what you want. Every selected column should be in GROUP BY or inside an aggregate.

Q34. How do you find duplicate values in a column?

Group by the column and keep the groups with more than one row. 34 product names are used by more than one product; these are the most repeated:

SELECT name,
       COUNT(*) AS copies
FROM products
GROUP BY name
HAVING COUNT(*) > 1
ORDER BY copies DESC, name
LIMIT 5;

▶ Run this query in the SQL Playground

Q35. What is conditional aggregation?

Putting a CASE expression inside an aggregate so that it counts or sums only some rows. It produces several metrics in one pass and is the standard way to pivot rows into columns.

SELECT shipping_city,
       COUNT(*) AS orders,
       SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
       SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders
GROUP BY shipping_city
ORDER BY orders DESC, shipping_city
LIMIT 5;

▶ Run this query in the SQL Playground

Q36. What is the difference between GROUP BY and DISTINCT?

DISTINCT removes duplicate rows from the output. GROUP BY builds groups so an aggregate can be calculated for each. SELECT DISTINCT status FROM orders and SELECT status FROM orders GROUP BY status return the same rows, but only GROUP BY can add COUNT(*) per status.

Q37. How does GROUP BY treat NULL?

All NULLs are placed together in one group. Grouping employees by manager_id gives a NULL group for the people without a manager:

SELECT manager_id,
       COUNT(*) AS employees
FROM employees
GROUP BY manager_id
ORDER BY manager_id
LIMIT 3;

▶ Run this query in the SQL Playground

SQLite, MySQL and SQL Server sort NULLs first in ascending order; PostgreSQL and Oracle sort them last unless you write NULLS FIRST.

Q38. What is a subquery, and what types are there?

A subquery is a query nested inside another. A scalar subquery returns one value, a column subquery returns a list (used with IN), a table subquery is used in FROM, and a correlated subquery refers to the outer query. This scalar subquery supplies the company average. See SQL subqueries.

SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC
LIMIT 5;

▶ Run this query in the SQL Playground

Q39. What is a correlated subquery?

A subquery that uses a value from the outer query's current row, so it is logically evaluated once per outer row. This one compares each product with the average price of its own category. See correlated subqueries.

SELECT p.name, p.category, p.price
FROM products p
WHERE p.price > (SELECT AVG(p2.price)
                 FROM products p2
                 WHERE p2.category = p.category)
ORDER BY p.price DESC
LIMIT 5;

▶ Run this query in the SQL Playground

Q40. What is the difference between IN and EXISTS?

IN compares a value with a list returned by the subquery. EXISTS only checks whether the subquery returns at least one row and stops at the first match. For large subqueries EXISTS is often the safer choice, and it has no problem with NULLs. See EXISTS and IN vs EXISTS.

SELECT COUNT(*) AS customers_with_delivered_order
FROM users u
WHERE EXISTS (SELECT 1
              FROM orders o
              WHERE o.user_id = u.id
                AND o.status = 'delivered');

▶ Run this query in the SQL Playground

Q41. Why can NOT IN return no rows when the subquery contains NULL?

x NOT IN (a, b, NULL) means x <> a AND x <> b AND x <> NULL. The last comparison is unknown, so the whole condition is never true. Looking for employees who manage nobody shows the trap, because manager_id contains NULLs:

SELECT
  (SELECT COUNT(*) FROM employees
   WHERE id NOT IN (SELECT manager_id FROM employees)) AS with_not_in,
  (SELECT COUNT(*) FROM employees e
   WHERE NOT EXISTS (SELECT 1 FROM employees r
                     WHERE r.manager_id = e.id))        AS with_not_exists;

▶ Run this query in the SQL Playground

NOT IN returns 0; NOT EXISTS returns the correct 110. Use NOT EXISTS, or add WHERE manager_id IS NOT NULL to the subquery.

Q42. When would you use a subquery instead of a JOIN?

Use a JOIN when you need columns from both tables. Use a subquery when you only need to test existence, compare with a single computed value, or pre-aggregate before joining. Many subqueries can be rewritten as joins and the optimizer often treats them alike; choose the form that states the intent most clearly, and check the execution plan if performance matters.

Q43. What is a derived table?

A subquery in the FROM clause. It lets you aggregate in two steps — here, first orders per customer, then the average of those counts:

SELECT ROUND(AVG(order_count), 2) AS avg_orders_per_customer,
       MAX(order_count)           AS most_orders
FROM (SELECT user_id, COUNT(*) AS order_count
      FROM orders
      GROUP BY user_id) AS per_customer;

▶ Run this query in the SQL Playground

Q44. What is a CTE?

A common table expression is a named result defined with WITH and used by the query that follows. It makes a multi-step query readable from top to bottom. See CTEs.

WITH customer_totals AS (
  SELECT user_id,
         COUNT(*)             AS orders,
         ROUND(SUM(total), 2) AS spent
  FROM orders
  GROUP BY user_id
)
SELECT u.first_name, u.last_name, ct.orders, ct.spent
FROM customer_totals ct
JOIN users u ON u.id = ct.user_id
ORDER BY ct.spent DESC
LIMIT 5;

▶ Run this query in the SQL Playground

Q45. Can a query have several CTEs?

Yes. Separate them with commas after a single WITH; a later CTE can use an earlier one. This finds the months whose revenue was above the average month:

WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month,
         SUM(total) AS revenue
  FROM orders
  GROUP BY strftime('%Y-%m', order_date)
),
average AS (
  SELECT AVG(revenue) AS avg_revenue FROM monthly
)
SELECT m.month,
       ROUND(m.revenue, 2) AS revenue,
       (SELECT ROUND(avg_revenue, 2) FROM average) AS average_month
FROM monthly m
WHERE m.revenue > (SELECT avg_revenue FROM average)
ORDER BY m.revenue DESC
LIMIT 5;

▶ Run this query in the SQL Playground

Q46. What is the difference between a CTE, a subquery, a view and a temporary table?

A subquery is inline and anonymous. A CTE is a named subquery that lives for one statement and can be referenced several times. A view is a saved query stored in the database and reusable by anyone with access. A temporary table physically stores rows for the session and can be indexed. See views.

Q47. What is a recursive CTE?

A CTE that refers to itself. It has an anchor query, UNION ALL, and a recursive part that runs until it returns no rows. It is used for hierarchies (org charts, categories) and for generating series such as calendars.

WITH RECURSIVE numbers(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 5
)
SELECT n FROM numbers;

This output is from SQLite, but the SQLabHub Playground blocks WITH RECURSIVE to prevent runaway queries, so there is no run link. SQL Server writes it without the RECURSIVE keyword.

Q48. What is the difference between UNION and UNION ALL?

UNION combines two results and removes duplicate rows; UNION ALL keeps every row and is faster because it skips that step. Both need the same number of columns with compatible types.

SELECT
  (SELECT COUNT(*) FROM (SELECT city FROM users UNION     SELECT city FROM employees)) AS union_rows,
  (SELECT COUNT(*) FROM (SELECT city FROM users UNION ALL SELECT city FROM employees)) AS union_all_rows;

▶ Run this query in the SQL Playground

UNION ALL returns all 270 rows; UNION reduces them to 20 distinct cities.

Q49. How do you group or filter by part of a date?

Extract the part with a date function, which differs by database. SQLite: strftime('%Y-%m', d). MySQL: DATE_FORMAT(d, '%Y-%m') or YEAR(d). PostgreSQL: to_char(d, 'YYYY-MM') or DATE_TRUNC('month', d). SQL Server: FORMAT(d, 'yyyy-MM') or YEAR(d). For filtering, prefer a range on the raw column so an index can be used.

SELECT strftime('%Y-%m', order_date) AS month,
       COUNT(*) AS orders
FROM orders
WHERE order_date >= '2024-10-01'
  AND order_date <  '2025-01-01'
GROUP BY strftime('%Y-%m', order_date)
ORDER BY month;

▶ Run this query in the SQL Playground

Q50. What do COALESCE and NULLIF do?

COALESCE(a, b, …) returns the first argument that is not NULL, which makes it the usual way to supply a default. NULLIF(a, b) returns NULL when the two are equal, most often to avoid division by zero: x / NULLIF(y, 0). See COALESCE and NULLIF.

SELECT id,
       status,
       COALESCE(shipped_date, 'not shipped') AS shipped
FROM orders
ORDER BY id
LIMIT 4;

▶ Run this query in the SQL Playground

Q51. Which string functions should you know?

Changing case (UPPER, LOWER), trimming (TRIM), length (LENGTH; LEN in SQL Server), extracting (SUBSTR / SUBSTRING), searching (INSTR, POSITION, CHARINDEX), replacing (REPLACE) and concatenating. Names differ by database — see SQL string functions.

SELECT UPPER(last_name) || ', ' || first_name   AS display_name,
       SUBSTR(email, INSTR(email, '@') + 1) AS email_domain
FROM users
ORDER BY id
LIMIT 3;

▶ Run this query in the SQL Playground

Q52. How do you delete duplicate rows but keep one?

Number the rows inside each duplicate group with ROW_NUMBER() and delete those numbered above 1. The exact DELETE syntax varies by database; this is the common pattern. The Playground is read-only, so this is shown for reference and has not been run there.

DELETE FROM products
WHERE id IN (
  SELECT id
  FROM (SELECT id,
               ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS rn
        FROM products) AS numbered
  WHERE rn > 1
);

Q53. What is a window function, and how is it different from GROUP BY?

A window function calculates across a set of related rows but keeps every row in the result; GROUP BY collapses the rows into one per group. Here each employee keeps their own row and also gets the department average. See SQL window functions.

SELECT first_name,
       department,
       salary,
       ROUND(AVG(salary) OVER (PARTITION BY department)) AS dept_avg
FROM employees
ORDER BY department, salary DESC
LIMIT 5;

▶ Run this query in the SQL Playground

Q54. What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

ROW_NUMBER gives every row a different number, even for ties. RANK gives ties the same number and then skips (1, 2, 2, 4). DENSE_RANK gives ties the same number without skipping (1, 2, 2, 3). Ranking products by number of reviews shows all three:

WITH review_counts AS (
  SELECT product_id, COUNT(*) AS reviews
  FROM reviews
  GROUP BY product_id
)
SELECT product_id,
       reviews,
       ROW_NUMBER() OVER (ORDER BY reviews DESC, product_id) AS row_num,
       RANK()       OVER (ORDER BY reviews DESC)             AS rnk,
       DENSE_RANK() OVER (ORDER BY reviews DESC)             AS dense_rnk
FROM review_counts
ORDER BY reviews DESC, product_id
LIMIT 7;

▶ Run this query in the SQL Playground

Q55. What do PARTITION BY and ORDER BY do inside OVER()?

PARTITION BY splits the rows into independent windows, like groups that are not collapsed. ORDER BY inside OVER sets the order within each window, which ranking, LAG/LEAD and running totals depend on. An empty OVER () treats the whole result as one window.

Q56. What do LAG and LEAD do?

LAG reads a value from a previous row and LEAD from a following row, in the window's order. They replace a self join for comparisons such as "this month vs last month". The first LAG and the last LEAD are NULL.

WITH monthly AS (
SELECT strftime('%Y-%m', order_date) AS month,
           ROUND(SUM(total), 2) AS revenue
    FROM orders
    WHERE order_date >= '2024-07-01'
    GROUP BY strftime('%Y-%m', order_date)
)
SELECT month,
       revenue,
       LAG(revenue)  OVER (ORDER BY month) AS prev_month,
       LEAD(revenue) OVER (ORDER BY month) AS next_month
FROM monthly
ORDER BY month;

▶ Run this query in the SQL Playground

Q57. How do you calculate a running total?

Use SUM() OVER (ORDER BY …). With an ORDER BY in the window, the sum covers the rows from the start up to the current row.

WITH monthly AS (
SELECT strftime('%Y-%m', order_date) AS month,
           ROUND(SUM(total), 2) AS revenue
    FROM orders
    WHERE order_date >= '2024-07-01'
    GROUP BY strftime('%Y-%m', order_date)
)
SELECT month,
       revenue,
       ROUND(SUM(revenue) OVER (ORDER BY month), 2) AS running_total
FROM monthly
ORDER BY month;

▶ Run this query in the SQL Playground

Q58. How do you calculate a moving average?

Add a frame to the window. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW averages the current row and the two before it — a three-month moving average. The first rows average fewer months because there is nothing earlier.

WITH monthly AS (
SELECT strftime('%Y-%m', order_date) AS month,
           ROUND(SUM(total), 2) AS revenue
    FROM orders
    WHERE order_date >= '2024-07-01'
    GROUP BY strftime('%Y-%m', order_date)
)
SELECT month,
       revenue,
       ROUND(AVG(revenue) OVER (ORDER BY month
                                ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3m
FROM monthly
ORDER BY month;

▶ Run this query in the SQL Playground

Q59. What does NTILE do?

NTILE(n) splits the ordered rows into n buckets of nearly equal size and returns the bucket number. It is used for quartiles and deciles. Customers split into four spending quartiles:

WITH spend AS (
  SELECT user_id, SUM(total) AS spent
  FROM orders
  GROUP BY user_id
),
bucketed AS (
  SELECT user_id, spent,
         NTILE(4) OVER (ORDER BY spent DESC) AS quartile
  FROM spend
)
SELECT quartile,
       COUNT(*)             AS customers,
       ROUND(MIN(spent), 2) AS min_spent,
       ROUND(MAX(spent), 2) AS max_spent
FROM bucketed
GROUP BY quartile
ORDER BY quartile;

▶ Run this query in the SQL Playground

Q60. How do you get the top N rows per group?

Number the rows inside each group with ROW_NUMBER() OVER (PARTITION BY … ORDER BY …) in a CTE, then filter on that number in the outer query. The two highest-paid employees in each department:

WITH ranked AS (
  SELECT department, first_name, last_name, salary,
         ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT department, first_name, last_name, salary
FROM ranked
WHERE rn <= 2
ORDER BY department, salary DESC
LIMIT 6;

▶ Run this query in the SQL Playground

Q61. Why can't you use a window function in WHERE?

Window functions are calculated in the SELECT step, after WHERE, GROUP BY and HAVING have already run. To filter on one, calculate it in a CTE or subquery and filter in the outer query, as in the previous question. Some databases (Snowflake, BigQuery, Teradata) offer a QUALIFY clause for this; MySQL, PostgreSQL, SQL Server and SQLite do not.

Q62. What is the difference between ROWS and RANGE in a window frame?

ROWS counts physical rows. RANGE works on values, so rows that tie in the ORDER BY are treated as one peer group. The default frame when ORDER BY is present is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which means a running total gives tied rows the same value. Write ROWS explicitly when you want a strict row-by-row total.

Q63. Why does LAST_VALUE often return the current row?

Because of the default frame, which ends at the current row — so the "last" value in the frame is the current one. Extend the frame to the end of the partition:

LAST_VALUE(salary) OVER (
  PARTITION BY department
  ORDER BY salary
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Q64. What is an index and how does it speed up a query?

An index is a separate, sorted structure (usually a B-tree) that maps column values to rows, so the database can find matching rows without scanning the whole table. It speeds up filtering, joining and sorting on the indexed columns, at the cost of extra storage and slower writes, because every INSERT, UPDATE and DELETE must also update the index. See SQL indexes.

Q65. What is the difference between a clustered and a non-clustered index?

A clustered index defines the physical order of the table's rows, so a table can have only one; in SQL Server and MySQL InnoDB the primary key is clustered by default. A non-clustered (secondary) index is a separate structure that points back to the rows, and a table can have many. PostgreSQL does not keep tables clustered automatically.

Q66. When is an index not used?

Common reasons: the column is wrapped in a function (WHERE YEAR(order_date) = 2024), the pattern starts with a wildcard (LIKE '%son'), there is an implicit type conversion, the condition is not selective enough so a scan is cheaper, or a composite index is used without its leading column. A condition that can use an index is called sargable; rewriting the first example as a date range makes it sargable.

Q67. What is a composite index, and does column order matter?

A composite index covers several columns. Order matters: an index on (user_id, order_date) helps queries that filter on user_id, or on both, but not queries that filter only on order_date. Put the columns used for equality first, then the range or sort column.

Q68. What is an execution plan?

The steps the database chooses to run a query: which indexes it uses, the join order and join algorithms, and estimated row counts. You read it with EXPLAIN (MySQL, PostgreSQL), EXPLAIN ANALYZE for real timings (PostgreSQL), EXPLAIN QUERY PLAN (SQLite) or the graphical plan in SQL Server. Look for full table scans on large tables and for estimates far from the actual row counts.

Q69. How would you optimize a slow query?

Read the execution plan first. Then: select only the columns you need; filter early with sargable conditions; index the columns used in WHERE, JOIN and ORDER BY; avoid functions on indexed columns; replace correlated subqueries that run per row with a join or window function where the plan shows it helps; aggregate before joining to avoid row multiplication; and check that statistics are up to date. Measure before and after each change.

Q70. What is a transaction?

A group of statements treated as one unit: either all of them take effect (COMMIT) or none do (ROLLBACK). A transfer between two accounts is the classic case — the debit and the credit must both succeed. See SQL transactions.

Q71. What are the ACID properties?

Atomicity: all statements in the transaction succeed or none do. Consistency: the transaction moves the database from one valid state to another, with all constraints satisfied. Isolation: concurrent transactions do not see each other's unfinished work. Durability: once committed, the change survives a crash.

Q72. What are the transaction isolation levels?

From weakest to strongest: READ UNCOMMITTED allows dirty reads; READ COMMITTED prevents dirty reads but allows non-repeatable reads; REPEATABLE READ prevents those but, in the standard, allows phantom rows; SERIALIZABLE behaves as if transactions ran one after another. Stronger levels give fewer anomalies and more blocking or retries. The default is READ COMMITTED in PostgreSQL, SQL Server and Oracle, and REPEATABLE READ in MySQL InnoDB.

Q73. What is a deadlock and how do you prevent it?

A deadlock happens when two transactions each hold a lock the other needs, so neither can continue. The database detects it and rolls one back. To reduce deadlocks: access tables and rows in the same order everywhere, keep transactions short, index the columns you filter on so fewer rows are locked, and retry the transaction that was rolled back.

Q74. What is a view, and what is a materialized view?

A view is a saved SELECT that you query like a table; it stores no data and runs its query each time. A materialized view stores the result physically and must be refreshed, trading freshness for speed. PostgreSQL and Oracle have materialized views; SQL Server has indexed views; MySQL and SQLite have neither. See SQL views.

Q75. What is the difference between a stored procedure and a function?

A stored procedure is a saved program called with CALL or EXEC; it can change data, manage transactions and return several result sets. A function returns a value (or a table) and can be used inside a SELECT, and in most databases it should not change data. SQLite supports neither. See stored procedures.

Q76. What is a trigger?

A trigger is code the database runs automatically before or after an INSERT, UPDATE or DELETE on a table. Triggers are used for audit logs and for enforcing rules that constraints cannot express. They are easy to overuse: hidden side effects make behaviour hard to follow and slow down writes. See SQL triggers.

Q77. What is normalization? Explain 1NF, 2NF and 3NF.

Normalization organises tables to remove redundancy and update anomalies. 1NF: each column holds a single value and there are no repeating groups. 2NF: 1NF, and every non-key column depends on the whole primary key, not part of a composite key. 3NF: 2NF, and non-key columns depend only on the key, not on other non-key columns. The practice database keeps customers, orders and order lines in separate tables for this reason.

Q78. What is denormalization and when is it justified?

Denormalization deliberately stores redundant data to make reads faster, for example keeping a pre-computed total on a row or copying a name into a fact table. It is justified for read-heavy reporting and analytics, when joins are measurably too slow. The cost is that the copies must be kept consistent. In the practice data, employees stores the department name as well as department_id.

Q79. What is the difference between OLTP and OLAP?

OLTP systems handle many small, fast transactions (orders, payments), are highly normalized and are optimised for writes. OLAP systems answer analytical questions over large volumes, are usually denormalized into star schemas and are optimised for reads and aggregation. Data analysts mostly query OLAP-style warehouses.

Q80. What is SQL injection and how is it prevented?

SQL injection happens when user input is concatenated into a SQL string and is executed as code. Prevent it with parameterized queries (prepared statements), which send the query and the values separately, plus least-privilege database accounts and input validation. Never build SQL by joining strings with user input.

Q81. How do you calculate monthly revenue?

Group by the month and sum the order value, deciding first which orders count. Here cancelled and refunded orders are excluded. First half of 2024:

SELECT strftime('%Y-%m', order_date) AS month,
       COUNT(*)             AS orders,
       ROUND(SUM(total), 2) AS revenue
FROM orders
WHERE order_date >= '2024-01-01'
  AND order_date <  '2024-07-01'
  AND status NOT IN ('cancelled', 'refunded')
GROUP BY strftime('%Y-%m', order_date)
ORDER BY month;

▶ Run this query in the SQL Playground

Q82. How do you calculate month-over-month growth?

Calculate monthly revenue in a CTE, read the previous month with LAG, and divide the change by the previous value. Multiply by 100.0, not 100, to avoid integer division.

WITH monthly AS (
SELECT strftime('%Y-%m', order_date) AS month,
           ROUND(SUM(total), 2) AS revenue
    FROM orders
    WHERE order_date >= '2024-07-01'
    GROUP BY strftime('%Y-%m', order_date)
)
SELECT month,
       revenue,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
                   / LAG(revenue) OVER (ORDER BY month), 1) AS mom_growth_pct
FROM monthly
ORDER BY month;

▶ Run this query in the SQL Playground

The first month has no previous month, so its growth is NULL rather than zero.

Q83. How do you calculate year-over-year growth?

The same pattern at year level: total per year, then compare each year with the one before.

WITH yearly AS (
  SELECT strftime('%Y', order_date) AS year,
         ROUND(SUM(total), 2) AS revenue
  FROM orders
  GROUP BY strftime('%Y', order_date)
)
SELECT year,
       revenue,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY year))
                   / LAG(revenue) OVER (ORDER BY year), 1) AS yoy_growth_pct
FROM yearly
ORDER BY year;

▶ Run this query in the SQL Playground

Q84. How do you calculate average order value (AOV)?

AOV is revenue divided by the number of orders. State the population — here, all orders per year.

SELECT strftime('%Y', order_date) AS year,
       COUNT(*)                         AS orders,
       ROUND(SUM(total) / COUNT(*), 2)  AS avg_order_value
FROM orders
GROUP BY strftime('%Y', order_date)
ORDER BY year;

▶ Run this query in the SQL Playground

Q85. How do you find the repeat-customer rate?

Count orders per customer, then divide the customers with two or more orders by all customers who ordered.

WITH per_customer AS (
  SELECT user_id, COUNT(*) AS orders
  FROM orders
  GROUP BY user_id
)
SELECT COUNT(*) AS customers,
       SUM(CASE WHEN orders >= 2 THEN 1 ELSE 0 END) AS repeat_customers,
       ROUND(100.0 * SUM(CASE WHEN orders >= 2 THEN 1 ELSE 0 END) / COUNT(*), 1) AS repeat_rate_pct
FROM per_customer;

▶ Run this query in the SQL Playground

Q86. How do you calculate customer retention and churn?

Define the two periods, then check which customers from the first period appear in the second. Customers who ordered in 2023: how many ordered again in 2024? Churn is the share that did not.

WITH y2023 AS (
  SELECT DISTINCT user_id FROM orders WHERE strftime('%Y', order_date) = '2023'
),
y2024 AS (
  SELECT DISTINCT user_id FROM orders WHERE strftime('%Y', order_date) = '2024'
)
SELECT COUNT(*)             AS customers_2023,
       COUNT(y2024.user_id) AS retained_in_2024,
       ROUND(100.0 * COUNT(y2024.user_id) / COUNT(*), 1)              AS retention_pct,
       ROUND(100.0 * (COUNT(*) - COUNT(y2024.user_id)) / COUNT(*), 1) AS churn_pct
FROM y2023
LEFT JOIN y2024 ON y2024.user_id = y2023.user_id;

▶ Run this query in the SQL Playground

COUNT(y2024.user_id) counts only the matched rows, because the LEFT JOIN leaves NULL for customers who did not return.

Q87. How do you list churned customers?

Find each customer's most recent order with MAX(order_date) and keep those whose last order is before a cut-off date. 58 customers have not ordered since the end of 2023; these are the longest-inactive:

SELECT u.first_name, u.last_name,
       MAX(o.order_date) AS last_order
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.first_name, u.last_name
HAVING MAX(o.order_date) < '2024-01-01'
ORDER BY last_order
LIMIT 5;

▶ Run this query in the SQL Playground

Q88. How do you calculate customer lifetime value?

In its simplest form, lifetime value is the total a customer has spent on orders that count as revenue. Exclude cancelled and refunded orders.

SELECT u.first_name, u.last_name,
       COUNT(o.id)            AS orders,
       ROUND(SUM(o.total), 2) AS lifetime_value,
       ROUND(AVG(o.total), 2) AS avg_order
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status NOT IN ('cancelled', 'refunded')
GROUP BY u.id, u.first_name, u.last_name
ORDER BY lifetime_value DESC
LIMIT 5;

▶ Run this query in the SQL Playground

Q89. How do you build a cohort analysis?

Assign each customer to a cohort by the period of their first order, then count how many customers of each cohort were active in each later period. Cohorts by year of first order:

WITH first_order AS (
  SELECT user_id,
         MIN(strftime('%Y', order_date)) AS cohort_year
  FROM orders
  GROUP BY user_id
)
SELECT f.cohort_year,
       strftime('%Y', o.order_date) AS order_year,
       COUNT(DISTINCT o.user_id)    AS active_customers
FROM first_order f
JOIN orders o ON o.user_id = f.user_id
GROUP BY f.cohort_year, strftime('%Y', o.order_date)
ORDER BY f.cohort_year, order_year;

▶ Run this query in the SQL Playground

Read it by cohort: of the 52 customers whose first order was in 2022, 15 ordered again in 2023 and 13 in 2024.

Q90. How do you build a funnel in SQL?

Count how many records reach each stage with conditional aggregation, then divide by the first stage. An order funnel from placed to delivered:

SELECT COUNT(*) AS orders_placed,
       SUM(CASE WHEN status IN ('shipped', 'delivered') THEN 1 ELSE 0 END) AS shipped,
       SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END)                AS delivered,
       ROUND(100.0 * SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) / COUNT(*), 1) AS delivered_pct
FROM orders;

▶ Run this query in the SQL Playground

Q91. How do you calculate a conversion or success rate per group?

Divide the successful rows by all rows inside each group. Payment success rate by method:

SELECT method,
       COUNT(*) AS attempts,
       SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS successful,
       ROUND(100.0 * SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) / COUNT(*), 1) AS success_rate_pct
FROM payments
GROUP BY method
ORDER BY success_rate_pct DESC;

▶ Run this query in the SQL Playground

Q92. How do you calculate each category's percentage of total revenue?

Divide each group's total by the grand total. A window function over the grouped result, SUM(SUM(x)) OVER (), gives the grand total without a second query.

SELECT p.category,
       ROUND(SUM(oi.subtotal), 2) AS revenue,
       ROUND(100.0 * SUM(oi.subtotal) / SUM(SUM(oi.subtotal)) OVER (), 1) AS pct_of_total
FROM order_items oi
JOIN products p ON p.id = oi.product_id
GROUP BY p.category
ORDER BY revenue DESC;

▶ Run this query in the SQL Playground

Q93. How do you segment customers by spending?

Total each customer's spending, then bucket the totals with CASE and aggregate again.

WITH spend AS (
  SELECT user_id, SUM(total) AS spent
  FROM orders
  GROUP BY user_id
)
SELECT CASE
         WHEN spent >= 10000 THEN 'high'
         WHEN spent >= 4000  THEN 'medium'
         ELSE 'low'
       END                  AS segment,
       COUNT(*)             AS customers,
       ROUND(SUM(spent), 2) AS revenue
FROM spend
GROUP BY segment
ORDER BY revenue DESC;

▶ Run this query in the SQL Playground

Q94. Why does a percentage calculation sometimes return 0?

Integer division. When both operands are integers, many databases (SQLite, PostgreSQL, SQL Server) discard the fraction, so 7 / 2 is 3 and a ratio below 1 becomes 0. Make one operand a decimal: multiply by 1.0 or 100.0, or use CAST. MySQL's / returns a decimal.

SELECT 7 / 2   AS integer_division,
       7 / 2.0 AS decimal_division;

▶ Run this query in the SQL Playground

Q95. Find the second-highest salary.

Take the highest salary that is lower than the overall highest. This handles ties at the top correctly. An alternative is SELECT DISTINCT salary … ORDER BY salary DESC LIMIT 1 OFFSET 1.

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

▶ Run this query in the SQL Playground

Q96. Find the nth-highest salary.

Rank the salaries with DENSE_RANK, which does not skip numbers after ties, and filter on the rank. The third-highest:

WITH ranked AS (
  SELECT first_name, last_name, salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT first_name, last_name, salary
FROM ranked
WHERE rnk = 3;

▶ Run this query in the SQL Playground

Q97. Find duplicate records and show which rows they are.

Group by the columns that define a duplicate and list the ids in each group.

SELECT name,
       COUNT(*)         AS copies,
       GROUP_CONCAT(id) AS product_ids
FROM products
GROUP BY name
HAVING COUNT(*) > 1
ORDER BY copies DESC, name
LIMIT 3;

▶ Run this query in the SQL Playground

GROUP_CONCAT exists in SQLite and MySQL. PostgreSQL and SQL Server use STRING_AGG(id, ','); Oracle uses LISTAGG.

Q98. Find employees who earn more than their department's average.

Calculate the average per department in a derived table and join it back. 59 of the 120 employees qualify; the top five:

SELECT e.first_name, e.last_name, e.department, e.salary
FROM employees e
JOIN (SELECT department, AVG(salary) AS avg_salary
      FROM employees
      GROUP BY department) d ON d.department = e.department
WHERE e.salary > d.avg_salary
ORDER BY e.salary DESC
LIMIT 5;

▶ Run this query in the SQL Playground

Q99. Find the highest-paid employee in each department.

Rank employees inside each department and keep rank 1. Using a window function returns the employee's name, which a plain MAX(salary) … GROUP BY cannot.

WITH ranked AS (
  SELECT department, first_name, last_name, salary,
         ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT department, first_name, last_name, salary
FROM ranked
WHERE rn = 1
ORDER BY department;

▶ Run this query in the SQL Playground

Q100. Find the top 3 salaries in each department.

Use DENSE_RANK so that tied salaries share a rank, and keep ranks 1 to 3. Shown for two departments:

WITH ranked AS (
  SELECT department, first_name, last_name, salary,
         DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
  FROM employees
  WHERE department IN ('Design', 'Product')
)
SELECT department, first_name, last_name, salary, rnk
FROM ranked
WHERE rnk <= 3
ORDER BY department, rnk;

▶ Run this query in the SQL Playground

Q101. Find customers who never placed an order.

LEFT JOIN customers to orders and keep the rows with no match. 36 of the 150 customers have no orders; the first five:

SELECT u.id, u.first_name, u.last_name
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL
ORDER BY u.id
LIMIT 5;

▶ Run this query in the SQL Playground

Q102. Find the first order of every customer.

Number each customer's orders by date and keep number 1. This returns the whole first order, not just its date.

WITH numbered AS (
  SELECT user_id, id AS order_id, order_date, total,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date, id) AS rn
  FROM orders
)
SELECT user_id, order_id, order_date, total
FROM numbered
WHERE rn = 1
ORDER BY user_id
LIMIT 5;

▶ Run this query in the SQL Playground

Q103. Find the latest order of every customer.

The same pattern with the order reversed. The id in the ORDER BY breaks ties when a customer has two orders on the same date.

WITH numbered AS (
  SELECT user_id, id AS order_id, order_date, status,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC, id DESC) AS rn
  FROM orders
)
SELECT user_id, order_id, order_date, status
FROM numbered
WHERE rn = 1
ORDER BY user_id
LIMIT 5;

▶ Run this query in the SQL Playground

Q104. Find days on which orders were placed on consecutive dates.

Reduce the data to distinct order dates, read the previous date with LAG, and keep pairs exactly one day apart. julianday is SQLite; use DATEDIFF in MySQL and SQL Server, or subtract dates in PostgreSQL.

WITH days AS (
  SELECT DISTINCT order_date FROM orders
),
with_prev AS (
  SELECT order_date,
         LAG(order_date) OVER (ORDER BY order_date) AS prev_date
  FROM days
)
SELECT prev_date AS first_day, order_date AS next_day
FROM with_prev
WHERE julianday(order_date) - julianday(prev_date) = 1
ORDER BY order_date
LIMIT 5;

▶ Run this query in the SQL Playground

Q105. Find the longest periods with no orders (missing dates).

The gap between two consecutive order dates, minus one, is the number of missing days between them. Sort by the gap to find the longest quiet periods.

WITH days AS (
  SELECT DISTINCT order_date FROM orders
),
with_prev AS (
  SELECT order_date,
         LAG(order_date) OVER (ORDER BY order_date) AS prev_date
  FROM days
)
SELECT prev_date  AS last_order_before_gap,
       order_date AS next_order,
       CAST(julianday(order_date) - julianday(prev_date) AS INTEGER) - 1 AS days_without_orders
FROM with_prev
WHERE prev_date IS NOT NULL
ORDER BY days_without_orders DESC, order_date
LIMIT 5;

▶ Run this query in the SQL Playground

Q106. Find the average gap between a customer's orders.

Use LAG partitioned by customer to get the previous order date, then average the differences. Shown for customers with at least four orders:

WITH gaps AS (
  SELECT user_id,
         julianday(order_date)
           - julianday(LAG(order_date) OVER (PARTITION BY user_id ORDER BY order_date)) AS days_since_prev
  FROM orders
)
SELECT user_id,
       COUNT(*)                    AS orders,
       ROUND(AVG(days_since_prev)) AS avg_days_between_orders
FROM gaps
GROUP BY user_id
HAVING COUNT(*) >= 4
ORDER BY avg_days_between_orders;

▶ Run this query in the SQL Playground

AVG ignores the NULL produced for each customer's first order, so the average is over the real gaps only.

Q107. Find products that have never been sold.

LEFT JOIN products to order lines and keep the products with no match. In the practice data, 1 of the 120 products has no sales:

SELECT p.id, p.name, p.category
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE oi.id IS NULL
ORDER BY p.id
LIMIT 5;

▶ Run this query in the SQL Playground

Q108. Find the best-selling product in each category.

Total the units per product, rank the products inside each category, and keep rank 1. RANK returns more than one product for a category when there is a tie.

WITH sales AS (
  SELECT p.category, p.id, p.name, SUM(oi.quantity) AS units
  FROM order_items oi
  JOIN products p ON p.id = oi.product_id
  GROUP BY p.category, p.id, p.name
),
ranked AS (
  SELECT category, name, units,
         RANK() OVER (PARTITION BY category ORDER BY units DESC) AS rnk
  FROM sales
)
SELECT category, name, units
FROM ranked
WHERE rnk = 1
ORDER BY category
LIMIT 5;

▶ Run this query in the SQL Playground

Q109. Find the top-selling product in each month.

The same pattern with the month as the partition. Last quarter of 2024, by units sold:

WITH sales AS (
  SELECT strftime('%Y-%m', o.order_date) AS month,
         p.name,
         SUM(oi.quantity) AS units
  FROM order_items oi
  JOIN orders o   ON o.id = oi.order_id
  JOIN products p ON p.id = oi.product_id
  WHERE o.order_date >= '2024-10-01'
  GROUP BY strftime('%Y-%m', o.order_date), p.id, p.name
),
ranked AS (
  SELECT month, name, units,
         ROW_NUMBER() OVER (PARTITION BY month ORDER BY units DESC, name) AS rn
  FROM sales
)
SELECT month, name, units
FROM ranked
WHERE rn = 1
ORDER BY month;

▶ Run this query in the SQL Playground

Q110. Find employees who earn more than their manager.

Self join each employee to their manager and compare the two salaries. 63 employees out-earn their manager; the largest differences:

SELECT e.first_name || ' ' || e.last_name AS employee,
       e.salary,
       m.first_name || ' ' || m.last_name AS manager,
       m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.salary > m.salary
ORDER BY e.salary - m.salary DESC
LIMIT 5;

▶ Run this query in the SQL Playground

Q111. Pivot order counts by status into columns, one row per year.

Use conditional aggregation: one SUM(CASE …) per column you want. It works in every database, unlike vendor-specific PIVOT syntax.

SELECT strftime('%Y', order_date) AS year,
       SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
       SUM(CASE WHEN status = 'shipped'   THEN 1 ELSE 0 END) AS shipped,
       SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
       SUM(CASE WHEN status = 'refunded'  THEN 1 ELSE 0 END) AS refunded
FROM orders
GROUP BY strftime('%Y', order_date)
ORDER BY year;

▶ Run this query in the SQL Playground

Q112. Find the median salary.

Number the rows in salary order and average the middle one (odd count) or the middle two (even count). PostgreSQL and Oracle also offer PERCENTILE_CONT(0.5).

WITH ordered AS (
  SELECT salary,
         ROW_NUMBER() OVER (ORDER BY salary) AS rn,
         COUNT(*)     OVER ()                AS n
  FROM employees
)
SELECT AVG(salary) AS median_salary
FROM ordered
WHERE rn IN ((n + 1) / 2, (n + 2) / 2);

▶ Run this query in the SQL Playground

The integer division is deliberate: for 120 rows it selects rows 60 and 61.

Q113. Find customers who ordered in every year.

Count the distinct years per customer and compare with the number of years in the data.

SELECT u.first_name, u.last_name,
       COUNT(DISTINCT strftime('%Y', o.order_date)) AS years_active,
       COUNT(*) AS orders
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.first_name, u.last_name
HAVING COUNT(DISTINCT strftime('%Y', o.order_date)) =
       (SELECT COUNT(DISTINCT strftime('%Y', order_date)) FROM orders)
ORDER BY orders DESC, u.last_name
LIMIT 5;

▶ Run this query in the SQL Playground

Q114. Which five shipping cities generated the most revenue from delivered orders?

SELECT shipping_city,
       COUNT(*)             AS delivered_orders,
       ROUND(SUM(total), 2) AS revenue
FROM orders
WHERE status = 'delivered'
GROUP BY shipping_city
ORDER BY revenue DESC
LIMIT 5;

Q115. Which products have never been reviewed?

SELECT p.id, p.name
FROM products p
LEFT JOIN reviews r ON r.product_id = p.id
WHERE r.id IS NULL
ORDER BY p.id
LIMIT 5;

Q116. What is the average review rating per product category, for categories with at least 15 reviews?

SELECT p.category,
       COUNT(*)                AS reviews,
       ROUND(AVG(r.rating), 2) AS avg_rating
FROM reviews r
JOIN products p ON p.id = r.product_id
GROUP BY p.category
HAVING COUNT(*) >= 15
ORDER BY avg_rating DESC;

Q117. How many employees were hired each year, and what was the cumulative headcount?

SELECT strftime('%Y', hire_date) AS year,
       COUNT(*) AS hires,
       SUM(COUNT(*)) OVER (ORDER BY strftime('%Y', hire_date)) AS cumulative_headcount
FROM employees
GROUP BY strftime('%Y', hire_date)
ORDER BY year;

Q118. Which customers spent more in 2024 than in 2023 (having ordered in both years)?

SELECT u.first_name, u.last_name,
       ROUND(SUM(CASE WHEN strftime('%Y', o.order_date) = '2023' THEN o.total ELSE 0 END), 2) AS spent_2023,
       ROUND(SUM(CASE WHEN strftime('%Y', o.order_date) = '2024' THEN o.total ELSE 0 END), 2) AS spent_2024
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.first_name, u.last_name
HAVING SUM(CASE WHEN strftime('%Y', o.order_date) = '2023' THEN o.total ELSE 0 END) > 0
   AND SUM(CASE WHEN strftime('%Y', o.order_date) = '2024' THEN o.total ELSE 0 END) >
       SUM(CASE WHEN strftime('%Y', o.order_date) = '2023' THEN o.total ELSE 0 END)
ORDER BY spent_2024 - spent_2023 DESC
LIMIT 5;

Q119. For each customer, find the date of their second order.

WITH numbered AS (
  SELECT user_id, order_date,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date, id) AS rn
  FROM orders
)
SELECT user_id, order_date AS second_order_date
FROM numbered
WHERE rn = 2
ORDER BY user_id
LIMIT 5;

Q120. What share of successfully collected money does each payment method account for?

SELECT method,
       ROUND(SUM(amount), 2) AS collected,
       ROUND(100.0 * SUM(amount) / SUM(SUM(amount)) OVER (), 1) AS share_pct
FROM payments
WHERE status = 'success'
GROUP BY method
ORDER BY collected DESC;

Q121. Which customers have at least two orders, all of them delivered?

SELECT u.first_name, u.last_name,
       COUNT(*) AS orders
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.first_name, u.last_name
HAVING COUNT(*) >= 2
   AND SUM(CASE WHEN o.status = 'delivered' THEN 1 ELSE 0 END) = COUNT(*)
ORDER BY orders DESC, u.last_name, u.first_name;