Fundamentals · SQL

SQL: the questions candidates report

18 questions
6 themes
11 companies
17 with candidate reports

Eighteen SQL questions reported in interviews, each with a query and its exact output on one small sample dataset. Load the schema once, run each query yourself, then read the pitfalls.

The tables used below

Four tables. The results below assume 4 departments, 8 employees (one with no department), 6 customers (two emails appear twice) and 15 orders (one guest order with no customer), as listed in the INSERT statements after the table definitions.

Show the tables and the sample rows

schema.sql

CREATE TABLE departments (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  salary INTEGER NOT NULL,
  dept_id INTEGER REFERENCES departments (id),
  manager_id INTEGER REFERENCES employees (id),
  city TEXT NOT NULL
);

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers (id),
  category TEXT NOT NULL,
  product TEXT NOT NULL,
  order_date TEXT NOT NULL,
  status TEXT NOT NULL,
  amount INTEGER NOT NULL
);

INSERT INTO departments VALUES (1, 'Engineering'), (2, 'Sales'), (3, 'Support'), (4, 'Legal');

INSERT INTO employees VALUES
  (1, 'Asha', 9000, 1, NULL, 'Pune'),
  (2, 'Bilal', 7000, 1, 1, 'Pune'),
  (3, 'Chen', 7000, 1, 1, 'Delhi'),
  (4, 'Dev', 8000, 2, NULL, 'Mumbai'),
  (5, 'Esha', 5000, 2, 4, 'Mumbai'),
  (6, 'Farid', 5000, 2, 4, 'Pune'),
  (7, 'Gita', 4000, 3, 2, 'Pune'),
  (8, 'Hari', 6500, NULL, 1, 'Pune');

INSERT INTO customers VALUES
  (1, 'Maya', 'maya@example.com'),
  (2, 'Noor', 'noor@example.com'),
  (3, 'Omar', 'omar@example.com'),
  (4, 'Maya', 'maya@example.com'),
  (5, 'Pia', 'pia@example.com'),
  (6, 'Noor', 'noor@example.com');

INSERT INTO orders VALUES
  (1, 1, 'Books', 'Atlas', '2026-01-05', 'completed', 400),
  (2, 1, 'Books', 'Novel', '2026-01-20', 'completed', 250),
  (3, 2, 'Toys', 'Robot', '2026-01-20', 'completed', 900),
  (4, 2, 'Books', 'Atlas', '2026-02-03', 'cancelled', 400),
  (5, 3, 'Toys', 'Robot', '2026-02-03', 'completed', 900),
  (6, 3, 'Toys', 'Puzzle', '2026-02-10', 'completed', 300),
  (7, 3, 'Books', 'Novel', '2026-02-10', 'completed', 250),
  (8, 4, 'Games', 'Chess', '2026-02-10', 'completed', 600),
  (9, NULL, 'Toys', 'Puzzle', '2026-03-01', 'completed', 300),
  (10, 2, 'Games', 'Chess', '2026-03-01', 'completed', 600),
  (11, 1, 'Games', 'Cards', '2026-03-15', 'completed', 150),
  (12, 6, 'Books', 'Atlas', '2026-03-20', 'completed', 400),
  (13, 4, 'Toys', 'Robot', '2026-03-20', 'completed', 900),
  (14, 3, 'Books', 'Comic', '2026-03-20', 'completed', 250),
  (15, 6, 'Toys', 'Blocks', '2026-03-20', 'completed', 600);

Joins

