Classic SQL Interview Queries for QA (Worked Set)

📌 Definition:

📖 Detailed Explanation:

Interview SQL questions recycle a small number of patterns; recognizing which pattern a question maps to is most of the battle. Nth-highest → DENSE_RANK (or a MAX-of-lesser subquery). Find duplicates → GROUP BY … HAVING COUNT(*) > 1. Top-N per group → ROW_NUMBER/RANK with PARTITION BY. In-A-not-in-B → LEFT JOIN … IS NULL, NOT EXISTS, or EXCEPT. Second-table existence checks → EXISTS. Per-group aggregates with a threshold → GROUP BY … HAVING. Because these come up constantly, the productive study method is to keep one small schema in mind and solve each pattern against it, articulating the trade-offs (window function vs subquery, NOT EXISTS vs NOT IN) as you go — which is exactly what interviewers listen for. The worked answers below assume the users/orders/products schema used throughout this bank.

🔑 Key Points:

• Nth-highest distinct → DENSE_RANK() = N (or MAX where value < previous MAX)
• Duplicates → GROUP BY key HAVING COUNT(*) > 1
• Top-N per group → ROW_NUMBER()/RANK() OVER (PARTITION BY group ORDER BY metric)
• In-A-not-in-B → NOT EXISTS / LEFT JOIN … IS NULL / EXCEPT (prefer NOT EXISTS with NULLs)
• Recognize the pattern first, then state the technique and its trade-offs aloud

🌍 Real-World Example:

📎 Code Example:

-- 1) Second-highest DISTINCT order total
SELECT total FROM (
  SELECT total, DENSE_RANK() OVER (ORDER BY total DESC) AS r FROM orders
) t WHERE r = 2;

-- 2) Duplicate customer emails
SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;

-- 3) Most expensive product in each category
SELECT category, name, price FROM (
  SELECT category, name, price,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
  FROM products
) t WHERE rn = 1;

-- 4) Users who never placed an order (null-safe anti-join)
SELECT u.id, u.email FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 5) Running total of orders per day
SELECT created_at::date AS day,
       COUNT(*) OVER (ORDER BY created_at::date) AS running_orders
FROM orders;