SQL interview questions

You write clauses in one order, but the database evaluates them in another:

1. FROM / JOIN: build the set of rows.
2. WHERE: filter individual rows.
3. GROUP BY: collapse rows into groups.
4. HAVING: filter groups.
5. SELECT: compute output columns and aliases (window functions run here too).
6. DISTINCT: remove duplicate output rows.
7. ORDER BY: sort.
8. LIMIT / OFFSET: cut the result.

This explains common errors. A column alias from SELECT can be used in ORDER BY (which runs later), but not in WHERE (which runs earlier):

 name  |   yearly
-------+------------
 Maya  | 2220000.00
 Arjun | 1704000.00
 Lena  | 1704000.00
(3 rows)

ERROR:  column "yearly" does not exist

Fix it by repeating the expression in WHERE, or by wrapping the query in a subquery or CTE.

It's the logical order. The optimiser may physically do things differently (for example, pushing filters down), but the result must match this order.

• WHERE filters rows, before grouping. It can't use aggregates like sum() or count().
• HAVING filters groups, after GROUP BY. It's where conditions on aggregates go.

Departments with more than two employees, counting only staff hired before 2024:

    name     | staff | avg_salary
-------------+-------+------------
 Engineering |     4 |     141750
 Sales       |     3 |      92333
(2 rows)

Rule of thumb: put every condition that doesn't need an aggregate in WHERE. Filtering rows early means less data to group, which is faster.

Customers and how many orders each has. LEFT JOIN keeps Stark Supply, who has never ordered; an INNER JOIN would drop them:

     name     | orders
--------------+--------
 Acme Corp    |      3
 Globex       |      2
 Initech      |      2
 Hooli        |      1
 Umbrella     |      1
 Stark Supply |      0
(6 rows)

Note count(o.id), not count(*): for Stark Supply the joined row has o.id = NULL, and count(column) skips NULLs, giving 0 instead of 1.

Follow-up trap: a condition on the right table in WHERE turns a LEFT JOIN into an inner join, because NULL rows fail the test. Put such conditions in the ON clause instead:

     name     | shipped_orders
--------------+----------------
 Acme Corp    |              2
 Globex       |              2
 Hooli        |              1
 Initech      |              0
 Stark Supply |              0
 Umbrella     |              1
(6 rows)

NULL means unknown (or missing), not zero or empty. That leads to three-valued logic: a comparison can be true, false or unknown.

• NULL = NULL is unknown, not true. Test with IS NULL / IS NOT NULL.
• WHERE keeps only rows where the condition is true, so unknown rows disappear.
• Arithmetic with NULL gives NULL: 5 + NULL is NULL.
• Aggregates like sum, avg and count(column) ignore NULLs; count(*) counts rows.

 all_customers | with_city | equals_null | is_null
---------------+-----------+-------------+---------
             6 |         5 |           0 |       1
(1 row)

Useful tools:

     name     |  city   | not_london
--------------+---------+------------
 Acme Corp    | London  |
 Globex       | Berlin  | Berlin
 Initech      | London  |
 Umbrella     | Paris   | Paris
 Hooli        | Unknown |
 Stark Supply | Berlin  | Berlin
(6 rows)

Other gotchas interviewers like:

• NOT IN with a NULL in the list returns no rows (see the NOT IN question).
• NULLs sort last in ascending order in PostgreSQL and Oracle, but first in SQL Server and MySQL. Use NULLS FIRST / NULLS LAST to be explicit.
• A UNIQUE column can usually hold many NULLs, since they aren't equal to each other.

 employees | in_a_department | departments_used
-----------+-----------------+------------------
        10 |               9 |                3
(1 row)

Sam has no department (NULL), so count(department_id) is one less than count(*).

count(1) is the same as count(*) in every major database; neither is faster.

Both stack the results of two queries (same number of columns, compatible types).

• UNION removes duplicates, which needs a sort or hash step.
• UNION ALL keeps every row and is faster.

  city
--------
 Berlin
 London
(2 rows)

  city
--------
 Berlin
 Berlin
 Berlin
 London
 London
(5 rows)

Use UNION ALL by default, unless you actually need duplicates removed. It's a common performance fix.

Related set operators: INTERSECT (rows in both) and EXCEPT (rows in the first but not the second; MINUS in Oracle).

BEGIN

DELETE 2

 items_left
------------
         14
(1 row)

ROLLBACK

 items_after_rollback
----------------------
                   16
(1 row)

Good habits: run SELECT with the same WHERE before a DELETE, and do risky changes inside a transaction so you can ROLLBACK.

TRUNCATE fails if other tables reference this one with foreign keys (unless you use CASCADE).

Constraints are rules the database enforces, so bad data can't get in even when application code has a bug.

The foreign key on orders.customer_id stops an order for a customer that doesn't exist:

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(999) is not present in table "customers".

Follow-ups:

• Natural vs surrogate keys: a natural key has business meaning (an ISBN, an email); a surrogate key is generated (id bigint GENERATED ALWAYS AS IDENTITY, or a UUID). Surrogate keys are usually preferred, because business values change. Keep a UNIQUE constraint on the natural key.
• ON DELETE options for foreign keys: RESTRICT / NO ACTION (block), CASCADE (delete children too), SET NULL.
• Index foreign key columns: PostgreSQL doesn't do it automatically, and joins and deletes on the parent need it.

