I can teach you Snowflake analytics! I will bet you have never seen a listagg analytic!
Some major companies use the listagg analytic for their email marketing campaigns. They cleverly find out the last three web pages the customer went to on their website, and then they send them a customized email offering sales on the products they were viewing last. Let me show you exactly how it works.
The picture below is not a listagg analytic. The example below is that of a normal query where the data displays in vertical rows. I want you to see this so I can then show you how a Listagg displays the data.
The next example below shows each Product_ID and then does a listagg on the Daily_Sales for each Product_ID. We are telling Snowflake that we want each Daily_Sales to be listed on the same line per customer and separated by a pipe symbol. Within the group, we want the listagg to show the Daily_Sales in the order of the Sale_Date ascending. We can now see what the Daily_Sales has been for each Product_ID for the entire week. The listagg places the values on a single line in a list.
The next example shows customers who visited the company website, and it captures what web pages they visited in reverse order. A company can then take the first web page three entries and customize a marketing campaign. Customer one gets an email explaining that there is a sale on Electric Cords, Hammers, and Saws. For customer two, they get an email that it is “W’ week because Wrenches are on sale and bundled in with Screw Drivers and Tubing.
Now, we are going to take the next example even farther by adding a derived table that builds a temporary table that only lasts the life of this query. The derived table will hold the salesperson and what they sold per month. We will then use a listagg to build a report that shows each salesperson and their monthly sales over six months.
Here is an even easier way to listagg Snowflake data with the Nexus. Download a free trial at www.CoffingDW.com.
Step 1: Run a query against a Snowflake table. Now, press on the Analytics button.
You will now enter the Nexus Garden of Analytics, where every answer set for the day is available to you to get additional analytic reports. Nexus will do all of the calculations for you from inside your PC. Nexus makes your PC a data warehouse!
When you enter the Garden of Analytics, you must set your result set as the “Active ResultSet.” To do this, right-click on the result set and from the menu choose “Set as Active ResultSet.”
You then choose from the tabs above the ListAgg tab.
The ListAgg template will appear. You can left-click on the arrows to choose the columns you want to use for the ListAgg. You can then choose how you want the report to sort and partition. Each time you hit the arrow, the columns from the “Active Resultset” are available to be chosen.
When you finish choosing the resultset columns, you hit the CREATE button. The new ListAgg report displays. You can save the report to Excel, Tableau, CSV, and others, and you can also share the report with fellow peers. The brilliance behind the Nexus Garden of Analytics is that some databases don’t support every analytic. Only Snowflake and Amazon Redshift support ListAgg, but with Nexus, every database can get a ListAgg. Nexus takes every answer set from any system and allows you to get analytic reports that Nexus calculates for you.
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.