SQL interview questions, answered the way you’d say them.

SQL commands are grouped by what they do. DDL defines structure (CREATE, ALTER, DROP, TRUNCATE), DML changes rows (INSERT, UPDATE, DELETE) and DQL reads them (SELECT). DCL controls access with GRANT and REVOKE, and TCL manages transactions with COMMIT, ROLLBACK and SAVEPOINT. Manuals draw the lines slightly differently: MySQL’s, for one, files SELECT under data manipulation.

Expect a follow-up on TRUNCATE: it removes every row, yet MySQL’s manual lists it with the data definition statements, not DML.

MySQL 8.4 manual: SQL Statements

Practise it with Prism

There are four main kinds. Arithmetic (+, -, *, /, %) and comparison (=, <>, <, >, plus BETWEEN, IN, LIKE and IS NULL) work on values; logical operators, AND, OR and NOT, combine conditions using three-valued logic, so a comparison with NULL is unknown, not true or false. Set operators, UNION, INTERSECT and EXCEPT, combine whole query results.

Know the precedence: AND binds tighter than OR, so a OR b AND c means a OR (b AND c); add brackets when you mean otherwise.

PostgreSQL 18 docs: Logical OperatorsPostgreSQL 18 docs: Comparison Functions and OperatorsPostgreSQL 18 docs: Combining Queries (UNION, INTERSECT, EXCEPT)PostgreSQL 18 docs: Lexical Structure (operator precedence)

Practise it with Prism

In a LIKE pattern, % matches any run of characters, including none, and _ matches exactly one. So '_%r%_' needs at least one character before the r and one after it, which keeps r away from both ends. PostgreSQL’s LIKE is case-sensitive and ILIKE isn’t, while MySQL ignores case by default; to match a literal % or _, put the escape character, a backslash by default, in front.

SELECT customer_id, name
FROM customers
WHERE name ILIKE '_%r%_';      -- r or R, with a character on each side

-- A literal % sign, escaped with the default backslash
SELECT promo_code FROM promos WHERE description LIKE '%50\% off%';

A pattern that starts with % can’t use an ordinary B-tree index, so expect a full scan on a big table.

PostgreSQL 18 docs: Pattern MatchingMySQL 8.4 manual: String Comparison Functions and OperatorsPostgreSQL 18 docs: Index Types

Practise it with Prism

Both read a value from another row in the same partition, following the window’s order, with no self join. LAG(col, n, default) looks n rows back and LEAD looks n rows ahead; n defaults to 1, and you get NULL, or your default, when no such row exists. I use LAG to compare with the previous period, as in month-on-month growth, and LEAD for what comes next, such as the date of a customer’s next order.

SELECT customer_id, order_date,
       LAG(order_date)  OVER w AS previous_order,
       LEAD(order_date) OVER w AS next_order
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY order_date);

Neither knows about calendars: LAG returns the previous row, which is last month only if no month is missing.

PostgreSQL 18 docs: Window Functions

Practise it with Prism

SQL is the standard language, ISO/IEC 9075, for defining and querying relational databases. A DBMS is any software that stores and manages data; an RDBMS organises it into related tables with a fixed schema, enforces constraints such as foreign keys, and supports ACID transactions. NoSQL stores, such as document, key-value, wide-column and graph databases, trade some of that rigidity for flexible schemas and easier horizontal scaling.

Don’t claim NoSQL can’t do transactions: MongoDB, for one, supports multi-document ACID transactions.

PostgreSQL 18 docs: SQL ConformanceMicrosoft Learn: Understand data models (Azure Architecture Center)MongoDB docs: Transactions

Practise it with Prism

I’d filter with WHERE on status, payment mode and a half-open date range, on or after 1 October and before 1 November, then sort with ORDER BY order_date DESC. I avoid MONTH(order_date) = 10: the range is just as readable, stays correct if the column ever holds timestamps, and leaves any index on order_date usable.

SELECT order_id, customer_id, order_date, amount
FROM orders
WHERE status = 'delivered'
  AND payment_mode = 'UPI'
  AND order_date >= '2026-10-01'
  AND order_date <  '2026-11-01'
ORDER BY order_date DESC, order_id DESC;

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

WHERE filters individual rows before any grouping happens, while HAVING filters whole groups after aggregation, which is why only HAVING can use COUNT, SUM and the other aggregates. Put a condition in WHERE whenever it doesn’t need an aggregate, so fewer rows reach the grouping step.

Show both in one query, such as cities with at least 2 delivered orders: the status check goes in WHERE and the count in HAVING.

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