Normalization organises tables so each fact is stored once. That avoids anomalies: updating a customer's city in one row but not another, or losing a product when its last order is deleted.

The short version: "every non-key column depends on the key, the whole key, and nothing but the key".

Our sample schema is in 3NF: customer details live in customers, product details in products, and order_items links them.

Denormalization deliberately duplicates data for read speed or simplicity:

• analytics and data warehouses (star schemas, wide tables);
• storing computed totals (orders.total) to avoid summing items on every page view;
• caching data that rarely changes.

The cost is keeping copies in sync (triggers, application code, or rebuild jobs). Note that order_items.unit_price here is not a normalization mistake: it records the price at the time of the order, which is a different fact from today's price.

Use a self join: join employees to itself, with different aliases for the two roles. A LEFT JOIN keeps Maya, who has no manager:

 employee | manager
----------+---------
 Maya     |
 Arjun    | Maya
 Lena     | Maya
 Tom      | Arjun
 Sofia    | Maya
 Kofi     | Sofia
 Priya    | Sofia
 Diego    | Maya
 Hana     | Diego
 Sam      | Maya
(10 rows)

Follow-up: "Find employees who earn more than their manager."

 name | salary | manager | manager_salary
------+--------+---------+----------------
(0 rows)

No rows: nobody here out-earns their manager. Add UPDATE employees SET salary = 150000 WHERE id = 4; above the query and run it again to see Tom appear (his manager Arjun earns 142,000).

