SQL queries using group by and windows function.

Question

Accepted Answer

Group by and window functions are used in SQL queries to aggregate data and perform calculations on subsets of data.

Group by is used to group data based on one or more columns and perform aggregate functions on each group.
Window functions are used to perform calculations on a subset of data within a group, without affecting the grouping of the data.
Examples of aggregate functions include SUM, AVG, COUNT, MAX, and MIN.
Examples of window functions include ROW_NUMBER, RANK, DENSE_RANK, and NTILE.

Accepted Answer

Window functions vs GROUP BY

Window functions and GROUP BY are both used in SQL to perform calculations and aggregations on data. However, they have some differences in terms of syntax and functionality.

Window Functions

✅ Window functions are used to perform calculations across a set of rows that are related to the current row.

✅They can be used to calculate rankings, running totals, moving averages, and more

✅Window functions are typically used with the OVER clause, which defines the window or group of rows to perform the calculation on.

✅Window functions do not affect the grouping of the result set.

**GROUP BY**

✅GROUP BY is used to group rows based on one or more columns.

✅It is used with aggregate functions like SUM, COUNT, AVG, etc., to calculate summary statistics for each group.

✅GROUP BY affects the grouping of the result set, and the output is divided into groups based on the specified columns.

⏩When comparing window functions and GROUP BY, it’s essential to remember that GROUP BY collapses the individual records into groups; after using GROUP BY, you cannot refer to any individual field because it is collapsed.

⏩Window functions are a powerful feature of SQL. They allow us to apply functions like AVG, COUNT, MAX, and MIN on a group of records while still leaving the individual records accessible. Since the individual records are not collapsed, we can create queries showing data from the individual record together with the result of the window function. This is what makes window functions so powerful.

⏩In general, window functions are more flexible and powerful than GROUP BY, as they allow for more complex calculations and do not require explicit grouping. However, GROUP BY is still useful for simple aggregations and when explicit grouping is required.

Example: We have a table called employee with a total of five employees and three departments:

| Employee_Name | Department | Salary |
|-----------------|-------------|--------|
| John Roberts | Finance | 2300 |
| Peter Hudson | Marketing | 1800 |
| Sue Gibson | Finance | 2000 |
| Melinda Bishop | Marketing | 1500 |
| Nancy Hudson | IT | 1950 |

Suppose we want to obtain the average salary per department and the top salary for every department. We should use the following query:

SELECT Department,
avg(salary) as average,
max(salary) as top_salary
FROM employee
GROUP BY department

| Department | Average | Top Salary |
|-------------------|--------------|-----------------|
| Marketing | 1650 | 1800 |
| Finance | 2150 | 2300 |
| IT | 1950 | 1950 |

https://www.linkedin.com/in/pintukushwaha/

SQL queries using group by and windows function.

Popular interview questions of Data Analyst

Top HR questions asked in Stanza Living Data Analyst