I can teach you Snowflake analytics! Today we are going to learn about Nth_Value.
The Nth_Value of Snowflake returns the nth value (up to 1000) within an ordered group of values. In the picture below, we have Nth_Value(Daily_Sales, 1). Once the data sort by Sale_Date via the ORDER BY clause, this query returns the first row’s value of Daily_Sales.
The next example is slightly different because we are adding and additional column with an alias of Diff. The Diff takes the Daily_Sales of the current row and then substracts the first value Daily_Sales to show the difference between the current row and the first value.
The next example uses a QUALIFY statement. The example below is the same as the example below except for the QUALIFY statement. The QUALIFY is a filter against results after all the calculations run. The answer set from the example above, and the answer set from the example below are the same at first, but the QUALIFY filters further to only bring back rows that have a DIFF greater than 0. We can now see the rows that have a higher Daily_Sales than the column with the alias First_Val.
The next example is great for multiple reasons. We have a Nth_Value(Grade_Pt, 2), which returns the second-highest Grade_Pt of all students. Even though the answer set not presented with sort order, the 3.95 Grade_Pt is still the second-highest Grade_Pt, which displays in the column with the alias Sec_Highest.
Also, notice the IGNORE NULLS keywords. If those words were absent, the second-highest Grade_Pt is 4.0. Why? Nulls sort first in DESC mode so Stanley Johnson would be the highest from the sort, and the 4.0 would have been second. The IGNORE NULLS does not keep the null values from showing up on the report, but it does exclude them in the calculation.
Next, we are going to take the next example even farther by adding a PARTITION BY Clause. The example below will find the second highest Grade_Pt within each Class_Code. The CASE statement at the end is not needed. The calculation would have come out the same, even if the final report orders randomly. The CASE statement is a clever way to sort the final answer set to make it look logical and pretty.
The next example below is the exact example as above, but we have added a “FROM LAST” clause. The final ORDER BY statement is not necessary. I did it for two reasons.
- It shows my point earlier that when ordering in DESC mode, a null value comes first.
- For explanation purposes, I want it to be clear that 1.90 is the second to the last value.
If we did not use the ORDER BY clause, in the end, the second to last value would still be 1.90 no matter how the rows displayed on the report.
In the next example below, we are using a PARTITION BY Class_Code to find the second highest Grade_Pt within each Class_Code. PARTITION BY resets to calculate within each partition. We have also added a CASE Statement for display purposes. It does not change the values in the column we alias Sec_Highest, but it shows the rows in an order that makes it easy to understand. Plus, I bet you didn’t know you could use a CASE statement in an ORDER BY statement.
Also, notice Stanley Johnson. He is the only person with a Class_Code of Null, so it is impossible to get the 2nd value. When this happens, the Nth_Value returns a null.
Have you ever had a wish to join an Excel spreadsheet to a table? How would you like to use a tool that does this and automatically writes the SQL for you? Download a free trial of Nexus at www.CoffingDW.com.
The Nexus Super Join Builder allows a user to see their tables visually. All the user has to do is place a checkmark on the columns they want on the report, and Nexus creates the SQL.
In the example below, we are joining the Order_Table from SQL Server to an Excel spreadsheet that holds customer information. Notice that SQL is T-SQL from SQL Server. If the table were from Oracle, Nexus would change the SQL automatically depending on the system.
I hope you enjoyed today’s Snowflake analytic lesson. See you next week.
CEO, Coffing Data Warehousing
Direct: 513 300-0341
YouTube channel: CoffingDW
Tom Coffing, better known as Tera-Tom, is the founder of Coffing Data Warehousing where he has been CEO for the past 25 years. Tom has written over 75 books on all aspects of Teradata, Netezza, Yellowbrick, Snowflake, Redshift, Aurora, Vertica, SQL Server, and Greenplum. Tom has taught over 1,000 classes worldwide, and he is the designer of the Nexus Product Line.