COUNT(*) counts every row. COUNT(column) counts only the rows where that column isn’t NULL, and COUNT(DISTINCT column) counts how many different non-NULL values it holds. On the example customers table, that gives 7 customers, 3 who were referred and 6 cities.

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

Group the delivered orders by payment_mode, then use COUNT(*) for the orders and SUM(amount) for revenue. The rule to say out loud: every column in SELECT is either in the GROUP BY or inside an aggregate. PostgreSQL, and MySQL in its default ONLY_FULL_GROUP_BY mode, both refuse a query that breaks it.

SELECT payment_mode,
       COUNT(*)    AS orders,
       SUM(amount) AS revenue
FROM orders
WHERE status = 'delivered'
GROUP BY payment_mode
ORDER BY revenue DESC;

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

NULL stands for an unknown value, and comparing anything with unknown gives unknown, never true, so no row passes. Use IS NULL or IS NOT NULL instead. The same logic catches referred_by <> 1: it skips the NULL rows too, so add OR referred_by IS NULL if you want them.

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

Use COALESCE, which returns the first of its arguments that isn’t NULL. For example, COALESCE(SUM(o.amount), 0) after a LEFT JOIN shows 0 for customers with no delivered orders instead of NULL. Keep the arguments the same type, since PostgreSQL rejects a number and text together in one COALESCE.

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

Use a CASE expression. It checks each WHEN in turn and returns the first one that’s true, so order matters: test amount < 1000 before amount < 2500, and let ELSE take everything else. To count orders per band, group by that same CASE expression.

SELECT order_id, amount,
       CASE
         WHEN amount < 1000 THEN 'Small'
         WHEN amount < 2500 THEN 'Medium'
         ELSE 'Large'
       END AS size_band
FROM orders;

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

DISTINCT removes duplicate rows from a result, while GROUP BY collapses rows into groups so you can aggregate each one. For a plain list of unique values, such as the cities customers live in, both return the same thing. The moment you need a count or a total per value, you need GROUP BY.

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

Both stack two results that have the same number of columns. UNION additionally strips out duplicate rows, which needs a sort or a hash, while UNION ALL returns every row, so it’s quicker. I use UNION ALL by default and switch to UNION only when I actually need the duplicates gone.

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

A primary key is the row’s identity: unique, never NULL, and only one per table. A unique key also stops duplicates, yet it accepts NULLs and you can have several on one table. A foreign key ties a column to another table’s key, so you can’t record, say, a payment against an order that doesn’t exist. Keys can span columns too, like (order_id, product_id) in order_items.

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

DELETE removes the rows you pick and can be rolled back; TRUNCATE quickly empties the whole table but keeps it; DROP removes the table itself. Worth adding the dialect detail: in MySQL, TRUNCATE causes an implicit commit, so it can’t be rolled back, while in PostgreSQL it’s transactional and can be.

A DELETE can also be blocked by a foreign key: deleting the cancelled orders in the example fails because order_items still points at order 1003.

MySQL 8.4 manual: TRUNCATE TABLE StatementPostgreSQL 18 docs: TRUNCATE

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

ORDER BY price DESC LIMIT 5 works in MySQL and PostgreSQL; SQL Server uses TOP instead. Say out loud that LIMIT stops at exactly five rows, even when the sixth product costs the same as the fifth. To keep those ties, PostgreSQL offers FETCH FIRST 5 ROWS WITH TIES, and in either database a ranking window function can do it too.

SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 5;

-- PostgreSQL, keeping ties for 5th place
SELECT product_name, price
FROM products
ORDER BY price DESC
FETCH FIRST 5 ROWS WITH TIES;

PostgreSQL 18 docs: SELECT

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

A join pairs every match, so a key that appears m times on one side and n times on the other produces m × n rows. NULL never equals anything, not even another NULL, so a NULL key never matches. In the example, key 1 gives 2 × 3 = 6 rows, which is the inner join; a left join adds each unmatched left row once, making 8, a right join makes 7 and a full join 9.

left_t.k:   1, 1, 2, NULL          right_t.k:   1, 1, 1, 3
INNER JOIN   key 1 only: 2 × 3                     = 6 rows
LEFT JOIN    6 + unmatched left rows (2, NULL)     = 8 rows
RIGHT JOIN   6 + unmatched right row (3)           = 7 rows
FULL JOIN    6 + 2 + 1                             = 9 rows

In real work, compare row counts before and after a join: an unexpected jump usually means a key you assumed was unique isn’t.

PostgreSQL 18 docs: Table Expressions (joined tables)PostgreSQL 18 docs: Comparison Functions and Operators

Practise it with Prism

Three families, all written with OVER and all keeping every row, unlike GROUP BY. Aggregate functions such as SUM, AVG and COUNT give running totals or a group’s total beside each row. Ranking functions, ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK and CUME_DIST, place each row within its partition, and value functions, LAG, LEAD, FIRST_VALUE, LAST_VALUE and NTH_VALUE, fetch a value from another row.

SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date)  AS running_total,   -- aggregate
       RANK()      OVER (ORDER BY amount DESC) AS amount_rank,     -- ranking
       LAG(amount) OVER (ORDER BY order_date)  AS previous_amount  -- value
FROM orders;

LAST_VALUE trips people up: with ORDER BY, the default frame ends at the current row and its ties, so widen it with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

PostgreSQL 18 docs: Window Functions

Practise it with Prism

Compare each day with the calendar day before it. A self join on y.record_date = w.record_date - 1 does exactly that, and a day whose previous date is missing simply finds no partner. LAG is shorter, but it returns the previous row, so after a gap it compares with an older day; if you use it, also check that the previous row’s date is one day earlier.

SELECT w.record_date, w.temperature
FROM weather w
JOIN weather y ON y.record_date = w.record_date - 1
WHERE w.temperature > y.temperature;

-- With LAG, checking that the previous row really is the day before
SELECT record_date, temperature FROM (
  SELECT record_date, temperature, LAG(record_date) OVER (ORDER BY record_date) AS prev_date,
         LAG(temperature) OVER (ORDER BY record_date) AS prev_temp FROM weather) t
WHERE temperature > prev_temp AND record_date = prev_date + 1;

Ask whether dates can be missing or repeated before you write it: that decides between the self join and LAG.

PostgreSQL 18 docs: Date/Time Functions and OperatorsPostgreSQL 18 docs: Window Functions

Practise it with Prism

Number the rows inside each group of matching values with ROW_NUMBER() OVER (PARTITION BY … ORDER BY …): the first copy gets 1, and every row numbered 2 or more is a duplicate. Unlike GROUP BY with HAVING COUNT(*) > 1, this keeps each row whole, ids and all, so you can review exactly which copies would go. The ORDER BY decides which copy counts as the original, such as the earliest sign-up.

WITH numbered AS (
  SELECT customer_id, email,
         ROW_NUMBER() OVER (PARTITION BY LOWER(TRIM(email)) ORDER BY customer_id) AS rn
  FROM customers
)
SELECT customer_id, email
FROM numbered
WHERE rn > 1;      -- the extra copies; rn = 1 is the one to keep

To remove them in PostgreSQL, put the same CTE before DELETE FROM customers WHERE customer_id IN (SELECT customer_id FROM numbered WHERE rn > 1).

PostgreSQL 18 docs: Window FunctionsPostgreSQL 18 docs: DELETE

Practise it with Prism

Normalisation organises tables so each fact is stored once, avoiding redundancy and the inconsistencies of updating it in several places. In 1NF every value is atomic, with no repeating groups; in 2NF every non-key column depends on the whole key; in 3NF non-key columns depend only on the key, not on each other; BCNF tightens this so anything that determines another column is a superkey. The cost is more tables and joins, which is why reporting models often denormalise dimensions on purpose.

order_lines(order_id, product_id, customer_id, city, product_name, qty)   key: (order_id, product_id)
2NF breaks: customer_id needs only order_id, product_name only product_id  ->  move them out
3NF breaks: city depends on customer_id, a non-key column                   ->  move it out
Result:  orders(order_id, customer_id)       customers(customer_id, city)
         products(product_id, product_name)  order_items(order_id, product_id, qty)

Asked to normalise a table on the spot, name its key first, then test each column: does it depend on the whole key, and on nothing but the key?

IBM Db2 13 for z/OS docs: Normalization in database designSilberschatz, Korth and Sudarshan, Database System Concepts, 7th ed.: chapter 7 slides (relational design)Microsoft Learn: Understand star schema and the importance for Power BI

Practise it with Prism

Start from the entities and how they relate: customers, restaurants, menu items, orders, order lines, payments and delivery partners. Give each table a primary key, link them with foreign keys, and resolve many-to-many links with a junction table: order_items joins orders to menu items and records the quantity and the price paid. Then prove the design by writing a report on it, such as revenue by restaurant.

CREATE TABLE customers   (customer_id INT PRIMARY KEY, name TEXT, city TEXT);
CREATE TABLE restaurants (restaurant_id INT PRIMARY KEY, name TEXT, city TEXT);
CREATE TABLE menu_items  (item_id INT PRIMARY KEY, restaurant_id INT REFERENCES restaurants,
                          name TEXT, price NUMERIC(8,2));
