SQL Interview Questions — Top 20 for QA

-- Find duplicate emails in customers table
SELECT email, COUNT(*) AS count
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

-- Find the 3rd most expensive product
SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 1 OFFSET 2;

-- Alternative using window function
SELECT product_name, price FROM (
  SELECT product_name, price,
    DENSE_RANK() OVER (ORDER BY price DESC) AS rnk
  FROM products
) ranked
WHERE rnk = 3;

-- Keep the row with the lowest ID, delete the rest
DELETE c1 FROM customers c1
INNER JOIN customers c2
  ON c1.email = c2.email
  AND c1.customer_id > c2.customer_id;

-- Safer approach: identify first, then delete
SELECT * FROM customers
WHERE customer_id NOT IN (
  SELECT MIN(customer_id)
  FROM customers
  GROUP BY email
);

-- Method 1: LEFT JOIN
SELECT c.customer_id, c.first_name, c.last_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

-- Method 2: NOT EXISTS
SELECT customer_id, first_name, last_name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
);

-- Method 3: NOT IN
SELECT customer_id, first_name, last_name
FROM customers
WHERE customer_id NOT IN (
  SELECT DISTINCT customer_id FROM orders
);

-- UNION removes duplicates (slower — has to sort and compare)
SELECT city FROM customers
UNION
SELECT city FROM customers;
-- Returns unique cities only

-- UNION ALL keeps all rows including duplicates (faster)
SELECT city FROM customers
UNION ALL
SELECT city FROM customers;
-- Returns every city twice

-- Classic interview question — 4 ways to solve it

-- Method 1: Subquery with MAX
SELECT MAX(price) AS second_highest
FROM products
WHERE price < (SELECT MAX(price) FROM products);

-- Method 2: LIMIT OFFSET
SELECT DISTINCT price
FROM products
ORDER BY price DESC
LIMIT 1 OFFSET 1;

-- Method 3: DENSE_RANK
SELECT price FROM (
  SELECT price, DENSE_RANK() OVER (ORDER BY price DESC) AS rnk
  FROM products
) t WHERE rnk = 2
LIMIT 1;

-- Using our schema: find customers in the same city
SELECT c1.first_name AS customer1,
       c2.first_name AS customer2,
       c1.city
FROM customers c1
JOIN customers c2
  ON c1.city = c2.city
  AND c1.customer_id < c2.customer_id;

-- WHERE filters rows BEFORE grouping
SELECT category, COUNT(*) AS product_count
FROM products
WHERE price > 1000         -- filters individual products first
GROUP BY category;

-- HAVING filters groups AFTER aggregation
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 3;       -- filters categories with more than 3 products

-- Orders that have no payment
SELECT o.order_id, o.total_amount, o.status
FROM orders o
LEFT JOIN payments p ON o.order_id = p.order_id
WHERE p.payment_id IS NULL;

SELECT order_date,
       total_amount,
       SUM(total_amount) OVER (
         ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM orders
WHERE status = 'completed'
ORDER BY order_date;

-- Show order count per status as columns
SELECT
  customer_id,
  SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
  SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending,
  SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
  SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END) AS returned
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

-- Find gaps in order_id sequence
SELECT o1.order_id + 1 AS gap_start,
       MIN(o2.order_id) - 1 AS gap_end
FROM orders o1
LEFT JOIN orders o2 ON o2.order_id > o1.order_id
GROUP BY o1.order_id
HAVING o1.order_id + 1 < MIN(o2.order_id);

-- Common string operations asked in interviews
SELECT
  first_name,
  UPPER(first_name) AS upper_name,
  LOWER(email) AS lower_email,
  LENGTH(first_name) AS name_length,
  CONCAT(first_name, ' ', last_name) AS full_name,
  SUBSTRING(email, 1, LOCATE('@', email) - 1) AS email_username,
  REVERSE(first_name) AS reversed
FROM customers
LIMIT 5;

-- Common date operations
SELECT
  order_date,
  YEAR(order_date) AS yr,
  MONTH(order_date) AS mo,
  DAY(order_date) AS dy,
  DAYNAME(order_date) AS day_name,
  DATEDIFF(CURDATE(), order_date) AS days_ago,
  DATE_ADD(order_date, INTERVAL 30 DAY) AS plus_30_days,
  DATE_FORMAT(order_date, '%d-%b-%Y') AS formatted
FROM orders
LIMIT 5;

-- Top 2 expensive products per category
SELECT category, product_name, price FROM (
  SELECT category, product_name, price,
    ROW_NUMBER() OVER (
      PARTITION BY category ORDER BY price DESC
    ) AS rn
  FROM products
) ranked
WHERE rn <= 2
ORDER BY category, price DESC;

-- COALESCE returns first non-NULL value
SELECT
  p.product_name,
  COALESCE(AVG(r.rating), 0) AS avg_rating,
  COALESCE(r.review_text, 'No review yet') AS review
FROM products p
LEFT JOIN reviews r ON p.product_id = r.product_id
GROUP BY p.product_name, r.review_text;

-- IN: checks if value is in a list (subquery runs once)
SELECT * FROM customers
WHERE customer_id IN (
  SELECT customer_id FROM orders
);

-- EXISTS: checks if subquery returns any rows (runs per row)
SELECT * FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
);

-- EXISTS is faster for large datasets
-- IN is simpler to read for small lists

-- Find customers who ordered on consecutive days
SELECT DISTINCT c.first_name, c.last_name,
       o1.order_date AS day1,
       o2.order_date AS day2
FROM orders o1
JOIN orders o2
  ON o1.customer_id = o2.customer_id
  AND DATEDIFF(o2.order_date, o1.order_date) = 1
JOIN customers c ON o1.customer_id = c.customer_id;

-- Get the 5th row from products (ordered by price)
SELECT * FROM products
ORDER BY price DESC
LIMIT 1 OFFSET 4;

-- Alternative: using ROW_NUMBER
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY price DESC) AS rn
  FROM products
) t
WHERE rn = 5;