What are the join types, and how do you keep rows that have no match?CoreSeveral reports
INNER JOIN keeps only rows that match on both sides. LEFT JOIN keeps every left row and fills the right columns with NULL when nothing matches. RIGHT JOIN mirrors it, FULL JOIN keeps unmatched rows from both sides, and CROSS JOIN pairs every row with every row.
The query lists every employee with a department name. Hari has no department, so he still appears with NULL; an inner join would drop him silently, which is a common reporting bug.
Pitfall: a filter on the right table in WHERE (such as d.name = 'Sales') turns a left join back into an inner join, because NULL fails the test. Put it in the ON clause to keep unmatched rows. Follow-up: SQLite supports RIGHT and FULL joins only from version 3.39, and MySQL has no FULL JOIN.
query.sql
```
SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON d.id = e.dept_id
ORDER BY e.id
```
name department
Asha Engineering
Bilal Engineering
Chen Engineering
Dev Sales
Esha Sales
Farid Sales
Gita Support
Hari NULL
Count the employees in every department, including departments that have none.CoreOne report
To count children per parent and keep parents with none, start from the parent table, LEFT JOIN the child table, and count a child column rather than *.
COUNT(e.id) ignores NULLs, so Legal shows 0. COUNT(*) would count the single NULL-padded row and report 1, which is the classic mistake. The query sorts by count descending and then by department id, so ties come out in a stable order.
- Group by the department id as well as the name, so two departments with the same name stay separate.
- A condition on employees (for example only active ones) belongs in the ON clause, not WHERE, or empty departments vanish again.
Variant: a correlated scalar subquery, (SELECT COUNT(*) FROM employees e WHERE e.dept_id = d.id), gives the same answer without a join.
query.sql
```
SELECT d.id, d.name, COUNT(e.id) AS employees
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
GROUP BY d.id, d.name
ORDER BY employees DESC, d.id
```
id name employees
1 Engineering 3
2 Sales 3
3 Support 1
4 Legal 0
- Swiggy
- Candidate write-up · 2021 ↗
Find employees who live in the same city as their manager.CoreSeveral reports
A self join joins a table to itself under two aliases, one playing the employee and the other the manager. Here e.manager_id = m.id links each employee to the manager row, and the extra condition e.city = m.city keeps those who share a city.
Employees with no manager (Asha and Dev, whose manager_id is NULL) never match, so the inner join drops them. To list them too, use a LEFT JOIN and decide what the missing city comparison should mean, since comparing with NULL is never true.
Variants an interviewer reaches for: employees who earn more than their manager (e.salary > m.salary), managers ranked by number of reports (group by m.id), and the full chain of command for one person, which needs a recursive CTE.
query.sql
```
SELECT e.name AS employee, m.name AS manager, e.city
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.city = m.city
ORDER BY e.id
```
employee manager city
Bilal Asha Pune
Esha Dev Mumbai
Gita Bilal Pune
Hari Asha Pune
- Microsoft
- Candidate write-up ↗
- Prep guide ↗
Find the rows in one table that have no match in another, such as customers with no orders.CoreSeveral reports
An anti-join returns rows from one table that have no partner in another. There are three forms: LEFT JOIN ... WHERE right.id IS NULL, NOT EXISTS and NOT IN. The query uses the first and returns Pia, who has no orders.
Test a right-table column that can never be NULL for a real match, normally its primary key.
The trap is NOT IN. One sample order has a NULL customer_id, so NOT IN (SELECT customer_id FROM orders) compares against an unknown value and returns no rows at all. NOT EXISTS has no such problem and is the safest default.
Follow-up: engines usually plan a LEFT JOIN anti-join and NOT EXISTS the same way, and an index on orders.customer_id makes both cheap.
query.sql
```
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL
```
id name
5 Pia
- Zomato
- Candidate write-up · 2025 ↗
- Prep guide ↗

name	department
Asha	Engineering
Bilal	Engineering
Chen	Engineering
Dev	Sales
Esha	Sales
Farid	Sales
Gita	Support
Hari	NULL

id	name	employees
1	Engineering	3
2	Sales	3
3	Support	1
4	Legal	0

employee	manager	city
Bilal	Asha	Pune
Esha	Dev	Mumbai
Gita	Bilal	Pune
Hari	Asha	Pune

id	name
5	Pia

Aggregation and grouping

