-- 1. Most expensive products in their category
SELECT p1.*
FROM products p1
WHERE p1.price = (
    SELECT MAX(p2.price)
    FROM products p2
    WHERE p2.category = p1.category
);

-- Alternative with row subquery
SELECT *
FROM products
WHERE (category, price) IN (
    SELECT category, MAX(price)
    FROM products
    GROUP BY category
);

-- 2. Customers with 50% above average orders
SELECT 
    c.*,
    (
        SELECT AVG(amount)
        FROM orders
        WHERE customer_id = c.customer_id
    ) AS customer_avg
FROM customers c
WHERE (
    SELECT AVG(amount)
    FROM orders
    WHERE customer_id = c.customer_id
) > (
    SELECT AVG(amount) * 1.5
    FROM orders
);

-- 3. Manager-subordinate relationships (recursive)
WITH RECURSIVE org_chart AS (
    SELECT 
        employee_id,
        employee_name,
        manager_id,
        0 AS level,
        employee_name AS reporting_chain
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT 
        e.employee_id,
        e.employee_name,
        e.manager_id,
        oc.level + 1,
        CONCAT(oc.reporting_chain, ' > ', e.employee_name)
    FROM employees e
    INNER JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart;

-- 4. Customers who ordered every product in a category
SELECT c.*
FROM customers c
WHERE NOT EXISTS (
    SELECT p.product_id
    FROM products p
    WHERE p.category = 'Electronics'
    AND NOT EXISTS (
        SELECT 1
        FROM orders o
        JOIN order_items oi ON o.order_id = oi.order_id
        WHERE o.customer_id = c.customer_id
        AND oi.product_id = p.product_id
    )
);

-- 5. Running totals using subqueries
SELECT 
    order_date,
    amount,
    (
        SELECT SUM(amount)
        FROM orders o2
        WHERE o2.order_date <= o1.order_date
            AND o2.customer_id = o1.customer_id
    ) AS running_total
FROM orders o1
WHERE customer_id = 123
ORDER BY order_date;

-- 6. Second highest salary in each department
SELECT 
    department,
    (
        SELECT DISTINCT salary
        FROM employees e2
        WHERE e2.department = e1.department
        ORDER BY salary DESC
        LIMIT 1 OFFSET 1
    ) AS second_highest_salary
FROM employees e1
GROUP BY department;

-- Alternative approach
SELECT department, MAX(salary) AS second_highest
FROM employees e1
WHERE salary < (
    SELECT MAX(salary)
    FROM employees e2
    WHERE e2.department = e1.department
)
GROUP BY department;

-- 7. Detect duplicate orders
SELECT o1.*
FROM orders o1
WHERE EXISTS (
    SELECT 1
    FROM orders o2
    WHERE o2.customer_id = o1.customer_id
        AND DATE(o2.order_date) = DATE(o1.order_date)
        AND o2.amount = o1.amount
        AND o2.order_id != o1.order_id
);

-- 8. Percentile ranking using subqueries
SELECT 
    product_id,
    product_name,
    price,
    (
        SELECT COUNT(*)
        FROM products p2
        WHERE p2.price <= p1.price
    ) * 100.0 / (SELECT COUNT(*) FROM products) AS percentile_rank
FROM products p1
ORDER BY price;

-- More precise percentile calculation
SELECT 
    product_name,
    price,
    ROUND(
        (
            SELECT COUNT(DISTINCT price)
            FROM products p2
            WHERE p2.price < p1.price
        ) * 100.0 / (
            SELECT COUNT(DISTINCT price)
            FROM products
        ),
        2
    ) AS percentile
FROM products p1
ORDER BY price;