CREATE TABLE orders      (order_id INT PRIMARY KEY, customer_id INT REFERENCES customers,
                          restaurant_id INT REFERENCES restaurants, ordered_at TIMESTAMPTZ, status TEXT);
CREATE TABLE order_items (order_id INT REFERENCES orders, item_id INT REFERENCES menu_items,
                          quantity INT CHECK (quantity > 0), unit_price NUMERIC(8,2),
                          PRIMARY KEY (order_id, item_id));

Store the price paid on each order line, not just on the menu, or every menu price change rewrites past revenue.

PostgreSQL 18 docs: Constraints

Practise it with Prism

It’s a pattern for layering data in a lakehouse by quality. Bronze keeps raw data as it arrived, so you can reprocess and audit it; silver holds it cleaned, de-duplicated and validated; gold holds business-level tables, often aggregated, ready for dashboards, reports and models. Each layer is built from the one before, so quality improves step by step, and both Databricks and Microsoft Fabric document it as their recommended design.

Say where you’d put the data-quality checks, such as on the way into silver, so bad rows are caught before anyone reports on them.

Databricks docs: What is the medallion lakehouse architecture?Microsoft Learn: Implement medallion lakehouse architecture in Fabric

Practise it with Prism

A cumulative sum, or running total, adds each row’s value to the total of all the rows before it. Without a window function, a correlated subquery can sum every row up to the current date, or a self join on b.order_date <= a.order_date with GROUP BY does the same. Both re-add the earlier rows for every row, about n²/2 additions for n days, which is why SUM() OVER (ORDER BY order_date) is the better choice where it’s available.

SELECT a.order_date, a.revenue,
       (SELECT SUM(b.revenue) FROM daily_revenue b
        WHERE b.order_date <= a.order_date) AS running_total
FROM daily_revenue a
ORDER BY a.order_date;

-- or a self join
SELECT a.order_date, SUM(b.revenue) AS running_total
FROM daily_revenue a JOIN daily_revenue b ON b.order_date <= a.order_date
GROUP BY a.order_date ORDER BY a.order_date;

A common follow-up is a running total per customer: add the customer to the subquery’s condition, or to PARTITION BY in the window version.

PostgreSQL 18 docs: Value Expressions (scalar subqueries)

Practise it with Prism

Atomicity means all of a transaction happens or none of it does; consistency, that it leaves the data valid under every constraint; isolation, that concurrent transactions don’t see each other’s uncommitted work; and durability, that committed changes survive a crash. In the textbook model, a transaction is active while running and partially committed after its last statement, then ends committed, or failed and then aborted, rolled back to how the database was before it started.

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;
COMMIT;      -- both changes stick; after an error, ROLLBACK undoes both

Isolation has levels: PostgreSQL defaults to Read Committed, while MySQL’s InnoDB defaults to Repeatable Read.

PostgreSQL 18 docs: Glossary (ACID)Silberschatz, Korth and Sudarshan, Database System Concepts, 7th ed.: chapter 17 slides (transaction states)PostgreSQL 18 docs: Transaction IsolationMySQL 8.4 manual: Transaction Isolation Levels

Practise it with Prism

Use conditional aggregation: AVG(CASE WHEN gender = 'F' THEN salary END) averages only women’s salaries, because the CASE returns NULL for everyone else and AVG uses only non-null values. Do the same for men, then compare the two columns with another CASE that handles ties and departments where a group is missing. Add that a raw gap doesn’t show unequal pay for equal work, since role and seniority may differ.

WITH g AS (
  SELECT department_id,
         ROUND(AVG(CASE WHEN gender = 'F' THEN salary END), 0) AS avg_f,
         ROUND(AVG(CASE WHEN gender = 'M' THEN salary END), 0) AS avg_m
  FROM employees
  GROUP BY department_id
)
SELECT department_id, avg_f, avg_m,
       CASE WHEN avg_f > avg_m THEN 'F' WHEN avg_m > avg_f THEN 'M' ELSE 'Tie or no data' END AS higher
FROM g;

Show the headcount beside each average: in a small department, one very high salary can decide the answer.

PostgreSQL 18 docs: Aggregate Functions

Practise it with Prism

Logically, SQL runs FROM and the joins first, then WHERE, GROUP BY, HAVING, SELECT (where window functions run), DISTINCT, ORDER BY and finally LIMIT. That explains why a SELECT alias works in ORDER BY but not in WHERE, and why filtering on a window function needs a subquery or CTE. One dialect detail: PostgreSQL accepts aliases in GROUP BY but not in HAVING, while MySQL accepts both.