What is the difference between WHERE and HAVING when grouping?CoreSeveral reports
WHERE filters individual rows before grouping, so it cannot use aggregates. HAVING filters groups after aggregation, so it can. The logical order is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.
The query first drops employees paid 5000 or less with WHERE, groups the rest by department, then keeps departments with at least two remaining people with HAVING. Engineering has three and averages 7666.7. Sales is left with only Dev and Support with nobody, so both disappear.
Rule of thumb: put every condition that needs no aggregate in WHERE, because it shrinks the data before grouping and can use an index. Follow-up: every selected column must be grouped or aggregated. Most engines reject a bare column, while SQLite accepts it and returns a value from an arbitrary row of the group.
query.sql
```
SELECT d.name, COUNT(*) AS headcount, ROUND(AVG(e.salary), 1) AS avg_salary
FROM employees e
JOIN departments d ON d.id = e.dept_id
WHERE e.salary > 5000
GROUP BY d.name
HAVING COUNT(*) >= 2
ORDER BY d.name
```
name headcount avg_salary
Engineering 3 7666.7
- Razorpay
List the dates on which the number of daily active users is odd.CoreOne report
Daily active users here means distinct customers with a completed order on a date. Group by date, count distinct customers, then keep odd counts with HAVING COUNT(DISTINCT customer_id) % 2 = 1. Some engines spell modulo MOD(x, 2).
Pitfalls: count distinct users, since one person can order twice in a day (2026-02-10 has three orders but only two customers); filter the status in WHERE so cancelled orders do not count; and remember that COUNT(DISTINCT col) skips NULLs. The guest order on 2026-03-01 has no customer, so that date counts one user, not two.
Variant: for weekly or monthly active users, group by a truncated date (DATE_TRUNC, or SUBSTR of an ISO text date as in SQLite). Follow-up: compare each date with the previous one using LAG.
query.sql
```
SELECT order_date, COUNT(DISTINCT customer_id) AS active_users
FROM orders
WHERE status = 'completed'
GROUP BY order_date
HAVING COUNT(DISTINCT customer_id) % 2 = 1
ORDER BY order_date
```
order_date active_users
2026-01-05 1
2026-02-03 1
2026-03-01 1
2026-03-15 1
2026-03-20 3
- CRED
- Candidate write-up · 2022 ↗

name	headcount	avg_salary
Engineering	3	7666.7

order_date	active_users
2026-01-05	1
2026-02-03	1
2026-03-01	1
2026-03-15	1
2026-03-20	3

Subqueries and CTEs

When do you use a subquery, a correlated subquery, a join or a CTE?CoreSeveral reports
A subquery is a query nested in another. A correlated subquery refers to the outer row and is conceptually re-run for each row; an uncorrelated one runs once. A CTE (WITH name AS (...)) names a subquery so the main query can reuse it and read top to bottom.
The example finds employees paid above their own department average. The CTE computes one average per department, a join attaches it to each employee, and WHERE compares. Asha and Dev qualify. Hari has no department, so he finds no partner in the join and is dropped.
Join versus subquery: the results are often the same and optimisers frequently rewrite one into the other, so choose for clarity. Use a join when you need columns from both sides, EXISTS for existence checks, and a window function (AVG(salary) OVER (PARTITION BY dept_id)) to avoid the second pass. Follow-up: a recursive CTE walks hierarchies.
query.sql
```
WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
)
SELECT e.name, e.salary, ROUND(a.avg_salary, 1) AS dept_avg
FROM employees e
JOIN dept_avg a ON a.dept_id = e.dept_id
WHERE e.salary > a.avg_salary
ORDER BY e.id
```
name salary dept_avg
Asha 9000 7666.7
Dev 8000 6000.0
Live on a given schema: return each customer's most recent completed order.CoreSeveral reports
A typical live task is "latest completed order for each customer". A correlated subquery solves it: keep an order only if its date equals the maximum completed date for the same customer.
Say your approach aloud: state the grain of the output (one row per customer), choose the grouping key, decide how cancelled rows are treated, and ask about ties. If a customer placed two orders on their latest day, this query returns both, as it does for customer 6 (orders 12 and 15). ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC, id DESC) with rn = 1 returns exactly one.
The guest order with a NULL customer is excluded because NULL = NULL is not true. That is usually right here, but say so. Follow-up: the same shape answers "maximum score for each student in the latest semester".
query.sql
```
SELECT o.customer_id, o.id AS order_id, o.order_date, o.amount
FROM orders o
WHERE o.status = 'completed'
  AND o.order_date = (
    SELECT MAX(i.order_date)
    FROM orders i
    WHERE i.customer_id = o.customer_id AND i.status = 'completed'
  )
ORDER BY o.customer_id
```
customer_id order_id order_date amount
1 11 2026-03-15 150
2 10 2026-03-01 600
3 14 2026-03-20 250
4 13 2026-03-20 900
6 12 2026-03-20 400
6 15 2026-03-20 600