For the whole chain of managers (manager's manager, and so on), use a recursive CTE.

This is an anti-join. For "customers who have never placed an order", there are three common ways:

     name
--------------
 Stark Supply
(1 row)

     name
--------------
 Stark Supply
(1 row)

     name
--------------
 Stark Supply
(1 row)

All three agree here, but prefer NOT EXISTS:

• It's always correct with NULLs. NOT IN returns nothing if the subquery contains a single NULL (see the next question).
• Optimisers turn both NOT EXISTS and LEFT JOIN ... IS NULL into an efficient anti-join.
• It states the intent clearly.

The opposite, a semi-join ("customers who have ordered"), is EXISTS, which returns each customer once even if they have many orders.

Because of NULL. x NOT IN (a, b, NULL) means x <> a AND x <> b AND x <> NULL. The last part is unknown for every x, so the whole condition is never true, and WHERE drops every row.

Departments that no employee belongs to. Sam's department_id is NULL:

 name
------
(0 rows)

No rows, even though Legal clearly has no employees. The fixes:

 name
-------
 Legal
(1 row)

 name
-------
 Legal
(1 row)

IN doesn't have this problem: x IN (a, NULL) is true when x = a. Only the negated form breaks. That's why many teams have a rule: use NOT EXISTS, never NOT IN with a subquery.

A CTE (common table expression) is a named, temporary result defined with WITH and used in the query that follows. It makes multi-step queries readable, top to bottom:

   name    | orders |  spent
-----------+--------+---------
 Acme Corp |      3 | 4300.00
 Globex    |      2 | 1840.00
 Umbrella  |      1 | 1840.00
 Initech   |      1 | 1300.00
 Hooli     |      1 |  300.00
(5 rows)

Performance: modern PostgreSQL (12+) and SQL Server inline CTEs like subqueries, so they usually cost nothing extra. In PostgreSQL, WITH x AS MATERIALIZED (...) forces it to be computed once. For big intermediate results reused by several queries, a temporary table with an index can be much faster.

A correlated subquery refers to columns of the outer query, so it's logically evaluated once per outer row.

 name  | department_id |  salary
-------+---------------+-----------
 Maya  |             1 | 185000.00
 Arjun |             1 | 142000.00
 Lena  |             1 | 142000.00
 Sofia |             2 | 120000.00
 Diego |             3 |  64000.00
(5 rows)

Same result with a window function, which reads the table once:

 name  | department_id |  salary
-------+---------------+-----------
 Maya  |             1 | 185000.00
 Arjun |             1 | 142000.00
 Lena  |             1 | 142000.00
 Sofia |             2 | 120000.00
 Diego |             3 |  64000.00
(5 rows)

Interview points:

• Correlated subqueries are not always slow: optimisers often rewrite them as joins. But on large tables, a join or window function is a safer bet.
• EXISTS subqueries are almost always correlated, and that's fine.
• Sam, with no department, never appears: NULL = NULL is unknown, so his subquery finds no rows and the average is NULL.

A classic. Note that Arjun and Lena share the second-highest salary, so be clear whether you want the second-highest value or the second person.

 second_highest
----------------
      142000.00
(1 row)

  salary
-----------
 142000.00
(1 row)

  salary
-----------
 142000.00
(1 row)

Things interviewers check:

• Ties: DISTINCT / DENSE_RANK handle them. ROW_NUMBER would pick one of Arjun and Lena arbitrarily.
• Empty case: the max() version returns NULL when there's no second salary, which is often what's asked for.
• Dialect: SQL Server uses TOP or OFFSET ... FETCH, Oracle uses FETCH FIRST.

Number the rows within each group with a window function, then filter:

 department  | name  |  salary
-------------+-------+-----------
 Engineering | Maya  | 185000.00
 Engineering | Arjun | 142000.00
 Sales       | Sofia | 120000.00
 Sales       | Priya |  81000.00
 Support     | Diego |  64000.00
 Support     | Hana  |  52000.00
(6 rows)

Choose the numbering function by how you want ties handled:

PostgreSQL alternatives:

• DISTINCT ON (department_id) ... ORDER BY department_id, salary DESC for the top 1 per group.
• A LATERAL join (CROSS APPLY in SQL Server) with LIMIT 2, which can use an index on (department_id, salary) and is fast when there are few groups.

Both compute values over a set of rows, but:

• GROUP BY collapses each group into one row.
• A window function keeps every row and adds a computed value next to it.

 name  | department_id |  salary   | dept_avg |  vs_avg
-------+---------------+-----------+----------+-----------
 Maya  |             1 | 185000.00 |   141750 |  43250.00
 Arjun |             1 | 142000.00 |   141750 |    250.00
 Lena  |             1 | 142000.00 |   141750 |    250.00
 Tom   |             1 |  98000.00 |   141750 | -43750.00
 Sofia |             2 | 120000.00 |    92333 |  27667.00
 Priya |             2 |  81000.00 |    92333 | -11333.00
 Kofi  |             2 |  76000.00 |    92333 | -16333.00
 Diego |             3 |  64000.00 |    58000 |   6000.00
 Hana  |             3 |  52000.00 |    58000 |  -6000.00
(9 rows)

The OVER (...) clause defines the window:

Families of window functions:

• Ranking: ROW_NUMBER, RANK, DENSE_RANK, NTILE.
• Offsets: LAG, LEAD, FIRST_VALUE, LAST_VALUE.
• Aggregates as windows: sum, avg, count, min, max with OVER.

Window functions run after WHERE, GROUP BY and HAVING, so you can't filter on them directly in WHERE. Wrap the query in a subquery or CTE (or use QUALIFY in Snowflake, BigQuery and DuckDB).

They differ only in how they handle ties (Arjun and Lena both earn 142,000):

 name  |  salary   | row_number | rank | dense_rank
-------+-----------+------------+------+------------
 Maya  | 185000.00 |          1 |    1 |          1
 Arjun | 142000.00 |          2 |    2 |          2
 Lena  | 142000.00 |          3 |    2 |          2
 Sofia | 120000.00 |          4 |    4 |          3
 Tom   |  98000.00 |          5 |    5 |          4
(5 rows)

Always add a tie-breaker column to ORDER BY when using ROW_NUMBER (for example ORDER BY salary DESC, id), or results can change from run to run.

Use sum() as a window function with an ORDER BY. Monthly revenue from non-cancelled orders, and the running total:

   month    | revenue | running_total
------------+---------+---------------
 2024-01-01 | 3740.00 |       3740.00
 2024-02-01 | 3140.00 |       6880.00
 2024-03-01 | 2700.00 |       9580.00
(3 rows)

Details interviewers probe:

• The default frame. With ORDER BY and no frame, the window is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With RANGE, rows with the same sort value are added together, which surprises people when dates repeat. Write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to add strictly row by row.
• Per group: add PARTITION BY customer_id for a running total per customer.
• Moving average: avg(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) gives a 3-month moving average.

Before window functions, this needed a self join or correlated subquery, which is O(n²).

LAG(value) reads from the previous row in the window; LEAD(value) from the next one.

   month    | revenue | previous | change  | pct_change
------------+---------+----------+---------+------------
 2024-01-01 | 3740.00 |          |         |
 2024-02-01 | 3140.00 |  3740.00 | -600.00 |      -16.0
 2024-03-01 | 2700.00 |  3140.00 | -440.00 |      -14.0
(3 rows)

The first month has no previous row, so LAG returns NULL. You can give a default: LAG(revenue, 1, 0). The second argument is the offset, so LAG(revenue, 12) compares with the same month last year.

Another common use: time between events, like days since a customer's previous order:

 customer_id | id  | ordered_on | days_since_last
-------------+-----+------------+-----------------
           1 | 101 | 2024-01-05 |
           1 | 103 | 2024-02-02 |              28
           1 | 106 | 2024-03-03 |              30
           2 | 102 | 2024-01-18 |
           2 | 107 | 2024-03-11 |              53
           3 | 104 | 2024-02-14 |
           3 | 109 | 2024-03-28 |              43
           4 | 105 | 2024-02-20 |
           5 | 108 | 2024-03-25 |
(9 rows)

Watch out for gaps: if a month has no orders, LAG compares with the last month that does have data. Generate the full list of months first (see the question on missing dates).

Group by the columns that define a duplicate and keep groups with more than one row:

CREATE TABLE

INSERT 0 6

      email      | copies | first_id
-----------------+--------+----------
 ana@example.com |      3 |        1
 bo@example.com  |      2 |        2
(2 rows)

To see the full duplicate rows, not just the groups, use a window count:

 id |      email      | signed_up
----+-----------------+------------
  1 | ana@example.com | 2024-01-02
  3 | ana@example.com | 2024-01-09
  5 | ANA@example.com | 2024-02-01
  2 | bo@example.com  | 2024-01-03
  6 | bo@example.com  | 2024-02-03
(5 rows)

Talk about what counts as a duplicate: exact match, case-insensitive email, trimmed spaces? Here lower(email) catches ANA@example.com. Then prevent new duplicates with a unique index, e.g. CREATE UNIQUE INDEX ON signups (lower(email));.

Number the rows within each duplicate group, keep number 1, delete the rest:

CREATE TABLE

INSERT 0 6

DELETE 3

 id |      email      | signed_up
----+-----------------+------------
  1 | ana@example.com | 2024-01-02
  2 | bo@example.com  | 2024-01-03
  4 | cy@example.com  | 2024-01-10
(3 rows)

The ORDER BY inside the window decides which copy survives: here the earliest signup.

No unique id? Each database has a hidden row identifier: ctid in PostgreSQL, ROWID in Oracle. In SQL Server, you can delete through a CTE directly:

-- SQL Server
WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY signed_up) AS rn
  FROM signups
)
DELETE FROM ranked WHERE rn > 1;

