SQL Practice Problems — 25 Real Interview Questions with Solutions

customers: customer_id, name, email, city
orders: order_id, customer_id, amount, order_date

SELECT
    c.customer_id,
    c.name,
    SUM(o.amount) AS total_spend
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
ORDER BY total_spend DESC
LIMIT 5;

SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount) AS monthly_revenue,
    COUNT(*) AS order_count
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2025
GROUP BY month
ORDER BY month;

SELECT c.customer_id, c.name, c.email
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

-- Alternative using NOT EXISTS
SELECT customer_id, name, email
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id
);

SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders
ORDER BY order_date;

SELECT
    category,
    product_name,
    SUM(sales) AS total_sales,
    RANK() OVER (PARTITION BY category ORDER BY SUM(sales) DESC) AS rank_in_category
FROM products
GROUP BY category, product_name
ORDER BY category, rank_in_category;

-- Using window function
SELECT DISTINCT salary
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
    FROM employees
) ranked
WHERE rank = 2;

-- Using subquery
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

SELECT
    order_date,
    daily_sales,
    AVG(daily_sales) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7day
FROM (
    SELECT order_date, SUM(amount) AS daily_sales
    FROM orders
    GROUP BY order_date
) daily_totals
ORDER BY order_date;

WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY month
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
    ((revenue - LAG(revenue) OVER (ORDER BY month)) /
     LAG(revenue) OVER (ORDER BY month) * 100) AS growth_pct
FROM monthly_revenue
ORDER BY month;

WITH RECURSIVE all_ids AS (
    SELECT MIN(order_id) AS id FROM orders
    UNION ALL
    SELECT id + 1 FROM all_ids
    WHERE id < (SELECT MAX(order_id) FROM orders)
)
SELECT a.id AS missing_order_id
FROM all_ids a
LEFT JOIN orders o ON a.id = o.order_id
WHERE o.order_id IS NULL
ORDER BY a.id;

WITH first_purchase AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', MIN(order_date)) AS cohort_month
    FROM orders
    GROUP BY customer_id
),
purchase_months AS (
    SELECT DISTINCT
        customer_id,
        DATE_TRUNC('month', order_date) AS purchase_month
    FROM orders
)
SELECT
    fp.cohort_month,
    pm.purchase_month,
    COUNT(DISTINCT pm.customer_id) AS active_customers,
    ROUND(
        COUNT(DISTINCT pm.customer_id) * 100.0 /
        (SELECT COUNT(*) FROM first_purchase WHERE cohort_month = fp.cohort_month),
        1
    ) AS retention_pct
FROM first_purchase fp
JOIN purchase_months pm ON fp.customer_id = pm.customer_id
GROUP BY fp.cohort_month, pm.purchase_month
ORDER BY fp.cohort_month, pm.purchase_month;

SELECT
    customer_id,
    product_id,
    order_date,
    COUNT(*) AS duplicate_count
FROM orders
GROUP BY customer_id, product_id, order_date
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC;