Have you written window functions and CTEs?

Yes, window functions and CTEs (Common Table Expressions) are actively used when working with databases, especially in complex queries for analytics and aggregations.

Window functions allow performing calculations across a set of table rows that are related to the current row, without grouping the data, for example, to calculate moving sums, rankings, or cumulative values.

CTEs simplify the reading and maintenance of complex queries by allowing the declaration of temporary named data sets that can be used within the main query.

Example of a window function in SQL:

SELECT employee_id, department_id, salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as salary_rank
FROM employees;

Example of a CTE:

WITH DepartmentSalaries AS (
    SELECT department_id, AVG(salary) as avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.employee_id, e.salary, d.avg_salary
FROM employees e
JOIN DepartmentSalaries d ON e.department_id = d.department_id;