name	salary	dept_avg
Asha	9000	7666.7
Dev	8000	6000.0

customer_id	order_id	order_date	amount
1	11	2026-03-15	150
2	10	2026-03-01	600
3	14	2026-03-20	250
4	13	2026-03-20	900
6	12	2026-03-20	400
6	15	2026-03-20	600

Window functions, ranking and top N

How do ROW_NUMBER, RANK and DENSE_RANK differ when there are ties?CoreSeveral reports
All three number rows inside an ordered window. ROW_NUMBER gives unique numbers and breaks ties arbitrarily unless you add a tiebreaker. RANK gives tied rows the same number and then skips ahead (1, 2, 3, 3, 5). DENSE_RANK gives tied rows the same number with no gaps (1, 2, 3, 3, 4).
In the output Bilal and Chen both earn 7000, so they share rank 3 and dense rank 3. The next salary, 6500, is rank 5 but dense rank 4. ROW_NUMBER gives them 3 and 4 by name order.
Use ROW_NUMBER to pick exactly one row per group (deduplication, latest record), RANK when competition-style gaps are wanted, and DENSE_RANK for "the Nth distinct value". Follow-up: NTILE and PERCENT_RANK. Window functions need SQLite 3.25 or newer.
query.sql
```
SELECT name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC, name) AS row_num,
  RANK() OVER (ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees
ORDER BY salary DESC, name
```
name salary row_num rnk dense_rnk
Asha 9000 1 1 1
Dev 8000 2 2 2
Bilal 7000 3 3 3
Chen 7000 4 3 3
Hari 6500 5 5 4
Esha 5000 6 6 5
Farid 5000 7 6 5
Gita 4000 8 8 6
Write a query for the Nth highest salary and say how ties behave.CoreSeveral reports
Use DENSE_RANK() OVER (ORDER BY salary DESC) and keep rank N. It counts distinct salaries, so N means the Nth highest value even with ties. The 3rd highest salary here is 7000, shared by Bilal and Chen, so two rows return.
Alternatives and their pitfalls:
- ORDER BY salary DESC LIMIT 1 OFFSET N-1 counts duplicates as separate positions. For the 4th highest, OFFSET 3 returns 7000 again instead of 6500 unless you select DISTINCT salary first.
- RANK skips numbers, so asking for the 4th highest returns nothing here, because the ranks run 1, 2, 3, 3, 5.
- The nested MAX(salary) WHERE salary < (SELECT MAX(salary) ...) works for the 2nd but nests badly for N.
If fewer than N distinct values exist, decide whether to return no rows or NULL. For a per-department answer add PARTITION BY dept_id.
query.sql
```
SELECT name, salary
FROM (
  SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) AS ranked
WHERE rnk = 3
ORDER BY name
```
name salary
Bilal 7000
Chen 7000
- PhonePe
- Amazon
How do you get the top N products within each category?DeepSeveral reports
Top N per group means ranking inside each group and then filtering. Compute revenue per category and product, rank with RANK() OVER (PARTITION BY category ORDER BY revenue DESC) in a CTE, and keep rnk <= 2. A window result cannot be filtered in the WHERE of the same SELECT (some engines add QUALIFY), so the filter sits in an outer query.
Ties decide the function. In Toys, Puzzle and Blocks both earn 600, so RANK returns three Toys rows, DENSE_RANK also keeps both, and ROW_NUMBER would keep an arbitrary one. State the rule you want before you write it.
Pitfalls: filter the status inside the revenue CTE, and agree whether top means revenue or units. Follow-up: without window functions, use a correlated count (keep a row when fewer than N products in its category earn more) or LATERAL and CROSS APPLY in engines that have them.
query.sql
```
WITH revenue AS (
  SELECT category, product, SUM(amount) AS total
  FROM orders
  WHERE status = 'completed'
  GROUP BY category, product
),
ranked AS (
  SELECT category, product, total,
    RANK() OVER (PARTITION BY category ORDER BY total DESC) AS rnk
  FROM revenue
)
SELECT category, product, total, rnk
FROM ranked
WHERE rnk <= 2
ORDER BY category, rnk, product
```
category product total rnk
Books Atlas 800 1
Books Novel 500 2
Games Chess 1200 1
Games Cards 150 2
Toys Robot 2700 1
Toys Blocks 600 2
Toys Puzzle 600 2
- Amazon
- Candidate write-up ↗
- Prep guide · 2026 ↗
Return the top three buyers by completed orders, and explain what happens with ties.CoreOne report
Count completed orders per customer, rank the counts, and keep ranks up to 3. LIMIT 3 is the tempting shortcut, but it cuts through ties arbitrarily. Here Omar has 4 orders and Maya 3, while three customer rows tie at 2 and all take rank 3, so five rows qualify and LIMIT 3 would drop two of them at random.
The ranking function matters once ties sit higher up. If two buyers tied for first, RANK would give the next buyer rank 3, while DENSE_RANK would give rank 2 and so return more people.
Join to customers after ranking, and exclude NULL customers (guest checkouts) so they do not form a group of their own. Another pitfall: two accounts with the same email show up as separate buyers, which the deduplication questions cover.
Ask the interviewer what top three means with ties and what should break them, such as revenue or earliest order, then add those as secondary sort keys inside the window.
query.sql
```
WITH counts AS (
  SELECT customer_id, COUNT(*) AS completed_orders
  FROM orders
  WHERE status = 'completed' AND customer_id IS NOT NULL
  GROUP BY customer_id
),
ranked AS (
  SELECT customer_id, completed_orders,
    RANK() OVER (ORDER BY completed_orders DESC) AS rnk
  FROM counts
)
SELECT c.id, c.name, r.completed_orders, r.rnk
FROM ranked r
JOIN customers c ON c.id = r.customer_id
WHERE r.rnk <= 3
ORDER BY r.rnk, c.id
```
id name completed_orders rnk
3 Omar 4 1
1 Maya 3 2
2 Noor 2 3
4 Maya 2 3
6 Noor 2 3
- CRED
- Candidate write-up · 2022 ↗

