Modules

What a long title!

A picture (especially when it comes to explain window function) is worth a thousand words, even more...

<img src="https://storage.googleapis.com/dataford-assets/learning-path/window_function_explained.webp" width="700px" height="205px" />

• See on the left side, when you have a table, aggregation will group the 3 rows into 1 single row.
• On the right side, window functions are able to output each row with an aggregate value. It's basically summing a sum and having that in each row. Isn't that cool?

You remember the example we discussed in the last lesson:

You have 3 rows here, and with a window function, for each single row, you can output the total of the whole salary column : 217k + 185k + 165k for each row!

So you could for example determine what % Data Analyst job represent over all the 3 jobs (i.e 217k divided by the total of all the columns) and that with a single query.

If you didn't have this powerful weapon, you will have probably have to do some weird joins to make that happen! And in SQL, it's pretty easy to do:

SELECT
    job_title,
    SUM(salary) AS total_salary,
    SUM(SUM(salary)) OVER () AS overall_salary
FROM employee_salary
GROUP BY 1;

And this logic applies to all aggregate functions (SUM, AVG, MIN).

And when you want to tell the window function do the sum only for a specific column type you will then use PARTITION BY.

Here it's only summing the total salary from the same job title. For example, Grace on the last row, she is a Data Analyst and make $72k. The$217k represent the sum of all the salary of Data Analysts.

And the query just needs a small tweak:

SELECT
    job_title,
    SUM(salary) AS total_salary,
    SUM(SUM(salary)) OVER (PARTITION BY job_title) AS total_salary_per_title
FROM employee_salary
GROUP BY 1;

We just added a PARTITION BY to tell the query, just sum everything but only what has the same job_title as the row I am at.

Ok, let's practice now!