Window Functions vs GROUP BY

-- Requirement: list every order with the customer's total order count

-- WRONG attempt with GROUP BY: loses individual order rows
SELECT user_id, COUNT(*) AS total_orders
FROM orders
GROUP BY user_id;
-- Can't include order id, amount, etc. — they would need to be aggregated too

-- CORRECT: window function preserves every row
SELECT
    o.id           AS order_id,
    o.user_id,
    o.amount,
    o.status,
    o.created_at,
    COUNT(*)   OVER (PARTITION BY o.user_id) AS user_total_orders,
    SUM(amount) OVER (PARTITION BY o.user_id) AS user_total_spent
FROM orders o;

-- Department summary: average salary vs company-wide average
-- GROUP BY first collapses employees to department level,
-- then window function compares each department avg to company avg.
SELECT
    d.name                      AS department,
    COUNT(e.id)                 AS headcount,
    ROUND(AVG(e.salary), 2)     AS dept_avg_salary,
    ROUND(
        AVG(AVG(e.salary)) OVER (),   -- window over the grouped rows
        2
    )                           AS company_avg_salary,
    ROUND(
        100.0 * AVG(e.salary)
              / AVG(AVG(e.salary)) OVER () - 100,
        1
    )                           AS pct_vs_company_avg
FROM employees e
JOIN departments d ON d.id = e.department_id
GROUP BY d.id, d.name
ORDER BY dept_avg_salary DESC;

-- Show employees who earn more than the company average (window can't go in WHERE)
-- WRONG:
SELECT name, salary,
       AVG(salary) OVER () AS avg_salary
FROM employees
WHERE salary > AVG(salary) OVER ();  -- ERROR

-- CORRECT via CTE:
WITH emp_with_avg AS (
    SELECT
        name,
        department_id,
        salary,
        AVG(salary) OVER ()                              AS company_avg,
        AVG(salary) OVER (PARTITION BY department_id)   AS dept_avg
    FROM employees
)
SELECT name, department_id, salary, company_avg, dept_avg
FROM emp_with_avg
WHERE salary > company_avg;