name	salary	row_num	rnk	dense_rnk
Asha	9000	1	1	1
Dev	8000	2	2	2
Bilal	7000	3	3	3
Chen	7000	4	3	3
Hari	6500	5	5	4
Esha	5000	6	6	5
Farid	5000	7	6	5
Gita	4000	8	8	6

name	salary
Bilal	7000
Chen	7000

category	product	total	rnk
Books	Atlas	800	1
Books	Novel	500	2
Games	Chess	1200	1
Games	Cards	150	2
Toys	Robot	2700	1
Toys	Blocks	600	2
Toys	Puzzle	600	2

id	name	completed_orders	rnk
3	Omar	4	1
1	Maya	3	2
2	Noor	2	3
4	Maya	2	3
6	Noor	2	3

How do you compute a running total or rolling window with a window frame?DeepPrep guides

A running total is a window SUM over an ordered frame. Aggregate to one row per day first, then use SUM(SUM(amount)) OVER (PARTITION BY category ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). The inner SUM is the group aggregate and the outer one is the window.

State the frame. With only ORDER BY, the default is a RANGE frame that treats rows with equal sort keys as peers and adds them together, while ROWS counts physical rows. The two differ when the same key repeats within a partition.

A rolling 7-day window is ROWS BETWEEN 6 PRECEDING AND CURRENT ROW, which is correct only if every day has a row. Otherwise join to a calendar table or use a RANGE frame over dates where the engine supports it. Follow-up: retention, which compares each user's events with a week later.

