SQL

CREATE TABLE sales (
  id        BIGINT PRIMARY KEY,
  region    TEXT NOT NULL,
  rep_name  TEXT NOT NULL,
  amount    NUMERIC NOT NULL,
  sold_at   DATE NOT NULL
);

Return the top 2 sales reps in each region by total amount. If two reps tie, both should appear and the next rep should be skipped (classic 1, 2, 2, 4 ranking).

SELECT region, rep_name, total
FROM (
  SELECT region, rep_name, SUM(amount) AS total,
         RANK() OVER (PARTITION BY region ORDER BY SUM(amount) DESC) AS rk
  FROM sales
  GROUP BY region, rep_name
) t
WHERE rk <= 2;

RANK() produces 1, 2, 2, 4: exactly the 'include ties, skip the next slot' semantics asked for. ROW_NUMBER (a) breaks ties arbitrarily and would drop a tied rep. DENSE_RANK (c) would let a third rep through because it produces 1, 2, 2, 3. LIMIT 2 (d) returns 2 rows globally, not 2 per region.

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

CREATE TABLE orders (
  id          BIGINT PRIMARY KEY,
  customer_id BIGINT,  -- nullable: guest checkouts also live here
  total       NUMERIC NOT NULL
);

Return every customer who has never placed an order. Note that orders.customer_id is nullable because guest checkouts are stored in the same table.

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

LEFT JOIN ... WHERE o.id IS NULL is the textbook anti-join and is NULL-safe. Option (a) is the classic NULL trap: NOT IN against a subquery that contains any NULL returns no rows because 'x NOT IN (..., NULL, ...)' evaluates to UNKNOWN. (c) is logically impossible since INNER JOIN guarantees o.id is not null. (d) just checks whether any guest order exists anywhere, it has no correlation to c.

CREATE TABLE daily_revenue (
  day     DATE PRIMARY KEY,
  amount  NUMERIC NOT NULL
);

Return each day with a running total of revenue from the earliest day through that day, ordered by day.

SELECT day, amount,
       SUM(amount) OVER (ORDER BY day
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM daily_revenue
ORDER BY day;

A windowed SUM with ORDER BY and an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame is the canonical running total. (a) and (d) return the grand total on every row because there is no ORDER BY in the window. (c) partitions by day, so each window contains a single row and 'running_total' just equals amount.

CREATE TABLE employees (
  id         BIGINT PRIMARY KEY,
  department TEXT NOT NULL,
  salary     NUMERIC NOT NULL
);

Return the median salary per department in PostgreSQL. Use a continuous median (interpolate between the two middle values for even-sized groups).

SELECT department,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees
GROUP BY department;

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) is Postgres's continuous median and interpolates between the two middle values. PERCENTILE_DISC (c) returns the lower of the two middle values, which is a discrete median. AVG (a) is the mean, not the median. (d) uses a MEDIAN() function that does not exist in Postgres.

CREATE TABLE employees (
  id         BIGINT PRIMARY KEY,
  name       TEXT NOT NULL,
  manager_id BIGINT REFERENCES employees(id),
  salary     NUMERIC NOT NULL
);

Return the names of employees who earn strictly more than their direct manager. Employees with no manager should not appear.

SELECT e.name
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.salary > m.salary;

An INNER self-join on manager_id pairs each employee with their direct manager and excludes top-level employees (manager_id IS NULL) automatically. (a) is a cross join and compares everyone to everyone. (b) re-adds top-level employees via the OR clause, violating the spec. (d) compares to the company average, not to the manager.

CREATE TABLE employees (
  id         BIGINT PRIMARY KEY,
  name       TEXT NOT NULL,
  manager_id BIGINT REFERENCES employees(id)
);

Given a CEO with manager_id IS NULL, return every employee in the org along with their depth (CEO = 0, direct reports = 1, etc.).

WITH RECURSIVE tree AS (
  SELECT id, name, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, t.depth + 1
  FROM employees e
  JOIN tree t ON e.manager_id = t.id
)
SELECT * FROM tree;