PostgreSQL 18 docs: SELECT

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

An INNER JOIN returns only the rows that match on both sides. A LEFT JOIN returns every left-table row, with NULLs where nothing matched on the right; a RIGHT JOIN is the same thing reversed, and most people just write it as a LEFT JOIN. A FULL OUTER JOIN returns everything from both tables, but MySQL lacks it, so there you add the unmatched RIGHT JOIN rows to a LEFT JOIN.

In MySQL, stack the two with UNION ALL, keeping only the RIGHT JOIN rows where the left side is NULL.

MySQL 8.4 manual: JOIN Clause

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

It’s an anti join: you want the customers that have no partner row in orders. I’d write it either as a LEFT JOIN that keeps the rows where the order side is NULL, or with NOT EXISTS; on the example data both return customers 6 and 7. I’d stay away from NOT IN when the column can contain NULL, because one NULL from the subquery makes it return nothing at all.

SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

Putting a condition on the right-hand table in WHERE discards the NULL rows the LEFT JOIN just preserved, so it ends up acting like an inner join. Move that condition into ON, for example AND o.status = 'delivered', and customers with no delivered order stay in, showing 0. Count o.order_id, not *, or they’d show 1.

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

Use a self join: the same table twice under two aliases, one for the employee and one for the manager. For the list I’d use a LEFT JOIN, so the head of the company, who has no manager, still appears. For the comparison, an inner join with WHERE e.salary > m.salary does it; on the example data that’s Kunal Verma, on 16 lakh, above Ishaan Gupta’s 14 lakh.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.emp_id = e.manager_id;

SELECT e.name, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.emp_id = e.manager_id
WHERE e.salary > m.salary;

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

Cut each date down to its month, then group by it. Dates are where MySQL and PostgreSQL differ most: PostgreSQL uses DATE_TRUNC('month', order_date), MySQL uses DATE_FORMAT(order_date, '%Y-%m-01'), and EXTRACT(MONTH FROM order_date) works in both. Two more pairs, MySQL then PostgreSQL: days between dates are DATEDIFF(later, earlier) or later - earlier, and 30 days ago is CURRENT_DATE - INTERVAL 30 DAY or CURRENT_DATE - INTERVAL '30 days'.

-- PostgreSQL
SELECT DATE_TRUNC('month', order_date)::date AS month,
       SUM(amount) AS revenue
FROM orders
WHERE status = 'delivered'
  AND order_date >= '2026-01-01' AND order_date < '2027-01-01'
GROUP BY 1
ORDER BY 1;

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

A single order can mix categories, so category revenue must be built from the order lines rather than the order totals. Join order_items to orders, to keep only delivered ones, and to products, for the category. Then sum quantity * unit_price, the price actually paid, for each category.

SELECT p.category,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders   o ON o.order_id   = oi.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status = 'delivered'
GROUP BY p.category
ORDER BY revenue DESC;

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

That’s fan-out: once orders are joined to their lines, every order appears once per line, so SUM(o.amount) adds a two-line order in twice. Sum each table at its own grain instead, either order totals from orders alone or line values from order_items. Don’t paper over it with DISTINCT.

In the example data, orders 1001 and 1004 have two lines each, so they are the ones counted twice.

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

A subquery is a query nested inside another, and a correlated one refers to the outer row; a CTE is a named result set you declare with WITH, so the logic reads from top to bottom. Both get the same answer here, but the CTE is simpler to read and debug. You need MySQL 8.0 or later for CTEs, and since PostgreSQL inlines a CTE that’s referenced once, there’s usually no performance penalty.

WITH category_avg AS (
  SELECT category, AVG(price) AS avg_price
  FROM products
  GROUP BY category
)
SELECT p.product_name, p.category, p.price
FROM products p
JOIN category_avg ca ON ca.category = p.category
WHERE p.price > ca.avg_price;

MySQL 8.0 manual: What Is New in MySQL 8.0PostgreSQL 18 docs: WITH Queries (Common Table Expressions)

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

Both check membership, in different ways. IN compares a value with a list, while EXISTS only checks whether a correlated subquery finds at least one row, so it can stop at the first match. Modern optimisers often plan the two the same way, so I choose for correctness: NOT EXISTS is safe with NULLs, and NOT IN isn’t.

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

Normalise the email first, then group on that cleaned value and keep any group of more than one row with HAVING COUNT(*) > 1. One address can turn up in different case or with a stray space, so use LOWER(TRIM(email)): MySQL’s default collation ignores case already but PostgreSQL’s doesn’t, and this makes both give the same answer. On the example data it finds one address used by customers 1 and 6.

