Practice Questions — SQL Interview Masterclass - Top 30 Questions Solved

SELECT DISTINCT salary FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) t WHERE rnk = 4;

SELECT id, name, price FROM products
WHERE price > (SELECT AVG(price) FROM products);

SELECT id, name, hire_date FROM employees
WHERE hire_date BETWEEN '2025-03-01' AND '2025-03-31';

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

SELECT dept, COUNT(*) AS n
FROM employees
GROUP BY dept
ORDER BY n DESC;

SELECT dept, name, salary FROM (
    SELECT dept, name, salary,
           ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
    FROM employees
    WHERE name LIKE 'A%' OR name LIKE 'P%'
) t
WHERE rn <= 3;

WITH avgs AS (
    SELECT dept, AVG(salary) AS avg_sal FROM employees GROUP BY dept
)
SELECT e.dept, a.avg_sal, COUNT(*) AS above_avg_count
FROM employees e
JOIN avgs a ON a.dept = e.dept
WHERE e.salary > a.avg_sal
GROUP BY e.dept, a.avg_sal;

SELECT id, name, hire_date
FROM employees
WHERE hire_date = (SELECT MIN(hire_date) FROM employees);

SELECT dept, name, salary FROM (
    SELECT dept, name, salary,
           ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
    FROM employees
) t
WHERE rn <= 2
ORDER BY dept, rn;

SELECT id, total,
       ROUND(100.0 * total / SUM(total) OVER (), 2) AS pct
FROM orders;

SELECT o.customer_id
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
GROUP BY o.customer_id
HAVING COUNT(DISTINCT p.category_id) > 3;

WITH gaps AS (
    SELECT user_id, login_date,
           DATEDIFF(LEAD(login_date) OVER (PARTITION BY user_id ORDER BY login_date),
                    login_date) AS gap_days
    FROM logins
)
SELECT user_id, MAX(gap_days) AS longest_gap
FROM gaps
WHERE gap_days IS NOT NULL
GROUP BY user_id;

SELECT sale_date, daily_total,
       SUM(daily_total) OVER (
           ORDER BY sale_date
           ROWS BETWEEN 13 PRECEDING AND CURRENT ROW
       ) AS rolling_14d
FROM daily_sales
ORDER BY sale_date;

WITH last3 AS (
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 0 MONTH), '%Y-%m') AS m UNION ALL
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m') UNION ALL
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 2 MONTH), '%Y-%m')
)
SELECT customer_id
FROM orders
WHERE DATE_FORMAT(order_date, '%Y-%m') IN (SELECT m FROM last3)
GROUP BY customer_id
HAVING COUNT(DISTINCT DATE_FORMAT(order_date, '%Y-%m')) = 3;

SELECT product_id
FROM order_items oi JOIN orders o ON o.id = oi.order_id
GROUP BY product_id
HAVING SUM(CASE WHEN YEAR(o.order_date) = 2024 THEN 1 ELSE 0 END) > 0
   AND SUM(CASE WHEN YEAR(o.order_date) = 2025 THEN 1 ELSE 0 END) > 0
   AND SUM(CASE WHEN YEAR(o.order_date) = 2026 THEN 1 ELSE 0 END) = 0;

WITH ranked AS (
    SELECT category_id, name, price,
           DENSE_RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS rnk
    FROM products
)
SELECT c.id AS category_id, r.name AS second_most_expensive
FROM (SELECT DISTINCT category_id AS id FROM products) c
LEFT JOIN ranked r ON r.category_id = c.id AND r.rnk = 2;

SELECT customer_id, MAX(order_date) AS last_order_date
FROM orders
GROUP BY customer_id
HAVING MAX(order_date) < DATE_SUB(CURDATE(), INTERVAL 180 DAY);

SELECT dept, COUNT(*) AS n FROM employees GROUP BY dept;

SELECT * FROM (
    SELECT e.*, AVG(salary) OVER (PARTITION BY dept) AS dept_avg
    FROM employees e
) t
WHERE salary BETWEEN dept_avg * 0.9 AND dept_avg * 1.1;

SELECT user_id, MIN(login_date) AS first_login
FROM logins GROUP BY user_id;

SELECT p.id,
       COUNT(DISTINCT c.id) AS comment_count,
       COUNT(DISTINCT l.user_id) AS like_count
FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
LEFT JOIN likes    l ON l.post_id = p.id
GROUP BY p.id;

SELECT category_id, name, price FROM (
    SELECT category_id, name, price,
           NTILE(5) OVER (PARTITION BY category_id ORDER BY price DESC) AS bucket
    FROM products
) t
WHERE bucket = 1;

SELECT id, order_date, total,
       AVG(total) OVER (
           ORDER BY order_date
           ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING
       ) AS avg_prev_5,
       total - AVG(total) OVER (
           ORDER BY order_date
           ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING
       ) AS diff
FROM orders;

SELECT e.name AS emp, e.dept AS emp_dept,
       m.name AS manager, m.dept AS mgr_dept
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.dept <> m.dept;

SELECT AVG(total) AS median
FROM (
    SELECT total,
           ROW_NUMBER() OVER (ORDER BY total) AS rn,
           COUNT(*)     OVER ()                AS n
    FROM orders
) t
WHERE rn IN (FLOOR((n + 1) / 2), CEIL((n + 1) / 2));

SELECT post_id
FROM likes
WHERE liked_at >= NOW() - INTERVAL 24 HOUR
GROUP BY post_id
HAVING COUNT(*) > 100;

SELECT customer_id,
       SUM(CASE WHEN DAYOFWEEK(order_date) IN (1, 7) THEN 1 ELSE 0 END) AS weekend_orders
FROM orders
GROUP BY customer_id;

WITH ranked AS (
    SELECT user_id, login_ts,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_ts) AS rn
    FROM logins
)
SELECT r1.user_id,
       TIMESTAMPDIFF(MINUTE, r1.login_ts, r2.login_ts) AS minutes_between
FROM ranked r1
JOIN ranked r2 ON r1.user_id = r2.user_id AND r1.rn = 1 AND r2.rn = 2;

SELECT m.name AS manager,
       GROUP_CONCAT(e.name ORDER BY e.name SEPARATOR ', ') AS reports
FROM employees e
JOIN employees m ON m.id = e.manager_id
GROUP BY m.id, m.name;

SELECT customer_id,
       SUM(CASE WHEN DATE_FORMAT(order_date,'%Y-%m') = DATE_FORMAT(CURDATE(),'%Y-%m')                           THEN total ELSE 0 END) AS this_month,
       SUM(CASE WHEN DATE_FORMAT(order_date,'%Y-%m') = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH),'%Y-%m') THEN total ELSE 0 END) AS last_month,
       SUM(CASE WHEN DATE_FORMAT(order_date,'%Y-%m') = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 2 MONTH),'%Y-%m') THEN total ELSE 0 END) AS two_months_ago
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
GROUP BY customer_id;

SELECT 100.0 * SUM(
    CASE WHEN EXISTS (
        SELECT 1 FROM order_items oi
        WHERE oi.order_id = o.id AND oi.unit_price > 1000
    ) THEN 1 ELSE 0 END
) / COUNT(*) AS pct
FROM orders o;

SELECT name, salary,
       AVG(salary) OVER () AS company_avg,
       salary - AVG(salary) OVER () AS diff
FROM employees;

SELECT name, dept, salary FROM employees e
WHERE salary > ALL (
    SELECT AVG(salary) FROM employees e2
    WHERE e2.dept <> e.dept
    GROUP BY e2.dept
);