-- employees who earn more than the average of THEIR OWN department
SELECT e.name, e.dept_id, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(i.salary)
    FROM employees i
    WHERE i.dept_id = e.dept_id          -- ← correlation: uses the outer row
)
ORDER BY e.emp_id;

name  | dept_id | salary
------+---------+-------
Asha  | 1       | 150000
Ravi  | 1       | 120000
Priya | 2       | 110000
Sara  | 3       | 85000

SELECT c.name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS orders,
       (SELECT MAX(order_date) FROM orders o WHERE o.customer_id = c.customer_id) AS last_order
FROM customers c
ORDER BY c.customer_id;

name   | orders | last_order
-------+--------+-----------
Ananya | 3      | 2024-03-15
Rahul  | 2      | 2024-03-01
Sneha  | 2      | 2024-03-08
Imran  | 1      | 2024-02-20
Kavya  | 0      | NULL

-- customers who bought something from the Furniture category
SELECT c.name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    JOIN products p     ON p.product_id = oi.product_id
    WHERE o.customer_id = c.customer_id AND p.category = 'Furniture'
)
ORDER BY c.name;

name
------
Ananya
Rahul
Sneha

ALTER TABLE customers ADD COLUMN lifetime_value INTEGER DEFAULT 0;

UPDATE customers
SET lifetime_value = COALESCE((
    SELECT SUM(oi.quantity * oi.unit_price)
    FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.customer_id = customers.customer_id AND o.status <> 'cancelled'
), 0);

SELECT name, lifetime_value FROM customers ORDER BY lifetime_value DESC, customer_id;

name   | lifetime_value
-------+---------------
Ananya | 68400
Rahul  | 61200
Sneha  | 13700
Imran  | 0
Kavya  | 0