SELECT LOWER(TRIM(email)) AS email_clean,
       COUNT(*)           AS sign_ups
FROM customers
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

Use conditional aggregation: SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END) counts just the cancelled orders, and dividing that by COUNT(*) gives each city’s share. Write 100.0 rather than 100: PostgreSQL truncates integer-by-integer division, while MySQL’s / returns the decimals.

SELECT c.city,
       COUNT(*) AS orders,
       ROUND(100.0 * SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END)
             / COUNT(*), 1) AS cancelled_pct
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
GROUP BY c.city
ORDER BY cancelled_pct DESC;

PostgreSQL 18 docs: Mathematical Functions and OperatorsMySQL 8.4 manual: Arithmetic Operators

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

Neither MySQL nor PostgreSQL has a PIVOT keyword, so I’d put a CASE inside SUM, one column per payment mode, and group by city; that works in both. In PostgreSQL, FILTER (WHERE …) is a neater alternative for each column, but it returns NULL rather than 0 when nothing matches.

SELECT c.city,
       SUM(CASE WHEN o.payment_mode = 'UPI'  THEN o.amount ELSE 0 END) AS upi,
       SUM(CASE WHEN o.payment_mode = 'Card' THEN o.amount ELSE 0 END) AS card,
       SUM(CASE WHEN o.payment_mode = 'COD'  THEN o.amount ELSE 0 END) AS cod
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'delivered'
GROUP BY c.city;

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

Filter to the two months, group by customer, and keep the customers whose orders fall in two different months, with HAVING COUNT(DISTINCT EXTRACT(MONTH FROM order_date)) = 2. On the example data, that’s customers 1 and 2. INTERSECT of the two months’ customers works too, in PostgreSQL and in MySQL from 8.0.31.

SELECT customer_id
FROM orders
WHERE order_date >= '2026-09-01' AND order_date < '2026-11-01'
GROUP BY customer_id
HAVING COUNT(DISTINCT EXTRACT(MONTH FROM order_date)) = 2;

MySQL 8.0 manual: INTERSECT Clause

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

All three are window functions: they calculate across rows related to the current row and, unlike GROUP BY, keep every row. They differ only on ties: ROW_NUMBER never repeats, RANK repeats a number and then skips, and DENSE_RANK repeats without skipping. So two salaries tied for third get 3 and 4 from ROW_NUMBER, 3 and 3 then 5 from RANK, and 3 and 3 then 4 from DENSE_RANK.

In MySQL 8, don’t name a column rank: it’s a reserved word.

PostgreSQL 18 docs: Window FunctionsMySQL 8.4 manual: Keywords and Reserved Words

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

Three ways work. MAX(salary) below the overall maximum returns NULL when there’s no second value; DENSE_RANK handles ties and becomes any Nth by changing one number; LIMIT 1 OFFSET 1 needs DISTINCT, or a tie at the top breaks it. Offer more than one and say which edge cases each handles; on the example data, all three return 1800000.

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

SELECT DISTINCT salary AS second_highest
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 2;

The two edge cases to name: a tie for the top salary, and a table holding just one distinct salary.

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

In PostgreSQL, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY marks) with GROUP BY class_id, sub_id does it, and it ignores NULL marks. Without that function, as in MySQL, number the rows inside each group with ROW_NUMBER, count them with COUNT(*) OVER, and average the middle row, or the middle two when the count is even. Filter out NULL marks first, or they shift the middle.

WITH ranked AS (
  SELECT class_id, sub_id, marks,
         ROW_NUMBER() OVER (PARTITION BY class_id, sub_id ORDER BY marks) AS rn,
         COUNT(*)     OVER (PARTITION BY class_id, sub_id) AS n
  FROM marks WHERE marks IS NOT NULL
)
SELECT class_id, sub_id, AVG(marks) AS median_marks
FROM ranked
WHERE rn IN ((n + 1) / 2, (n + 2) / 2)   -- the middle row, or the middle two
GROUP BY class_id, sub_id;

In MySQL, work out the middle positions with DIV: its / returns a decimal, so with an even count (n + 1) / 2 gives 2.5 and the lower middle row is missed.

PostgreSQL 18 docs: Aggregate FunctionsPostgreSQL 18 docs: Window FunctionsMySQL 8.4 manual: Arithmetic Operators

Practise it with Prism

