Snowflake Analytics – The Moving Difference
I can teach you analytics! Snowflake is built for analytics, and this week, I will show you the amazing moving difference commands. The moving difference is one of the most incredible aspects of analytics, but unless it is explained right it can seem confusing. With power comes incredible responsibility so I won’t let you down.
All of these examples have come from my books and training classes. Please do me a favor and tell your training coordinator that you know the best technical trainer in the World. Ask them to hire me to train at your company, either on-site or with a virtual class. They can see our classes, outlines, and a sample of my teaching at this link on our website. I hope to meet you and say thanks in my next class at your company:)
https://coffingdw.com/education/
Moving differences are commonly used in data analysis to understand the rate of change in a series of values over time, which allows us to look for positive or negative trends. They provide insights into the direction and magnitude of change, which can be useful for decision-making and forecasting. Check out the example below.
Let me caution you so you are not confused. A moving difference only compares one row to another row.
The first thing to remember is that these analytics are often referred to as ORDERED ANALYTICS because we have an ORDER BY statement inside the calculation that sorts the data first. Once the data is ordered correctly, the analytic calculates from the first row sorted to the last. In our example below, we first order from PRODUCT_ID ASC and then SALE_DATE ASC. We don’t need the ASC because ascending mode is the default, but I put it there for clarity.
If I have a moving window of four, as in the example below, then the DAILY_SALES of row one will compare to the DAILY_SALES value of row five (four rows apart) and show a positive or negative difference between those two rows.
Notice that the first four rows have null values in the moving difference. Why? Because each of them has something in common: they don’t have a row four rows ahead of them to compare.
Notice that the fifth row has a value of -16409.90. We are only comparing the DAILY_SALES value in row one with the DAILY_SALES value in row five because we have a moving window of four because of the keywords ROWS BETWEEN 4 PRECEDING AND 4 PRECEDING. I have colored those values in blue. Notice that we compared row two with row six (four rows apart), and we did better by 9,799.78 dollars, colored in red.
In the picture below, notice that we have a PARTITION BY PRODUCT_ID before our ORDER BY statement. The keywords PARTITION BY mean only to perform the calculation within each PRODUCT_ID. Within each PRODUCT_ID, we order the data by SALE_DATE.
Also, notice that we have a moving window of two rows because of the keywords ROWS BETWEEN 2 PRECEDING and 2 PRECEDING.
The moving difference calculation below will compare the DAILY_SALES value of the current row with the DAILY_SALES value two rows before. Remember, only two rows are compared, so we are comparing rows one and three for a moving difference of -12,850.33. In other words, we had a DAILY_SALES value of $48850.40 in row one. When we compared $48,850.40 with row three’s value of $36,000.07, we did worse by 12,850.33. Ouch!
Also, notice that we had null values for the first two rows in each PRODUCT_ID partition because there were not any rows two rows ahead.
The example below is pure genius and is your ticket to superstar status. We are comparing the DAILY_SALES to what we did the previous week. So, we will compare the DAILY_SALES that happened on a Monday with what happened the previous Monday. And compare Tuesday to Tuesday, etc.
I use several tricks to ensure that this is perfect. The first trick is using the TO_CHAR command to format the SALE_DATE to provide the day of the week, which I alias as DayofWeek. I then PARTITION BY PRODUCT_ID and DayofWeek, so the data will be calculated by PRODUCT_ID 1000, and then all of my Mondays, which are now back to back. These are followed by all of my Tuesdays in a row. I then use a moving window of one because of the keywords ROWS BETWEEN 1 PRECEDING and 1 PRECEDING.
The final trick I use is to put an additional ORDER BY statement at the end of the query where the major sort is PRODUCT_ID, and the minor sort is SALE_DATE.
The moving difference calculation ordered the data by PRODUCT_ID, DayOfWeek. The moving difference calculated Monday to the previous Monday, Tuesday to the previous Tuesday, etc. However, with the addition of the ORDER BY PRODUCT_ID SALE_DATE at the end, I resorted to it to make the report look nice. However, if you compare the first Monday, we made 97.00 to the second Monday, where we made 62.00, we did worse on the second Monday by 35.00. But we did better on the second Tuesday than the first Tuesday by 17.00.
Did you know that Coffing Data Warehousing is the first company to create software that joins data across all systems? Download your free Nexus trial at www.CoffingDW.com. Watch how Nexus performs federated queries to join a DB2 table to tables from twenty systems in a single query. Watch the video.
To find out more about Nexus, you can watch our 60-second videos here: