SQL Interview Questions With Worked Answers

All questions below use this schema. Create it in any PostgreSQL or MySQL database to follow along:

CREATE TABLE members (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  joined_at DATE NOT NULL
);

CREATE TABLE contributions (
  id SERIAL PRIMARY KEY,
  member_id INT REFERENCES members(id),
  amount DECIMAL(10, 2) NOT NULL,
  contributed_at DATE NOT NULL,
  status VARCHAR(20) DEFAULT 'confirmed'
);

INSERT INTO members (name, email, joined_at) VALUES
  ('Wanjiku Kamau', 'wanjiku@example.com', '2024-01-15'),
  ('Otieno Odhiambo', 'otieno@example.com', '2024-02-01'),
  ('Amina Hassan', 'amina@example.com', '2024-03-10'),
  ('Brian Mwangi', 'brian@example.com', '2024-06-01');

INSERT INTO contributions (member_id, amount, contributed_at, status) VALUES
  (1, 5000, '2024-04-01', 'confirmed'),
  (1, 5000, '2024-05-01', 'confirmed'),
  (2, 3000, '2024-04-01', 'confirmed'),
  (2, 5000, '2024-05-01', 'confirmed'),
  (3, 5000, '2024-04-01', 'confirmed'),
  (3, 0, '2024-05-01', 'missed'),
  (1, 5000, '2024-06-01', 'confirmed');

Q: List all members with their total confirmed contributions.

SELECT m.name, COALESCE(SUM(c.amount), 0) AS total
FROM members m
LEFT JOIN contributions c
  ON m.id = c.member_id AND c.status = 'confirmed'
GROUP BY m.id, m.name
ORDER BY total DESC;

We use LEFT JOIN so members with no contributions (Brian) still appear. COALESCE turns NULL sums into 0.

Q: Find members who have never contributed.

SELECT m.name
FROM members m
LEFT JOIN contributions c ON m.id = c.member_id
WHERE c.id IS NULL;

Brian joined in June and has no contribution records. The WHERE c.id IS NULL filter keeps only members with no matching rows in contributions.

Q: Which members contributed more than KES 8,000 total?

SELECT m.name, SUM(c.amount) AS total
FROM members m
JOIN contributions c ON m.id = c.member_id
WHERE c.status = 'confirmed'
GROUP BY m.id, m.name
HAVING SUM(c.amount) > 8000
ORDER BY total DESC;

HAVING filters after grouping. WHERE filters before grouping. Use WHERE for row-level conditions and HAVING for aggregate conditions.

Q: Count how many contributions each member made per month.

SELECT m.name,
  DATE_TRUNC('month', c.contributed_at) AS month,
  COUNT(*) AS contributions
FROM members m
JOIN contributions c ON m.id = c.member_id
GROUP BY m.id, m.name, month
ORDER BY m.name, month;

Q: Find the member with the single largest contribution.

SELECT m.name, c.amount, c.contributed_at
FROM contributions c
JOIN members m ON m.id = c.member_id
WHERE c.amount = (SELECT MAX(amount) FROM contributions);

Q: List members whose total contributions are above the average member total.

SELECT m.name, SUM(c.amount) AS total
FROM members m
JOIN contributions c ON m.id = c.member_id
WHERE c.status = 'confirmed'
GROUP BY m.id, m.name
HAVING SUM(c.amount) > (
  SELECT AVG(member_total)
  FROM (
    SELECT SUM(amount) AS member_total
    FROM contributions
    WHERE status = 'confirmed'
    GROUP BY member_id
  ) sub
);

Q: Rank members by their total contributions.

SELECT m.name,
  SUM(c.amount) AS total,
  RANK() OVER (ORDER BY SUM(c.amount) DESC) AS rank
FROM members m
JOIN contributions c ON m.id = c.member_id
WHERE c.status = 'confirmed'
GROUP BY m.id, m.name;

Window functions like RANK(), ROW_NUMBER(), and DENSE_RANK() compute a value across a set of rows without collapsing them. They are powerful for leaderboards, running totals, and moving averages.

Q: Show each contribution with a running total per member.

SELECT m.name, c.contributed_at, c.amount,
  SUM(c.amount) OVER (
    PARTITION BY m.id
    ORDER BY c.contributed_at
  ) AS running_total
FROM contributions c
JOIN members m ON m.id = c.member_id
WHERE c.status = 'confirmed'
ORDER BY m.name, c.contributed_at;