From the course: Intermediate SQL for Data Scientists (2021)

Unlock the full course today

Join today to access over 24,700 courses taught by industry experts.

NTH_VALUE and NTILE

NTH_VALUE and NTILE

- [Instructor] We can use typical aggregate functions like sum or average with windowing, but windowing also has some specialized functions that can come in handy and we'll look at two. We're going to look at NTILE and NTH_VALUE. But first, let's see how we can use average along with windowing. So let's say I want to select the salary of employees and group it by department and look at the average salary within a department and compare that to individual salaries. So the first thing I'm going to do is select the department_id and I want the salary, and then I'll take the average salary. Now here, I'm going to work with the window function and I want to say I want the average salary for department. And to do that, I just specify over and then I describe how I want to group the data and I want to group by partitioning by the department_id and I'm going to select this from the data_sci schema in the employees table. So I'm…

Contents