From the course: Advanced SQL – Window Functions
Join today to access over 22,600 courses taught by industry experts or purchase this course individually.
Solution: Rank window functions - SQL Tutorial
From the course: Advanced SQL – Window Functions
Solution: Rank window functions
- [Instructor] First, let's figure out the species average temperatures and every measurement's difference from it. I chose to use an aggregate window function partitioned by species and subtracted from the current temperature. Well, I could've separated this into two CTEs to avoid repeating the function. I believe that by now you should be comfortable enough with a bit more logic in each CTE. A reasonable alternative is to first calculate all the averages per species with a grouped query, and join it to routine checkups. It makes calculating the difference a bit more direct. At the bottom of the solution code file you will find an alternative query that uses the same steps as I'm about to show you for this one using slightly different syntax and features. The next step is calculating whether a measurement's difference from the average constitutes an exception or not. I chose to use the integers one and zero to…
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.
Download courses and learn on the go
Watch courses on your mobile device without an internet connection. Download courses using your iOS or Android LinkedIn Learning app.