COUNT(DISTINCT) OVER (Window)
COUNT(DISTINCT) OVER (Window) is a SQL function in the Window Functions category. Windowed distinct count — counts unique values per partition without collapsing rows. The syntax is COUNT(DISTINCT column) OVER (PARTITION BY grp). It accepts DISTINCT column. It returns distinct count window. A typical example: SELECT department, name, salary, COUNT(DISTINCT salary) OVER (PARTITION BY department) AS unique_salary_levels, COUNT(*) OVER (PARTITION BY department) AS dept_size FROM employees; -- Each row shows: total employees in dept, unique salary levels -- Not supported in all databases: -- PostgreSQL: supported -- SQL Server: supported (2016+) -- MySQL: NOT supported (use subquery workaround) -- Oracle: NOT supported (use subquery instead) -- MySQL workaround: SELECT e.*, d.unique_salaries FROM employees e JOIN… A close relative is ROW_NUMBER(), which assigns a unique sequential integer to each row within its partition (1, 2, 3...). A close relative is RANK(), which similar to ROW_NUMBER but gives the same rank to equal values. Leaves gaps in ranking.