For a huge table with many duplicates, it's often faster to copy the distinct rows into a new table (CREATE TABLE ... AS SELECT DISTINCT ON ...), swap the tables, and add a unique constraint so it can't happen again.

A recursive CTE has two parts joined by UNION ALL:

1. the anchor: the starting rows (the CEO, who has no manager);
2. the recursive part: joins the table to the rows found in the previous step, repeating until no new rows appear.

 org_chart | level |         path
-----------+-------+----------------------
 Maya      |     1 | Maya
   Arjun   |     2 | Maya > Arjun
     Tom   |     3 | Maya > Arjun > Tom
   Diego   |     2 | Maya > Diego
     Hana  |     3 | Maya > Diego > Hana
   Lena    |     2 | Maya > Lena
   Sam     |     2 | Maya > Sam
   Sofia   |     2 | Maya > Sofia
     Kofi  |     3 | Maya > Sofia > Kofi
     Priya |     3 | Maya > Sofia > Priya
(10 rows)

Other uses: category trees, bill of materials (parts made of parts), graph walks, and generating series of numbers or dates.

Safety points:

• A cycle in the data (A manages B, B manages A) makes it loop forever. Guard with a depth limit (WHERE org.level < 20) or, in PostgreSQL 14+, the CYCLE clause.
• SQL Server uses the same syntax without the word RECURSIVE, and stops at 100 levels by default (OPTION (MAXRECURSION n)).

Use conditional aggregation: one aggregate per output column, each counting only matching rows. Revenue per product category, with one column per month:

 category |   jan   |   feb   |   mar
----------+---------+---------+---------
 Hardware | 3540.00 | 2950.00 | 1900.00
 Services |         |  150.00 |  300.00
 Software |  200.00 |   40.00 |  500.00
(3 rows)

FILTER (WHERE ...) is PostgreSQL (and SQLite) syntax. The portable form uses CASE:

SUM(CASE WHEN ordered_on >= '2024-01-01' AND ordered_on < '2024-02-01'
         THEN quantity * unit_price ELSE 0 END) AS jan

Notes:

• SQL Server and Oracle have a PIVOT operator, and Snowflake has PIVOT too, but conditional aggregation works everywhere.
• SQL needs the output columns to be known when the query is written. A pivot with dynamic columns (one per month, however many there are) needs dynamic SQL or pivoting in the reporting tool.
• The reverse, unpivot, can be done with UNION ALL or a LATERAL (VALUES ...) join.

CASE is SQL's if/else. It returns the value of the first matching branch, or the ELSE value (or NULL if there's no ELSE).

 name  |  salary   |  band
-------+-----------+--------
 Maya  | 185000.00 | senior
 Arjun | 142000.00 | senior
 Lena  | 142000.00 | senior
 Sofia | 120000.00 | mid
 Tom   |  98000.00 | mid
 Priya |  81000.00 | mid
 Kofi  |  76000.00 | junior
 Sam   |  70000.00 | junior
 Diego |  64000.00 | junior
 Hana  |  52000.00 | junior
(10 rows)

It works anywhere an expression does: SELECT, WHERE, ORDER BY, GROUP BY and inside aggregates:

 shipped | pending | total
---------+---------+-------
       6 |       2 |     9
(1 row)

 id  |  status
-----+-----------
 106 | pending
 109 | pending
 101 | shipped
 102 | shipped
 103 | shipped
 105 | shipped
 107 | shipped
 108 | shipped
 104 | cancelled
(9 rows)

Branches are checked in order, so put the most specific conditions first. In the salary example, checking >= 80000 first would label everyone above 80,000 as "mid".

GROUP BY can only produce groups that exist in the data. To show zeros for empty periods, generate the full calendar and LEFT JOIN the data to it.

Orders per week in February and March 2024. Several weeks have no orders:

    week    | orders