query.sql

SELECT category, order_date,
  SUM(amount) AS day_revenue,
  SUM(SUM(amount)) OVER (
    PARTITION BY category
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_revenue
FROM orders
WHERE status = 'completed'
GROUP BY category, order_date
ORDER BY category, order_date

category	order_date	day_revenue	running_revenue
Books	2026-01-05	400	400
Books	2026-01-20	250	650
Books	2026-02-10	250	900
Books	2026-03-20	650	1550
Games	2026-02-10	600	600
Games	2026-03-01	600	1200
Games	2026-03-15	150	1350
Toys	2026-01-20	900	900
Toys	2026-02-03	900	1800
Toys	2026-02-10	300	2100
Toys	2026-03-01	300	2400
Toys	2026-03-20	1500	3900

Prep guide · 2026 ↗

Report revenue per month and the change from the previous month.DeepOne report
Aggregate to one row per month, then compare each month with the one before using LAG(revenue) OVER (ORDER BY month). The first month has no predecessor, so LAG returns NULL and the difference is NULL. Pass a third argument, LAG(revenue, 1, 0), if you want a default.
The month key here is SUBSTR(order_date, 1, 7), which works because dates are ISO text. Other engines use DATE_TRUNC('month', d), TO_CHAR or EXTRACT, and SQLite also has strftime('%Y-%m', d).
Pitfalls: LAG looks at the previous existing row, so a month with no sales is skipped rather than shown as zero; and a growth percentage divides by the previous value, which needs NULLIF(prev, 0). Variant: units, average selling price and revenue per month in one pass with COUNT, AVG and SUM.
query.sql
```
WITH monthly AS (
  SELECT SUBSTR(order_date, 1, 7) AS month, SUM(amount) AS revenue
  FROM orders
  WHERE status = 'completed'
  GROUP BY SUBSTR(order_date, 1, 7)
)
SELECT month, revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly
ORDER BY month
```
month revenue prev_revenue change
2026-01 1550 NULL NULL
2026-02 2050 1550 500
2026-03 3200 2050 1150
- Amazon
- Candidate write-up ↗

month	revenue	prev_revenue	change
2026-01	1550	NULL	NULL
2026-02	2050	1550	500
2026-03	3200	2050	1150

Set operations and NULL logic

What is the difference between UNION and UNION ALL?CoreSeveral reports
UNION combines two result sets and removes duplicate rows. UNION ALL keeps every row. Both need the same number of columns with compatible types, and the column names come from the first query.
The example takes the cities of two departments. UNION returns 3 rows (Delhi, Mumbai, Pune) while UNION ALL returns 6. Removing duplicates means sorting or hashing the whole result, so UNION ALL is cheaper. Prefer it when the inputs cannot overlap or when duplicates matter, as when summing across partitions.
Related operators: INTERSECT returns rows present in both inputs and EXCEPT (MINUS in Oracle) returns rows only in the first; both deduplicate. Pitfall: set operators treat two NULLs as equal for duplicate removal, unlike =. An ORDER BY applies to the whole combined result and goes last.
query.sql
```
SELECT 'UNION' AS operator, COUNT(*) AS rows_returned
FROM (
  SELECT city FROM employees WHERE dept_id = 1
  UNION
  SELECT city FROM employees WHERE dept_id = 2
) AS u
UNION ALL
SELECT 'UNION ALL', COUNT(*)
FROM (
  SELECT city FROM employees WHERE dept_id = 1
  UNION ALL
  SELECT city FROM employees WHERE dept_id = 2
) AS a
```
operator rows_returned
UNION 3
UNION ALL 6
- Amazon
- Candidate write-up ↗
- Prep guide ↗
How does NULL behave in comparisons, aggregates and joins?CoreSeveral reports
NULL means unknown, so a comparison with it is neither true nor false. x = NULL and x <> NULL are unknown and filter out every row, so test with IS NULL or IS NOT NULL. Aggregates skip NULLs: COUNT(col) and AVG(col) ignore them, while COUNT(*) counts rows.
In a join, a NULL key never equals anything, not even another NULL. The query shows 15 orders, 14 with a customer, 14 rows from the inner join, and 1 order with a NULL key that only a left join would keep. A NULL inside a NOT IN list means the predicate is never true, so no rows come back.
Handy tools: COALESCE(col, 0), NULLIF(a, b) to avoid division by zero, and a null-safe equality (IS NOT DISTINCT FROM, IS in SQLite, <=> in MySQL). Where NULLs sort differs by engine.
query.sql
```
SELECT
  (SELECT COUNT(*) FROM orders) AS all_orders,
  (SELECT COUNT(customer_id) FROM orders) AS with_customer,
  (SELECT COUNT(*) FROM orders o JOIN customers c ON c.id = o.customer_id) AS inner_join_rows,
  (SELECT COUNT(*) FROM orders WHERE customer_id IS NULL) AS null_key_rows
```
all_orders with_customer inner_join_rows null_key_rows
15 14 14 1
- Paytm
- Candidate write-up ↗
- Prep guide ↗

operator	rows_returned
UNION	3
UNION ALL	6

all_orders	with_customer	inner_join_rows	null_key_rows
15	14	14	1

Deduplication

How do you find duplicate rows in a table?CoreSeveral reports
Group by the column that should be unique and keep the groups with more than one row: GROUP BY email HAVING COUNT(*) > 1. The output lists each duplicated email, how many copies exist, and the lowest id, which is the usual survivor.
Clarify what duplicate means before writing. Is it the email alone or the whole row? Do case and whitespace count (LOWER(TRIM(email)))? Which copy is kept, the lowest id or the newest? Whole-row duplicates use SELECT DISTINCT or group by every column.
Prevent them rather than only finding them: add a unique index on the normalised email, such as LOWER(email) where the engine supports expression indexes. Follow-ups are how to delete the extras, covered in the next question, and what to do with rows in other tables that reference the duplicates.
query.sql
```
SELECT email, COUNT(*) AS copies, MIN(id) AS keep_id
FROM customers
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY email
```
email copies keep_id
maya@example.com 2 1
noor@example.com 2 2
- Flipkart
- Candidate write-up ↗
- Prep guide ↗
How do you delete duplicate rows but keep one copy of each?DeepSeveral reports
First select the rows to remove, then delete exactly those. ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) numbers the copies of each email, so every row with rn > 1 is a surplus copy and the lowest id survives. The query lists those rows: ids 4 and 6.
A portable delete is DELETE FROM customers WHERE id NOT IN (SELECT MIN(id) FROM customers GROUP BY email). MySQL rejects selecting from the table being deleted unless the subquery is wrapped in a derived table. PostgreSQL can use USING, and SQL Server can delete through a CTE.
Before running it, repoint dependent rows. Orders 8, 12, 13 and 15 reference customers 4 and 6, so a foreign key would block the delete or leave orphans. Run the select first, wrap the work in a transaction, and add a unique constraint afterwards. With no key at all, copy the distinct rows into a new table and swap it in.
query.sql
```
SELECT id, name, email
FROM (
  SELECT id, name, email,
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
  FROM customers
) AS numbered
WHERE rn > 1
ORDER BY id
```
id name email
4 Maya maya@example.com
6 Noor noor@example.com
- Flipkart
- Candidate write-up ↗
- Prep guide ↗

email	copies	keep_id
maya@example.com	2	1
noor@example.com	2	2

id	name	email
4	Maya	maya@example.com
6	Noor	noor@example.com

Keep going

Companies do not publish their questions and rotate them. These come from public candidate write-ups and prep guides, so treat the labels as one person's account or one list's tag. The answers are written for this site.

Now make it stick

Run a mock interview Back to all questions