Two ranges overlap when each starts before the other ends: a.start_date < b.end_date AND b.start_date < a.end_date. Self-join the table on the room, and add a.booking_id < b.booking_id so each clashing pair appears once and no booking matches itself. Agree first whether the end date is included: if a guest still holds the room on that day, use <= instead.

SELECT a.booking_id, b.booking_id AS clashes_with, a.room_id
FROM bookings a
JOIN bookings b
  ON  b.room_id    = a.room_id
  AND a.booking_id < b.booking_id
  AND a.start_date < b.end_date
  AND b.start_date < a.end_date;

PostgreSQL also has (start1, end1) OVERLAPS (start2, end2), which treats each period as half-open, the same as the test above.

PostgreSQL 18 docs: Date/Time Functions and Operators (OVERLAPS)

Practise it with Prism

Rank inside each department with DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) in a CTE, then keep the rows where the rank is 2. PARTITION BY is what restarts the ranking for every department. Changing the filter to rnk <= 3 gives the top three earners in each department instead.

WITH ranked AS (
  SELECT department, name, salary,
         DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT department, name, salary
FROM ranked
WHERE rnk = 2;

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

This is the standard top-N-per-group pattern: total the revenue per product, number the rows within each PARTITION BY category, then filter on that number in an outer query, since WHERE can’t see a window function. ROW_NUMBER gives exactly N rows and DENSE_RANK keeps ties, so tell the interviewer which one you picked.

WITH product_revenue AS (
  SELECT p.category, p.product_id, p.product_name, SUM(oi.quantity * oi.unit_price) AS revenue
  FROM order_items oi
  JOIN orders o ON o.order_id = oi.order_id JOIN products p ON p.product_id = oi.product_id
  WHERE o.status = 'delivered' GROUP BY p.category, p.product_id, p.product_name
), ranked AS (
  SELECT category, product_name, revenue,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC, product_id) AS rn
  FROM product_revenue)
SELECT category, product_name, revenue FROM ranked WHERE rn <= 3 ORDER BY category, rn;

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

First show exactly what will go: a self join on the cleaned email, LOWER(TRIM(email)), that matches each customer to one with a lower id. Then delete those rows, with DELETE … USING in PostgreSQL or DELETE c FROM … JOIN in MySQL. Mention that in real work you’d first move the duplicate’s orders to the customer you keep, or the foreign key will stop you; that earns marks.

-- See what will go
SELECT c.*
FROM customers c
JOIN customers k
  ON LOWER(TRIM(k.email)) = LOWER(TRIM(c.email))
 AND k.customer_id < c.customer_id;

-- PostgreSQL
DELETE FROM customers c USING customers k
WHERE LOWER(TRIM(k.email)) = LOWER(TRIM(c.email)) AND k.customer_id < c.customer_id;

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

Roll the orders up to one row per day first, then use SUM(revenue) OVER (ORDER BY order_date). With ORDER BY and no frame, the window runs from the first row to the current one and includes ties, so two rows with the same date would each show the total including both; that’s why the daily roll-up comes first. Add PARTITION BY to restart the total, for example each month.

WITH daily AS (
  SELECT order_date, SUM(amount) AS revenue
  FROM orders
  WHERE status = 'delivered'
  GROUP BY order_date
)
SELECT order_date, revenue,
       SUM(revenue) OVER (ORDER BY order_date) AS running_total
FROM daily
ORDER BY order_date;

The default frame with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which is why rows on the same date share a total.

MySQL 8.4 manual: Window Function Frame Specification

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

AVG(revenue) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) covers the last seven rows, not seven days, so when some days have no orders the window reaches back more than a week. To fix it, join to a calendar holding every date, or use a date-range frame: RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW in PostgreSQL, INTERVAL 6 DAY in MySQL. Also point out that the first six days have fewer than seven values to average.

WITH daily AS (
  SELECT order_date, SUM(amount) AS revenue
  FROM orders
  WHERE status = 'delivered'
  GROUP BY order_date
)
SELECT order_date, revenue,
       AVG(revenue) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_last_7_rows
FROM daily
ORDER BY order_date;

The calendar of every date comes from generate_series in PostgreSQL, or a recursive CTE in MySQL.

MySQL 8.4 manual: Window Function Frame Specification

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

Build monthly revenue in a CTE, then LAG(revenue) OVER (ORDER BY month) fetches the month before; LEAD would fetch the one after. Divide the change by NULLIF(previous, 0) so a month following a zero month gives NULL: PostgreSQL throws an error when dividing by zero, whereas MySQL returns NULL. Also flag gaps: a month without sales simply has no row unless you add one, which isn’t the same as zero.

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date)::date AS month, SUM(amount) AS revenue
  FROM orders
  WHERE status = 'delivered'
  GROUP BY 1
)
SELECT month, revenue,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS growth_pct
FROM monthly ORDER BY month;