------------+--------
 2024-02-05 |      0
 2024-02-12 |      1
 2024-02-19 |      1
 2024-02-26 |      1
 2024-03-04 |      0
 2024-03-11 |      1
 2024-03-18 |      0
 2024-03-25 |      2
(8 rows)

Key points:

• generate_series is PostgreSQL. Other options: a recursive CTE, a numbers table, or a permanent calendar (date dimension) table, which data warehouses usually have anyway (with columns like is_weekend, fiscal_quarter).
• Use count(o.id), not count(*), so empty weeks show 0 rather than 1.
• Use COALESCE(sum(...), 0) for sums, since an empty week's sum is NULL.
• Use half-open ranges (>= start AND < end) so timestamps at midnight aren't counted twice.

Islands are runs of consecutive values (days in a row a user was active); gaps are the breaks between them. Typical questions: "longest login streak", "periods a machine was down".

The trick: subtract a ROW_NUMBER from the date. Within a run of consecutive days, both go up by 1 each row, so the difference stays constant and identifies the island.

CREATE TABLE

INSERT 0 9

 user_id | streak_start | streak_end | days
---------+--------------+------------+------
       1 | 2024-03-01   | 2024-03-03 |    3
       1 | 2024-03-05   | 2024-03-06 |    2
       1 | 2024-03-09   | 2024-03-09 |    1
       2 | 2024-03-01   | 2024-03-01 |    1
       2 | 2024-03-03   | 2024-03-04 |    2
(5 rows)

Then the longest streak per user is just max(days) over that result.

If the data can contain the same day twice, de-duplicate first (SELECT DISTINCT user_id, day) or use DENSE_RANK.

An alternative approach uses LAG to flag where a new island starts (when the gap to the previous row is more than 1 day), then a running sum of those flags numbers the islands. It's handy for timestamps with a tolerance, like "sessions with no more than 30 minutes between events".

Divide by a window sum with an empty OVER (), which covers all rows:

 category | revenue | pct_of_total
----------+---------+--------------
 Hardware | 8390.00 |         87.6
 Software |  740.00 |          7.7
 Services |  450.00 |          4.7
(3 rows)

sum(sum(...)) OVER () looks odd but is normal: the inner sum is the GROUP BY aggregate per category, and the outer sum ... OVER () adds those up across all groups.

Add PARTITION BY for "percent within a group", e.g. each employee's share of their department's payroll:

 department_id | name  |  salary   | pct_of_dept
---------------+-------+-----------+-------------
             1 | Maya  | 185000.00 |        32.6
             1 | Arjun | 142000.00 |        25.0
             1 | Lena  | 142000.00 |        25.0
             1 | Tom   |  98000.00 |        17.3
             2 | Sofia | 120000.00 |        43.3
             2 | Priya |  81000.00 |        29.2
             2 | Kofi  |  76000.00 |        27.4
             3 | Diego |  64000.00 |        55.2
             3 | Hana  |  52000.00 |        44.8
(9 rows)

Integer division trap: in PostgreSQL and SQL Server, 5 / 2 is 2. Multiply by 100.0 (a decimal) first, as above, to get a decimal result.

Use the ordered-set aggregates percentile_cont (interpolates between values) and percentile_disc (returns an actual value from the data):

 department_id | staff |  mean  | median | median_disc
---------------+-------+--------+--------+-------------
             1 |     4 | 141750 | 142000 |   142000.00
             2 |     3 |  92333 |  81000 |    81000.00
             3 |     2 |  58000 |  58000 |    52000.00
(3 rows)

Sales shows why the median matters: Sofia's 120,000 pulls the mean up to 92,333, while the median of 81,000 better represents a typical salary in that team. Medians are robust to outliers, which is why they're used for incomes, response times and house prices.

Other percentiles: percentile_cont(0.95) for p95 latency. percentile_cont(ARRAY[0.25, 0.5, 0.75]) returns quartiles at once.

Dialects: SQL Server has PERCENTILE_CONT as a window function (OVER (PARTITION BY ...)), Snowflake and BigQuery have MEDIAN / APPROX_QUANTILES. On huge data, approximate functions are much cheaper.

"Rows that match all of several values" is a classic. Filter to the products you care about, group per customer, and require that each product appears:

   name
----------
 Umbrella
(1 row)

Why the other attempts fail:

• WHERE p.name = 'Laptop' AND p.name = 'Monitor' returns nothing: one row can't have two names.
• WHERE p.name IN (...) alone returns customers who bought either.
• count(*) = 2 would wrongly count a customer who bought two laptops; hence count(DISTINCT ...).

Variations:

   name
-----------
 Acme Corp
 Initech
(2 rows)

The fully general version, "customers who bought every product in a category", is called relational division: compare count(DISTINCT product) with the number of products in that category.

Several correct approaches. The most portable is ROW_NUMBER:

 customer_id | order_id | ordered_on
-------------+----------+------------
           1 |      101 | 2024-01-05
           2 |      102 | 2024-01-18
           3 |      104 | 2024-02-14
           4 |      105 | 2024-02-20
           5 |      108 | 2024-03-25
(5 rows)

PostgreSQL's DISTINCT ON is shorter: keep the first row of each customer_id according to the ORDER BY:

 customer_id | order_id | ordered_on
