Learning Snowflake SQL and Scripting

Chapter 7. Grouping and Aggregates

Databases must store data at the lowest level of granularity needed for any particular operation. For example, the Snowflake sample database stores data about customer orders, which is needed for business operations such as billing and inventory management. However, you can opt to work with the data at higher levels, such as yearly sales per region, which would be helpful for other parts of the business such as marketing and sales. This chapter discusses the ways that data in a Snowflake database can be grouped together to meet various types of business needs.

Grouping Concepts

Let’s say you were asked by the vice president of the marketing department to help with a special promotion to reward the best customers with 25% off their next order. This promotion will be for customers who have spent $1,800,000 or more or who have placed eight or more orders. Your job is to determine which customers will be eligible for the promotion.

The sample database has 115,269 orders, so you certainly don’t want to read through all the data at that level of granularity. What you need to do is group the orders together by customer, then count the number of orders, and sum the total price of the orders. This can be achieved using the group by clause, which is used to group rows together using common values of one or more columns. Here’s the first step in generating the data for the VP of marketing:

PUBLIC>select o_custkey
       from orders
       group by o_custkey; ...