A recursive CTE with the RECURSIVE keyword anchors on the CEO and walks down one level per iteration. (c) omits RECURSIVE, so Postgres treats 'tree' inside the second SELECT as an unknown table and the query fails. (a) caps depth at 1. (d) just counts how many managers a person has (0 or 1), which is not depth.

CREATE TABLE employees (
  id         BIGINT PRIMARY KEY,
  department TEXT NOT NULL
);

Return the names of departments that have at least 3 employees, alphabetically sorted.

SELECT department
FROM employees
GROUP BY department
HAVING COUNT(*) >= 3
ORDER BY department;

Filtering aggregates requires HAVING, which runs after GROUP BY. (a) and (c) try to use WHERE on COUNT(*), which is a syntax error because WHERE is evaluated before grouping. (d) applies HAVING to the whole result set (no GROUP BY), so it returns either all departments or none depending on the total row count.

CREATE TABLE users (
  id           BIGINT PRIMARY KEY,
  email        TEXT NOT NULL,
  phone        TEXT          -- nullable
);

Return a single row with two columns: total_users (every row) and users_with_phone (rows where phone is set).

SELECT COUNT(*) AS total_users,
       COUNT(phone) AS users_with_phone
FROM users;

COUNT(*) counts rows including NULLs, while COUNT(col) skips rows where col IS NULL, that is exactly the distinction we need. (a) counts every row twice. (b) is correct in practice but only because id is NOT NULL; COUNT(*) is the clearer intent. (d) tries to SUM a text column, which errors.

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

CREATE TABLE orders (
  id          BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id),
  total       NUMERIC NOT NULL
);

For a report, return every customer along with their order total. Customers who have placed no orders should still appear (with total = 0).

SELECT c.id, c.name, COALESCE(SUM(o.total), 0) AS total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

LEFT JOIN keeps every customer and pads missing orders with NULL, which COALESCE turns into 0. INNER JOIN (a) drops customers with no orders. RIGHT JOIN (c) keeps every order: fine here since orders.customer_id is NOT NULL, but it does not guarantee customers with no orders are kept. FULL OUTER (d) is overkill and would also keep orphan orders if customer_id were nullable.

CREATE TABLE products (
  id    BIGINT PRIMARY KEY,
  name  TEXT NOT NULL
);

CREATE TABLE returns (
  id         BIGINT PRIMARY KEY,
  product_id BIGINT  -- nullable: 'unknown product' returns
);

Return the names of products that have NEVER been returned. The returns table can contain rows with product_id IS NULL.

SELECT name
FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM returns r WHERE r.product_id = p.id
);

NOT EXISTS is NULL-safe: 'r.product_id = p.id' is simply false when r.product_id IS NULL, so those rows are ignored. NOT IN (a) returns zero rows because the subquery contains a NULL: 'x NOT IN (..., NULL)' evaluates to UNKNOWN, never true. (c) is a logic error: '!= ANY' is true if any value differs, so it is almost always true. (d) does not filter at all.

CREATE TABLE page_views (
  user_id BIGINT NOT NULL,
  page    TEXT   NOT NULL,
  viewed_at TIMESTAMPTZ NOT NULL
);

Return each unique (user_id, page) pair along with the number of views for that pair. Order by views desc.

SELECT user_id, page, COUNT(*) AS views
FROM page_views
GROUP BY user_id, page
ORDER BY views DESC;

GROUP BY user_id, page collapses to unique pairs and lets COUNT(*) count rows per pair. (a) is invalid in Postgres: COUNT(*) without GROUP BY collapses the whole table to one row, and DISTINCT cannot 'undo' that. (c) groups by user_id only, which means page is not a valid bare column. (d) counts distinct timestamps and is missing the GROUP BY entirely.

CREATE TABLE users (
  id    BIGINT PRIMARY KEY,
  email TEXT NOT NULL
);

Return every email address that is associated with more than one user row, along with how many times it appears.

SELECT email, COUNT(*) AS n
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

GROUP BY email + HAVING COUNT(*) > 1 is the canonical duplicate-finder. (a) puts the aggregate filter in WHERE, which is a syntax error. (c) returns every distinct email, not just duplicates, and the DISTINCT is redundant once GROUP BY is present. (d) duplicates rows by self-join and hardcodes n=2, wrong for emails that appear 3+ times.