-------------+----------+------------
           1 |      101 | 2024-01-05
           2 |      102 | 2024-01-18
           3 |      104 | 2024-02-14
           4 |      105 | 2024-02-20
           5 |      108 | 2024-03-25
(5 rows)

A common wrong answer:

SELECT customer_id, min(ordered_on), id   -- error: id isn't grouped
FROM orders GROUP BY customer_id;

min(ordered_on) gives the date but not which order it was. Joining back on the date breaks when a customer has two orders on the same day. That's why the window-function version, with a tie-breaker (id), is the safe answer.

Follow-up: "customer's latest order" is the same with ORDER BY ordered_on DESC. With an index on (customer_id, ordered_on), all of these are fast.

In PostgreSQL (and SQLite), use INSERT ... ON CONFLICT. It needs a unique constraint or primary key to detect the conflict:

CREATE TABLE

INSERT 0 2

INSERT 0 2

 product_id | quantity
------------+----------
          1 |       10
          2 |       12
          3 |        4
(3 rows)

EXCLUDED is the row you tried to insert. ON CONFLICT DO NOTHING skips rows that already exist.

Other databases:

Why not "SELECT, then INSERT or UPDATE" in application code? Two requests can both see "doesn't exist" and both insert, causing a duplicate or a constraint error. ON CONFLICT is atomic. (MERGE in SQL Server can still hit race conditions under concurrency unless you add locking hints like HOLDLOCK.)

OFFSET pagination skips rows:

 id  | ordered_on
-----+------------
 106 | 2024-03-03
 105 | 2024-02-20
 104 | 2024-02-14
(3 rows)

Keyset pagination remembers the last row of the previous page and continues after it:

 id  | ordered_on
-----+------------
 106 | 2024-03-03
 105 | 2024-02-20
 104 | 2024-02-14
(3 rows)

Keyset needs a unique, ordered key: add id as a tie-breaker so rows with the same date aren't lost. An index on (ordered_on, id) makes it efficient. The comparison (a, b) < (x, y) is a row value comparison; in databases without it, write a < x OR (a = x AND b < y).

An index is a separate, sorted data structure that maps column values to the rows that contain them, like the index at the back of a book. Without one, the database must read every row (a full table scan) to find matches.

Most indexes are B-trees: balanced trees whose leaves hold sorted keys and pointers to rows. Finding a value takes a few page reads, even among millions of rows (O(log n)).

A B-tree index on a column helps:

• equality and range filters: =, <, >, BETWEEN, LIKE 'abc%';
• ORDER BY and min/max (the data is already sorted);
• joins on that column.

The costs:

• slower writes: every INSERT, UPDATE and DELETE must update every index;
• disk and memory space;
• the optimiser may still ignore it if the query returns a large share of the table, because reading the table straight through is cheaper.

Other index types: hash (equality only), GIN (arrays, JSON, full-text search), GiST (geometry, ranges), BRIN (huge, naturally ordered tables like logs), plus partial and expression indexes.

Good candidates: columns in frequent WHERE, JOIN and ORDER BY clauses that are selective (many distinct values). Foreign keys usually deserve one.

• A clustered index is the table: rows are stored in the order of the index key. There can be only one per table.
• A non-clustered (secondary) index is a separate structure holding the key plus a pointer to the row. There can be many.

How databases differ:

• SQL Server: the primary key is clustered by default. Choose a narrow, ever-increasing clustered key (like an identity column); a random GUID causes page splits and fragmentation.
• MySQL (InnoDB): the table is always clustered on the primary key, and secondary indexes store the primary key as their pointer, so a long primary key makes every index bigger.
• PostgreSQL: tables are heaps (no clustered index). Every index is secondary. The CLUSTER command reorders a table once, but the order isn't maintained.

Covering index: if a non-clustered index contains every column a query needs, the database answers from the index alone (an "index-only scan"). SQL Server and PostgreSQL let you add extra columns with INCLUDE (...).

Yes, a lot. An index on (customer_id, ordered_on) is sorted by customer_id first, then by ordered_on within each customer, like a phone book sorted by last name, then first name.

CREATE INDEX orders_customer_date ON orders (customer_id, ordered_on);

This is the leftmost prefix rule: an index helps queries that filter on its first column, or its first two, and so on.

How to order the columns:

1. Columns compared with equality (=, IN) first.
2. Then the column used for a range or for sorting. After a range condition, later columns can't be used for seeking.
3. Among equality columns, the order matters less than people think. Put the one shared by the most queries first, so one index serves several queries.

Don't create both (a) and (a, b): the second already serves queries on a alone.

Work through it step by step:

1. Measure: confirm it's the query (not the network or the app), and how slow, with real parameters. Use slow-query logs or pg_stat_statements to find the worst offenders.
2. Read the execution plan: EXPLAIN ANALYZE (PostgreSQL, MySQL 8), "Include Actual Execution Plan" (SQL Server).
3. Look for the usual suspects:

                       QUERY PLAN
--------------------------------------------------------
 Seq Scan on orders  (cost=0.00..24.12 rows=6 width=44)
   Filter: (customer_id = 1)