On the example data, October 2026 shows 12791.00 against 4297.00 in September: growth of 197.7%.

MySQL 8.4 manual: Arithmetic Operators

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

Use LAG(order_date) over a window partitioned by customer_id and ordered by date, so each order sees that customer’s previous order date. In PostgreSQL, subtracting the two dates gives the days; in MySQL, wrap them in DATEDIFF. A customer’s first order has nothing before it, so it shows NULL.

-- PostgreSQL: subtracting two dates gives days
SELECT customer_id, order_id, order_date,
       order_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS days_since_last
FROM orders
ORDER BY customer_id, order_date;

Be ready for two follow-ups: each customer’s average gap, and anyone who ever waited more than 30 days between orders.

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

A cohort means customers who started in the same period, here the month of their first order. Find that month per customer in a CTE, then LEFT JOIN their orders from the month after and count distinct customers on each side; the LEFT JOIN keeps a cohort where no one returned, showing 0 rather than dropping it. On the example data, both September customers ordered again in October, so retention is 100%.

WITH first_order AS (
  SELECT customer_id, DATE_TRUNC('month', MIN(order_date))::date AS cohort_month
  FROM orders GROUP BY customer_id
)
SELECT f.cohort_month, COUNT(DISTINCT f.customer_id) AS customers,
       ROUND(100.0 * COUNT(DISTINCT o.customer_id) / COUNT(DISTINCT f.customer_id), 1) AS retention_pct
FROM first_order f
LEFT JOIN orders o ON o.customer_id = f.customer_id
 AND o.order_date >= f.cohort_month + INTERVAL '1 month' AND o.order_date < f.cohort_month + INTERVAL '2 months'
GROUP BY f.cohort_month ORDER BY f.cohort_month;

For the full retention grid, work out how many months each order falls after the customer’s first, then pivot on that number.

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

It’s the gaps and islands trick. Number each customer’s distinct order days in date order with ROW_NUMBER; on consecutive days, the date minus its row number gives the same value, which works as a label for the streak. Group by customer and that label, and keep groups of 3 or more days: on the example data, customer 2 ordered on 4, 5 and 6 October 2026.

WITH days AS (SELECT DISTINCT customer_id, order_date FROM orders),
numbered AS (
  SELECT customer_id, order_date,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn
  FROM days),
streaks AS (
  SELECT customer_id, order_date, order_date - CAST(rn AS INTEGER) AS streak_key FROM numbered)
SELECT customer_id, MIN(order_date) AS streak_start, MAX(order_date) AS streak_end, COUNT(*) AS days_in_a_row
FROM streaks
GROUP BY customer_id, streak_key HAVING COUNT(*) >= 3;

In MySQL, build the streak key with DATE_SUB(order_date, INTERVAL rn DAY) instead.

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

In PostgreSQL, use PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount). MySQL has no median function, so number the rows, count them and average the middle one or two; with 9 delivered orders in the example, that’s the 5th smallest, 1899. Interviewers ask for it because, unlike the average, the median isn’t dragged up by a few very large orders.

-- PostgreSQL
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount
FROM orders
WHERE status = 'delivered';

PostgreSQL 18 docs: Aggregate Functions

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

An index is a sorted structure, usually a B-tree, built on one or more columns so the database can locate rows without scanning the whole table. It makes reads faster and writes slower, because every insert must update it. It won’t help if the column sits inside a function, like YEAR(order_date) = 2026, if a LIKE pattern begins with a wildcard, if the filter returns most of the table, or if the filter skips a composite index’s leading column.

Rewrite a function on a column as a date range, and remember that both databases can index an expression when it’s truly needed.

The full answer, in the guidePractise it in the SQL LabPractise it with Prism

Measure before changing anything: EXPLAIN shows the plan, and EXPLAIN ANALYZE actually runs the query and reports real rows and timings, in recent MySQL and in PostgreSQL. Then I look for full scans of big tables and estimates far from the actual rows, index the filter and join columns, keep filters index-friendly, filter and aggregate early in a CTE, select only the columns I need, and fix any join that a DISTINCT was hiding.

EXPLAIN ANALYZE executes the statement for real, so only use it on an UPDATE or DELETE you actually want to run.

MySQL 8.4 manual: EXPLAIN StatementPostgreSQL 18 docs: Using EXPLAIN

The full answer, in the guidePractise it in the SQL LabPractise it with Prism