From the course: Advanced SQL – Window Functions

Unlock the full course today

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

Solution: Offset window functions

Solution: Offset window functions - SQL Tutorial

From the course: Advanced SQL – Window Functions

Start my 1-month free trial Buy for my team

Solution: Offset window functions

“

- The first step is to identify each quarter. And, I really hope you heeded my tip, and used quarter start dates as their identifiers, and were not tempted to extract quarter number and years from the dates. If you did, I bet you quickly realized what a hassle they are when dealing with cross-year boundaries. I chose to use MAKE_DATE with DATE_PART, but any other method is fine, as long as you end up with the date type to work with. All I need are the species and the quarters. At the bottom of the solution file, you will find an alternative query that uses extract. Now, I can figure out quarterly adoptions and then some. The first challenge is to find the right grouping expressions. I need to calculate quarterly adoptions, both for each species, and for all species. One option is to write two separate queries, one that groups by quarter and species, and another that groups only by quarter, and then union them. And, there's…

Contents