(2 rows)

On our tiny table, a sequential scan is the right plan. On a large table you'd want an index scan.

4. Reduce the work: select only needed columns, filter early, paginate with keyset, pre-aggregate with materialized views.
5. Verify the fix with the plan and timings, and watch that it doesn't slow down writes or other queries.

Good answers mention statistics and cardinality estimates: most bad plans come from the optimiser guessing row counts wrong.

A condition is sargable (Search ARGument ABLE) when the database can use an index to find matching rows. Wrapping the column in a function or calculation usually makes it non-sargable, because the index is sorted by the raw column value, not by the function's result.

Both versions return the same rows:

 non_sargable
--------------
            3
(1 row)

 sargable
----------
        3
(1 row)

The rule: keep the column bare on one side of the comparison and move the calculation to the other side. LIKE 'abc%' (prefix) can use a B-tree index; LIKE '%abc' can't.

Implicit type conversions are a sneaky cause, especially in SQL Server comparing varchar columns with nvarchar parameters.

ACID is the set of guarantees a database transaction gives:

BEGIN

UPDATE 1

UPDATE 1

 id |  name   |  price
----+---------+---------
  1 | Laptop  | 1150.00
  2 | Monitor |  350.00
(2 rows)

ROLLBACK

 id |  name   |  price
----+---------+---------
  1 | Laptop  | 1200.00
  2 | Monitor |  300.00
(2 rows)

How databases provide it: a write-ahead log (durability and crash recovery), locks and/or MVCC (multi-version concurrency control, for isolation), and constraint checks (consistency).

Follow-up: many NoSQL and distributed systems trade some ACID guarantees for availability and scale, often described as BASE (basically available, soft state, eventually consistent).

Isolation levels trade correctness for concurrency. Each prevents more anomalies:

BEGIN ISOLATION LEVEL SERIALIZABLE;
-- ...
COMMIT;

Points that impress interviewers:

• Most databases use MVCC: readers see a snapshot and don't block writers. In PostgreSQL, Read Uncommitted behaves like Read Committed.
• Serializable transactions may fail with a serialization error, so the application must retry them.
• Write skew example: two doctors both check "at least one other doctor is on call", both go off call, and now nobody is. Only Serializable (or explicit locking with SELECT ... FOR UPDATE) prevents it.
• Higher isolation means more blocking or more retries. Most apps use Read Committed plus targeted locks or constraints.

A deadlock happens when two transactions each hold a lock the other needs, so neither can continue:

The database detects the cycle and kills one transaction (the "victim") with a deadlock error. The other continues.

How to prevent or reduce them:

1. Lock in a consistent order: e.g. always update the lower account id first. That removes the cycle.
2. Keep transactions short: no user input or slow API calls while holding locks.
3. Touch fewer rows: good indexes so updates lock only the rows they need, not big ranges.
4. Lock up front when you'll update later: SELECT ... FOR UPDATE on all the rows at the start, in order.
5. Retry: deadlocks can't be ruled out completely, so catch the error and retry the whole transaction.

How to investigate: PostgreSQL logs both queries on a deadlock; SQL Server has deadlock graphs (system_health Extended Events); MySQL has SHOW ENGINE INNODB STATUS.

Don't confuse a deadlock with blocking: a long transaction holding locks makes others wait, but they continue once it finishes.

Both stop two users from overwriting each other's changes (a lost update).

Pessimistic locking: lock the row when you read it, so others must wait.

BEGIN;
SELECT quantity FROM stock WHERE product_id = 1 FOR UPDATE;   -- others wait here
UPDATE stock SET quantity = quantity - 1 WHERE product_id = 1;
COMMIT;

Optimistic locking: don't lock. Keep a version number, and only update if it hasn't changed since you read it:

CREATE TABLE

INSERT 0 1

UPDATE 1

UPDATE 0

 id |   body   | version
----+----------+---------
  1 | A's edit |       2
(1 row)

The second update affects 0 rows, so the app tells user B "this document changed; reload and try again".

For simple counters, a single atomic statement avoids the problem entirely: UPDATE stock SET quantity = quantity - 1 WHERE product_id = 1 AND quantity > 0.

• A view is a saved query. It stores no data; every time you select from it, the query runs again, so results are always current.
• A materialized view stores the result like a table. Reads are fast, but the data is only as fresh as the last refresh.

CREATE MATERIALIZED VIEW

 customer_id | revenue
-------------+---------
           1 | 4300.00
           2 | 1840.00
(2 rows)

INSERT 0 1

INSERT 0 1

 customer_id | revenue
-------------+---------
           1 | 4300.00
           2 | 1840.00
(2 rows)

REFRESH MATERIALIZED VIEW

 customer_id | revenue
-------------+---------
           1 | 4300.00
           2 | 4240.00
(2 rows)

Notes: PostgreSQL's REFRESH MATERIALIZED VIEW CONCURRENTLY avoids blocking readers (needs a unique index). SQL Server's equivalent is an indexed view, kept up to date automatically. Snowflake, BigQuery and Oracle can refresh materialized views automatically or incrementally.

A star schema has a central fact table of measurable events, surrounded by dimension tables that describe them:

