Advanced Queries

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

SELECT
    name,
    salary,
    (
        SELECT AVG(salary)
        FROM employees e2
        WHERE e2.dept_id = e1.dept_id
    ) AS dept_avg
FROM employees e1;

SELECT dept_name, avg_salary
FROM (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
) AS dept_averages
JOIN departments ON departments.id = dept_averages.dept_id
WHERE avg_salary > 75000;

SELECT name, email
FROM customers
WHERE customer_id IN (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
);

SELECT product_name, price
FROM products p
WHERE NOT EXISTS (
    SELECT 1
    FROM order_items oi
    WHERE oi.product_id = p.product_id
);

WITH customer_totals AS (
    SELECT
        customer_id,
        SUM(total) AS total_spent
    FROM orders
    GROUP BY customer_id
)
SELECT c.name, ct.total_spent
FROM customer_totals ct
JOIN customers c ON c.customer_id = ct.customer_id
WHERE ct.total_spent > 1000
ORDER BY ct.total_spent DESC;

WITH dept_averages AS (
    SELECT
        dept_id,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
),
company_average AS (
    SELECT AVG(salary) AS company_avg
    FROM employees
)
SELECT
    d.dept_name,
    da.avg_salary,
    ca.company_avg,
    da.avg_salary - ca.company_avg AS difference
FROM dept_averages da
JOIN departments d ON d.dept_id = da.dept_id
CROSS JOIN company_average ca
ORDER BY difference DESC;

WITH RECURSIVE management_chain AS (
    -- Base case: start with the employee
    SELECT
        employee_id,
        name,
        manager_id,
        0 AS level
    FROM employees
    WHERE employee_id = 42

    UNION ALL

    -- Recursive case: find their manager
    SELECT
        e.employee_id,
        e.name,
        e.manager_id,
        mc.level + 1
    FROM employees e
    JOIN management_chain mc ON e.employee_id = mc.manager_id
)
SELECT level, name
FROM management_chain
ORDER BY level;

SELECT
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;

SELECT
    name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

SELECT
    dept_name,
    name,
    salary,
    RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dept_rank
FROM employees
JOIN departments USING (dept_id);

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

SELECT
    order_date,
    total,
    AVG(total) OVER (
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3day
FROM orders
ORDER BY order_date;

SELECT
    order_date,
    total,
    LAG(total) OVER (ORDER BY order_date) AS prev_day,
    total - LAG(total) OVER (ORDER BY order_date) AS change
FROM orders
ORDER BY order_date;

SELECT
    name,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

-- All rows from start to current
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

-- Current and next row
ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING

-- Previous 3, current, and next 3 rows
ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING

-- All rows in partition
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

WITH ranked_products AS (
    SELECT
        category_name,
        product_name,
        total_sales,
        ROW_NUMBER() OVER (
            PARTITION BY category_id 
            ORDER BY total_sales DESC
        ) AS rank
    FROM products
    JOIN categories USING (category_id)
)
SELECT category_name, product_name, total_sales
FROM ranked_products
WHERE rank <= 3
ORDER BY category_name, rank;

SELECT
    month,
    year,
    monthly_sales,
    LAG(monthly_sales, 12) OVER (ORDER BY year, month) AS prev_year_sales,
    ROUND(
      (monthly_sales - LAG(monthly_sales, 12) OVER (ORDER BY year, month))
      / LAG(monthly_sales, 12) OVER (ORDER BY year, month) * 100, 1
    ) AS yoy_growth_pct
FROM monthly_sales_summary
ORDER BY year, month;

WITH customer_spending AS (
    SELECT
        customer_id,
        SUM(total) AS lifetime_value,
        NTILE(3) OVER (ORDER BY SUM(total) DESC) AS value_segment
    FROM orders
    GROUP BY customer_id
)
SELECT CASE
    value_segment
        WHEN 1 THEN 'High Value'
        WHEN 2 THEN 'Medium Value'
        WHEN 3 THEN 'Low Value'
    END AS segment,
    COUNT(*) AS customer_count,
    ROUND(AVG(lifetime_value), 2) AS avg_ltv,
    ROUND(MIN(lifetime_value), 2) AS min_ltv,
    ROUND(MAX(lifetime_value), 2) AS max_ltv
FROM customer_spending
GROUP BY value_segment
ORDER BY value_segment;

WITH first_purchase AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', MIN(order_date)) AS cohort_month
    FROM orders
    GROUP BY customer_id
),
cohort_activity AS (
    SELECT
        fp.cohort_month,
        DATE_TRUNC('month', o.order_date) AS activity_month,
        COUNT(DISTINCT o.customer_id)     AS active_customers
    FROM first_purchase fp
    JOIN orders o ON o.customer_id = fp.customer_id
    GROUP BY fp.cohort_month, DATE_TRUNC('month', o.order_date)
)
SELECT
    cohort_month,
    activity_month,
    active_customers,
    FIRST_VALUE(active_customers) OVER (
        PARTITION BY cohort_month 
        ORDER BY activity_month
    ) AS cohort_size,
    ROUND(
        active_customers::NUMERIC / 
        FIRST_VALUE(active_customers) OVER (
            PARTITION BY cohort_month 
            ORDER BY activity_month
        ) * 100,
      1
    ) AS retention_pct
FROM cohort_activity
ORDER BY cohort_month, activity_month;

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

SELECT
    date,
    inventory_count,
    inventory_count - LAG(inventory_count) OVER (ORDER BY date) AS daily_change
FROM inventory_snapshots
ORDER BY date;

SELECT
    product_name,
    sales,
    ROUND(
            sales * 100.0 / SUM(sales) OVER (),
            2
    ) AS pct_of_total
FROM product_sales
ORDER BY sales DESC;

WITH ranked_orders AS (
    SELECT
        customer_id,
        order_date,
        total,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id 
            ORDER BY order_date DESC
        ) AS recency_rank
    FROM orders
)
SELECT customer_id, order_date, total
FROM ranked_orders
WHERE recency_rank = 1;