How to fix ER_WINDOW_INVALID_WINDOW_FUNC_USE (3593): Invalid window function usage in MySQL

-- WRONG: Window function in WHERE clause
SELECT employee_id, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) as rank
FROM employees
WHERE ROW_NUMBER() OVER (ORDER BY salary DESC) <= 10;  -- Error!

-- CORRECT: Use derived table
SELECT * FROM (
  SELECT employee_id, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) as rank
  FROM employees
) ranked
WHERE rank <= 10;

-- WRONG: Window function in HAVING
SELECT department_id, AVG(salary) OVER (PARTITION BY department_id) as avg_dept_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) OVER (PARTITION BY department_id) > 50000;  -- Error!

-- CORRECT: Use derived table or CTE
WITH department_stats AS (
  SELECT
    department_id,
    AVG(salary) OVER (PARTITION BY department_id) as avg_dept_salary
  FROM employees
  GROUP BY department_id
)
SELECT * FROM department_stats WHERE avg_dept_salary > 50000;

-- Aggregate window functions (can be used like regular aggregates)
-- SUM, AVG, COUNT, MIN, MAX, STDDEV, VARIANCE
SELECT
  department_id,
  salary,
  AVG(salary) OVER (PARTITION BY department_id) as dept_avg,  -- OK
  SUM(salary) OVER (PARTITION BY department_id) as dept_total  -- OK
FROM employees;

-- Non-aggregate (ranking/value) window functions
-- ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE,
-- FIRST_VALUE, LAST_VALUE, NTH_VALUE, LAG, LEAD
SELECT
  employee_id,
  salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) as salary_rank,  -- OK
  LAG(salary, 1) OVER (ORDER BY employee_id) as prev_salary  -- OK
FROM employees;

-- Common mistake: Using non-aggregate where aggregate is expected
SELECT
  department_id,
  ROW_NUMBER() OVER (PARTITION BY department_id) as row_num,  -- OK in SELECT
  COUNT(*) as emp_count
FROM employees
GROUP BY department_id
-- ERROR if trying to use ROW_NUMBER() in aggregate context

-- Basic syntax: function_name() OVER (window_spec)
-- window_spec: [partition_clause] [order_clause] [frame_clause]

-- CORRECT examples:
SELECT
  ROW_NUMBER() OVER (ORDER BY salary DESC) as rank,
  AVG(salary) OVER (PARTITION BY department_id ORDER BY hire_date) as running_avg,
  SUM(salary) OVER (PARTITION BY department_id) as dept_total
FROM employees;

-- COMMON ERRORS to fix:

-- 1. Missing parentheses
SELECT ROW_NUMBER() OVER ORDER BY salary DESC;  -- WRONG
SELECT ROW_NUMBER() OVER (ORDER BY salary DESC);  -- CORRECT

-- 2. Incorrect frame specification
SELECT SUM(salary) OVER (ORDER BY hire_date RANGE) as running_total;  -- WRONG
SELECT SUM(salary) OVER (ORDER BY hire_date ROWS UNBOUNDED PRECEDING) as running_total;  -- CORRECT

-- 3. Mixing aggregate and non-aggregate incorrectly
SELECT
  department_id,
  COUNT(*) OVER (PARTITION BY department_id) as count,
  ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY COUNT(*) OVER (PARTITION BY department_id)) as rank  -- NESTED! WRONG
FROM employees;

-- 4. Invalid ORDER BY in partition
SELECT
  NTILE(4) OVER (PARTITION BY department_id) as quartile  -- Missing ORDER BY for NTILE
FROM employees;
SELECT
  NTILE(4) OVER (PARTITION BY department_id ORDER BY salary) as quartile  -- CORRECT
FROM employees;

-- LAG/LEAD work with any data type
SELECT
  order_date,
  amount,
  LAG(amount, 1) OVER (ORDER BY order_date) as prev_amount,  -- OK numeric
  LAG(customer_name, 1) OVER (ORDER BY order_date) as prev_customer  -- OK string
FROM orders;

-- Aggregate window functions have type restrictions
SELECT
  text_column,
  SUM(text_column) OVER (ORDER BY id)  -- ERROR if text_column not numeric
FROM my_table;

-- FIRST_VALUE/LAST_VALUE/NTH_VALUE work with any type
SELECT
  product_name,
  FIRST_VALUE(product_name) OVER (PARTITION BY category ORDER BY price) as cheapest_in_category
FROM products;

-- RANK functions require ORDER BY
SELECT
  student_id,
  RANK() OVER (ORDER BY test_score) as rank,  -- OK
  RANK() OVER () as rank_no_order  -- ERROR: RANK requires ORDER BY
FROM test_scores;

-- Fix by ensuring proper data types:
SELECT
  id,
  -- Convert text to numeric if it represents numbers
  SUM(CAST(text_number_column AS DECIMAL(10,2))) OVER (ORDER BY id) as running_total,
  -- Or use appropriate window function for text
  FIRST_VALUE(text_column) OVER (ORDER BY id) as first_text
FROM my_table;

-- Complex query that might trigger ER_WINDOW_INVALID_WINDOW_FUNC_USE
SELECT
  department_id,
  employee_id,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as dept_rank,
  AVG(salary) OVER (PARTITION BY department_id) as dept_avg,
  (salary - AVG(salary) OVER (PARTITION BY department_id)) /
    STDDEV(salary) OVER (PARTITION BY department_id) as z_score
FROM employees
WHERE hire_date > '2020-01-01';

-- Step 1: Test each window function separately
SELECT
  department_id,
  employee_id,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as dept_rank
FROM employees
WHERE hire_date > '2020-01-01';

-- Step 2: Add another window function
SELECT
  department_id,
  employee_id,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as dept_rank,
  AVG(salary) OVER (PARTITION BY department_id) as dept_avg
FROM employees
WHERE hire_date > '2020-01-01';

-- Step 3: Use CTE for complex calculations
WITH dept_stats AS (
  SELECT
    department_id,
    employee_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as dept_rank,
    AVG(salary) OVER (PARTITION BY department_id) as dept_avg,
    STDDEV(salary) OVER (PARTITION BY department_id) as dept_stddev
  FROM employees
  WHERE hire_date > '2020-01-01'
)
SELECT
  department_id,
  employee_id,
  salary,
  dept_rank,
  dept_avg,
  (salary - dept_avg) / NULLIF(dept_stddev, 0) as z_score
FROM dept_stats;

-- Alternative: Use multiple CTEs for clarity
WITH filtered_employees AS (
  SELECT * FROM employees WHERE hire_date > '2020-01-01'
),
ranked_employees AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as dept_rank
  FROM filtered_employees
),
with_avg AS (
  SELECT
    *,
    AVG(salary) OVER (PARTITION BY department_id) as dept_avg
  FROM ranked_employees
)
SELECT * FROM with_avg;