• Facts are numbers you add up (revenue, quantity) at a stated grain ("one row per order line").
• Dimensions are the "by" in "revenue by month by category".
• A snowflake schema normalizes dimensions further (category in its own table), trading simpler storage for more joins.

Related ideas: ETL/ELT pipelines copy data from OLTP systems into the warehouse; slowly changing dimensions (type 2) keep history when, say, a customer moves city.

SQL injection happens when user input is pasted into the SQL text, so the input can change the query itself.

# Vulnerable: input becomes part of the SQL
query = f"SELECT * FROM users WHERE email = '{email}'"
# email = "' OR '1'='1"  ->  WHERE email = '' OR '1'='1'  (returns every user)
# email = "'; DROP TABLE users; --"  -> runs a second statement

Prevention:

1. Parameterized queries (prepared statements), always. The SQL and the values are sent separately, so values can never be read as SQL:

cur.execute("SELECT * FROM users WHERE email = %s", (email,))

cmd.CommandText = "SELECT * FROM users WHERE email = @email";
cmd.Parameters.AddWithValue("@email", email);

2. Use an ORM or query builder correctly. They parameterize for you, but raw-SQL escape hatches can still be misused with string concatenation.
3. Allow-list identifiers. Table and column names can't be parameters. If users choose a sort column, map their choice to a fixed list of known names.
4. Least privilege: the app's database user shouldn't be able to drop tables or read other schemas.
5. Defence in depth: input validation, a WAF, and avoiding detailed database errors in responses.

Escaping quotes by hand is not a reliable fix; parameters are.

Both split a large table into smaller pieces, but in different places:

• Partitioning: one table is split into parts inside one database server, e.g. one partition per month. The database routes queries automatically.
• Sharding: data is split across several database servers, e.g. customers 1 to 1M on server A, the rest on server B. Each shard is a separate database.

CREATE TABLE events (
  id bigint, occurred_at timestamptz NOT NULL, payload jsonb
) PARTITION BY RANGE (occurred_at);
 
CREATE TABLE events_2024_03 PARTITION OF events
  FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

Choosing a partition or shard key is the critical decision: it should match the most common filter (e.g. occurred_at, tenant_id) and spread data evenly. A bad key creates hot spots.

Interview advice: suggest sharding last. First try indexes, query fixes, caching, read replicas and partitioning. Or use a database that shards for you (Citus, CockroachDB, Vitess, cloud warehouses).

CREATE TABLE

CREATE FUNCTION

CREATE TRIGGER

UPDATE 2

 product_id | old_price | new_price
------------+-----------+-----------
          4 |    100.00 |     90.00
          5 |     40.00 |     36.00
(2 rows)

Trade-offs:

A balanced answer: keep most business logic in the application; use the database for data integrity (constraints first, triggers when constraints can't express the rule), auditing, and heavy set-based processing.

In SQL Server, NVARCHAR stores Unicode (UTF-16); plain VARCHAR needs a UTF-8 collation for other languages. Mixing the two in comparisons can stop index use.

General rules for picking types:

• Money: numeric/decimal, never float. Floating point can't represent 0.1 exactly.
• Dates and times: real date / timestamp types, not strings. Store instants as timestamptz (or UTC).
• IDs: bigint identity for most tables; UUIDs when IDs are generated in many places (prefer time-ordered UUIDv7 for index friendliness).
• Booleans: boolean, not 'Y'/'N' strings.
• Smallest type that safely fits: smaller rows mean more rows per page and faster scans, but don't use int for something that may pass 2.1 billion.

      float_sum      | numeric_sum
---------------------+-------------
 0.30000000000000004 |         0.3
(1 row)

Use a string-aggregate function. In PostgreSQL and SQL Server 2017+, that's string_agg:

 department  |       employees        | staff
-------------+------------------------+-------
 Engineering | Arjun, Lena, Maya, Tom |     4
 Sales       | Kofi, Priya, Sofia     |     3
 Support     | Diego, Hana            |     2
(3 rows)

PostgreSQL can also build an array (array_agg) or JSON (json_agg), which is often better for APIs than a comma-separated string.

The reverse, splitting a string into rows, is string_to_table or unnest(string_to_array(...)) in PostgreSQL, and STRING_SPLIT in SQL Server.

They compute several levels of grouping in one query, such as per-month totals, per-category totals and a grand total, which would otherwise need several queries combined with UNION ALL.

 category |  month  | revenue
----------+---------+---------
 Hardware | 2024-01 | 3540.00
 Hardware | 2024-02 | 2950.00
 Hardware | 2024-03 | 1900.00
 Hardware |         | 8390.00
 Services | 2024-02 |  150.00
 Services | 2024-03 |  300.00
 Services |         |  450.00
 Software | 2024-01 |  200.00
 Software | 2024-02 |   40.00
 Software | 2024-03 |  500.00
 Software |         |  740.00
          |         | 9580.00
(12 rows)

The rows with NULL in month are subtotals per category, and the row with NULL in both columns is the grand total.

Use the GROUPING(col) function to tell a subtotal's NULL apart from a real NULL value in the data. It returns 1 when the column was rolled up.