CREATE TABLE signups (
  user_id    BIGINT PRIMARY KEY,
  signed_up  TIMESTAMPTZ NOT NULL
);

CREATE TABLE sessions (
  id         BIGINT PRIMARY KEY,
  user_id    BIGINT NOT NULL,
  started_at TIMESTAMPTZ NOT NULL
);

In PostgreSQL, return the user_ids of people who started at least one session strictly AFTER their signup and within 7 days of signing up (i.e. signup < session <= signup + 7 days).

SELECT DISTINCT s.user_id
FROM signups s
JOIN sessions x ON x.user_id = s.user_id
WHERE x.started_at >  s.signed_up
  AND x.started_at <= s.signed_up + INTERVAL '7 days';

Postgres supports INTERVAL arithmetic directly, and the half-open inequalities match the spec exactly. (a) compares a TIMESTAMPTZ subtraction (which yields an INTERVAL) to a bare number, invalid. (c) uses DATEDIFF, which is SQL Server / MySQL, not Postgres. (d) adds a bare integer 7 to a TIMESTAMPTZ (error) and uses BETWEEN, which includes both endpoints (so signup itself counts).

CREATE TABLE orders (
  id        BIGINT PRIMARY KEY,
  region    TEXT NOT NULL,
  total     NUMERIC NOT NULL,
  placed_at DATE NOT NULL
);

In PostgreSQL, pivot 2025 orders into one row per region with columns q1, q2, q3, q4 holding the sum of total for that quarter.

SELECT region,
       SUM(CASE WHEN EXTRACT(QUARTER FROM placed_at) = 1 THEN total ELSE 0 END) AS q1,
       SUM(CASE WHEN EXTRACT(QUARTER FROM placed_at) = 2 THEN total ELSE 0 END) AS q2,
       SUM(CASE WHEN EXTRACT(QUARTER FROM placed_at) = 3 THEN total ELSE 0 END) AS q3,
       SUM(CASE WHEN EXTRACT(QUARTER FROM placed_at) = 4 THEN total ELSE 0 END) AS q4
FROM orders
WHERE EXTRACT(YEAR FROM placed_at) = 2025
GROUP BY region;

Conditional aggregation (SUM(CASE WHEN ... )) is the portable Postgres pivot pattern: one row per region, one column per quarter. (b) puts CASE outside SUM, so SUM still collapses by group and the CASE just nullifies most rows; the extra GROUP BY placed_at also breaks the row-per-region requirement. (c) leaves the result long, not pivoted. (d) uses Oracle/T-SQL PIVOT syntax that Postgres does not support.

CREATE TABLE users (
  id      BIGINT PRIMARY KEY,
  status  TEXT NOT NULL
);

CREATE TABLE orders (
  id          BIGINT PRIMARY KEY,
  user_id     BIGINT NOT NULL REFERENCES users(id),
  placed_at   TIMESTAMPTZ NOT NULL
);

In PostgreSQL, set status = 'inactive' for every user who has placed no orders in the last 365 days (including users who have NEVER placed an order). Update only users currently not already inactive.

UPDATE users u
SET status = 'inactive'
WHERE u.status <> 'inactive'
  AND NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id
      AND o.placed_at >= NOW() - INTERVAL '365 days'
  );

NOT EXISTS asks 'does this user have ANY recent order?' and negates it, which naturally includes users with zero orders. (a) only marks users who DO have an old order and still joins to the orders table, missing never-ordered users entirely. (c) works only if orders.user_id is guaranteed NOT NULL: it is here, but the bigger issue is (d) would never match never-ordered users because MAX returns NULL and NULL < anything is UNKNOWN, not true.

CREATE TABLE users (
  id     BIGINT PRIMARY KEY,
  email  TEXT NOT NULL,
  phone  TEXT  -- NULL when the user skipped it at signup
);

Return every user who has not added a phone number. A skipped phone is stored as NULL.

SELECT id, email
FROM users
WHERE phone IS NULL;

NULL means unknown, so phone = NULL evaluates to NULL for every row, and WHERE keeps only rows where the condition is true: (a) returns nothing. IS NULL is the test that matches missing values. (c) looks for empty strings, which Postgres treats as a different value from NULL. (d) returns users who do have a phone, and it also skips the NULL rows, since NULL <> '' is unknown too.

