What are window functions in SQL, and how do you use them?

Explanation:

Window functions in SQL are a powerful feature that allows you to perform calculations across a set of table rows that are somehow related to the current row. Unlike aggregate functions, which return a single value for a group of rows, window functions return a value for each row in the query result. This makes them especially useful for tasks like ranking, running totals, moving averages, and more.

Key Talking Points:

• Definition: Window functions perform calculations across a set of table rows related to the current row.
• Non-Collapsing: They do not reduce the number of rows returned by the query.
• Use Cases: Useful for ranking, cumulative sums, moving averages, etc.
• Syntax: Typically includes an OVER() clause that defines the window of rows.

NOTES:

Reference Table:

Pseudocode:

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

This SQL query ranks employees based on their salary and calculates a running total of salaries within each department.

Follow-Up Questions and Answers:

1. Question: Can you explain the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?

   • Answer:
     • ROW_NUMBER() assigns a unique number to each row, starting from 1 for each partition.
     • RANK() assigns a rank to each row within a partition, with gaps in the ranking where there are ties.
     • DENSE_RANK() is similar to RANK() but does not leave gaps between ranks.

2. Question: How do window functions differ from GROUP BY?

   • Answer: GROUP BY aggregates data into a single result row for each group, effectively collapsing the result set. Window functions, however, maintain the original row count by performing calculations over a specified range of rows.

3. Question: Can window functions be used with WHERE clause conditions?

   • Answer: No, window functions cannot be used in the WHERE clause because they are applied after the result set is constructed. You can use them in the SELECT list or in the ORDER BY clause.

These elements provide a comprehensive understanding of window functions in SQL, suitable for an interview setting at a FAANG company.