Modules

It's hard to explain a window function with words. And you know me, I like giving examples, they always make more sense.

Let's say we have this table called employee_salary that have all these values:

And I ask you to get me the total salary for each job title. You will use a group by and I know how good you are now at doing this:

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

And this is the result you will get:

But what if I want for each row of that employee_salary the total salary across each job title. Basically, something like that:

See, for the first row, Alice salary is $70k and the total salary for her job title is$217k. How the heck am I able to do that for each single row of that table?

Well, you can do that using Window function and get some cool metrics, like what % Alice's salary represents compared to all employees in the same role.

Well, alright! I'll show you how it will look in SQL:

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

I didn't want you to freak out! But a window function can go across the whole table and sum the whole thing. You just need to specify it! Here we are go over the whole table and SUMMING everything but only for each job title that row belongs to.

That's why I am using PARTITION BY. If you want to sum the whole salary across the whole table, you can just remove that.

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

I know it might not make much sense now! But don't worry, we will as always go over more examples so that it makes sense.

See you in the next lesson!