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 (Common Table Expression) 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.