CREATE TABLE orders (
  id      BIGINT PRIMARY KEY,
  status  TEXT NOT NULL,     -- 'pending', 'shipped', 'delivered', 'cancelled'
  total   NUMERIC NOT NULL
);

Return the orders that are shipped or delivered and have a total over 100.

SELECT id, status, total
FROM orders
WHERE (status = 'shipped' OR status = 'delivered') AND total > 100;

AND binds tighter than OR, so (a) reads as status = 'shipped' OR (status = 'delivered' AND total > 100) and returns every shipped order whatever its total. The parentheses in (c) group the two statuses first. (b) can never be true, because one row has one status. (d) is an error in Postgres: 'delivered' cannot be read as a boolean, so it cannot be an operand of AND or OR.

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

CREATE TABLE employees (
  id             BIGINT PRIMARY KEY,
  name           TEXT NOT NULL,
  department_id  BIGINT REFERENCES departments(id)  -- NULL for new hires not yet placed
);

List every employee with their department name. New hires who are not in a department yet must still appear, with a NULL department name.

SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON d.id = e.department_id;

A LEFT JOIN keeps every row from the left table (employees) and fills the right side with NULLs when nothing matches, which is exactly what the unplaced new hires need. INNER JOIN (a) drops them. RIGHT JOIN (b) keeps every department instead, so empty departments show up and unplaced employees disappear. (c) starts out right, then the WHERE filter throws away the NULL rows the LEFT JOIN kept, which turns it back into an inner join.

CREATE TABLE orders (
  id           BIGINT PRIMARY KEY,
  customer_id  BIGINT NOT NULL,
  total        NUMERIC NOT NULL
);

Return each customer_id with more than 5 orders, along with their order count.

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

WHERE filters rows before grouping, so it cannot see an aggregate: (a) fails with 'aggregate functions are not allowed in WHERE'. HAVING filters groups after GROUP BY, which is where COUNT(*) exists. (c) is a MySQL habit: Postgres does not let HAVING refer to a SELECT alias, so it fails with column "order_count" does not exist. (d) returns the top 5 customers, which answers a different question.

CREATE TABLE orders (
  id           BIGINT PRIMARY KEY,
  customer_id  BIGINT NOT NULL,
  status       TEXT NOT NULL,
  total        NUMERIC NOT NULL
);

Return the total amount each customer has spent, one row per customer.

SELECT customer_id, SUM(total) AS spent
FROM orders
GROUP BY customer_id;

Every column in SELECT must be in GROUP BY or inside an aggregate. (a) has an aggregate but no GROUP BY, so Postgres rejects customer_id with 'must appear in the GROUP BY clause or be used in an aggregate function'. (c) fails the same way on status. (d) runs, but a window function keeps one row per order, and SUM(total) OVER () puts the grand total of the whole table on every row.

CREATE TABLE orders (
  id           BIGINT PRIMARY KEY,
  customer_id  BIGINT NOT NULL,
  placed_at    TIMESTAMPTZ NOT NULL
);

How many different customers placed at least one order in October 2026? placed_at is a timestamp, so orders land at any time of day, including late on October 31.

SELECT COUNT(DISTINCT customer_id)
FROM orders
WHERE placed_at >= '2026-10-01' AND placed_at < '2026-11-01';

COUNT(DISTINCT customer_id) counts each customer once, and the half-open range from October 1 up to (but excluding) November 1 covers every moment of October. (a) and (c) count orders: COUNT(customer_id) only skips NULLs, so a repeat customer is still counted once per order. (b) counts the right thing over the wrong range: '2026-10-31' means midnight at the start of October 31, so BETWEEN misses everything placed later that day.

CREATE TABLE products (
  id     BIGINT PRIMARY KEY,
  name   TEXT NOT NULL,
  price  NUMERIC  -- NULL until the product is priced
);

Return the 3 most expensive products. Unpriced products (NULL price) should not appear.

SELECT name, price
FROM products
WHERE price IS NOT NULL
ORDER BY price DESC
LIMIT 3;

