Junior — Middle

Have you had experience using window functions when working with SQL queries?

sobes.tech AI

Answer from AI

Yes, I have used window functions in SQL queries. They allow performing calculations across sets of table rows related to the current row without collapsing the result set, which is useful for analytics and complex queries.

Main window functions include:

ROW_NUMBER() — assigns a unique sequential number to rows within a partition.
RANK(), DENSE_RANK() — assign rank to rows within a partition, considering ties.
SUM(), AVG(), COUNT() — aggregate functions over a window.

Example: list employees with their salaries and rank within their department based on salary in descending order:

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

Here, PARTITION BY divides data by department, and ORDER BY sorts within each department. Window functions enable complex analytical queries without losing detail.

Previous Next