Postgres sorts NULL as larger than any value, so ORDER BY price DESC puts the unpriced products first and (a) can return three NULLs. Filtering them out with WHERE price IS NOT NULL (b) fixes that. (c) returns the cheapest products. (d) only returns products tied for the single highest price, which is usually one row.

CREATE TABLE users (
  id     BIGINT PRIMARY KEY,
  email  TEXT NOT NULL
);

Find every user whose email ends in @example.com. Some addresses were typed with capitals, like Sam@Example.com.

SELECT id, email
FROM users
WHERE email ILIKE '%@example.com';

ILIKE is the case-insensitive version of LIKE in Postgres, and the leading % matches any prefix, so (c) finds Sam@Example.com too. LIKE (a) is case-sensitive in Postgres and misses the capitalized addresses. (b) compares against the literal text '%@example.com', because = does no pattern matching. (d) puts the wildcard at the wrong end and matches emails that start with @example.com.

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

CREATE TABLE orders (
  id           BIGINT PRIMARY KEY,
  customer_id  BIGINT NOT NULL REFERENCES customers(id),
  total        NUMERIC NOT NULL
);

List every customer's email with their number of orders. Customers who have never ordered must appear with a count of 0.

SELECT c.email, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.email;

The LEFT JOIN from customers keeps a customer with no orders as one row whose order columns are NULL. COUNT(o.id) skips NULLs, so that customer gets 0. COUNT(*) in (a) counts the row itself and reports 1 for someone who never ordered. INNER JOIN (b) drops customers without orders entirely. (d) puts orders on the left, so it keeps every order and loses the customers who have none.

CREATE TABLE reviews (
  id          BIGINT PRIMARY KEY,
  product_id  BIGINT NOT NULL,
  rating      INT NOT NULL CHECK (rating BETWEEN 1 AND 5)
);

Return each product_id with an average rating of at least 4 across at least 10 reviews, along with the average and the review count.

SELECT product_id, AVG(rating) AS avg_rating, COUNT(*) AS review_count
FROM reviews
GROUP BY product_id
HAVING AVG(rating) >= 4 AND COUNT(*) >= 10;

Both conditions are about the whole group, so both belong in HAVING, joined with AND. (a) filters rows before grouping: it averages and counts only the 4 and 5 star reviews, so a product with ten 5s and ten 1s passes with a reported average of 5.0. (c) accepts a product that meets either condition, including one with hundreds of 2 star reviews. (d) drops the count, so a single 5 star review is enough.

CREATE TABLE employees (
  id      BIGINT PRIMARY KEY,
  name    TEXT NOT NULL,
  salary  NUMERIC NOT NULL,
  bonus   NUMERIC  -- NULL when the employee has no bonus
);

Return each employee's name and total pay, which is salary plus bonus. Employees without a bonus have NULL in bonus, and their total pay is their salary.

SELECT name, salary + COALESCE(bonus, 0) AS total_pay
FROM employees;

Any arithmetic with NULL returns NULL, so (a) shows a NULL total for everyone without a bonus. COALESCE(bonus, 0) in (b) swaps the missing bonus for 0 before the addition. (c) applies COALESCE after the addition has already produced NULL, so those employees show a total pay of 0. NULLIF(bonus, 0) in (d) works the other way round: it turns a bonus of 0 into NULL and leaves NULL as NULL, which makes more totals NULL.

CREATE TABLE posts (
  id            BIGINT PRIMARY KEY,
  title         TEXT NOT NULL,
  published_at  TIMESTAMPTZ NOT NULL
);

The blog shows 10 posts per page, newest first. Return page 2, which is posts 11 through 20.

SELECT id, title, published_at
FROM posts
ORDER BY published_at DESC, id DESC
LIMIT 10 OFFSET 10;

OFFSET skips (page - 1) * page_size rows, so page 2 skips 10 and LIMIT takes the next 10. (a) skips 20 and returns page 3. (b) has no ORDER BY, and without one the database may return rows in any order, which can differ between runs. (d) sorts oldest first. The id DESC tiebreaker in (c) gives posts with the same published_at a fixed order, so none of them repeats or disappears between pages.