Scenario Review the following scenario. Then complete the step-by-step instructions. You are a BI analyst for a grocery store chain that monitors dietary trends affecting in-store purchases. Your company wants you to examine which types of Hass avocados are purchased most often. The avocados are categorized as one of four sizes: small, medium, large, and extra large. In addition to the average price and total volume of each avocado, the date of each sale is also recorded. Using this data, you will create a historical table that illustrates how indexes and partitions work. This will allow you to practice creating partitions and clustered tables and demonstrate how to use them. Your goal is to use partitions and clusters to answer the following question: What is the distribution of avocado sales from 2015 to 2021?

To begin, create a new table without a partition or cluster. This will serve as a baseline to compare to the partitioned and clustered tables. Name it avocados.

Then, in the Editor tab, copy and paste the following SQL code and click Run. 

Next, create a table partitioned by an integer range (the years 2015 through 2022). Name it avocados_partitioned.

Return to the tab you entered the SQL code into. Delete that code then copy and paste the following SQL code. Click Run.

Next, create a table partitioned by an integer range and clustered by type. Name it avocados_clustered.

Return to the tab where you entered the SQL code. Delete that code, then copy and paste the following SQL code. Click Run.

Query the tables and compare performance

Query the Table without a partition or cluster

Query the partitioned Table

Query the partitioned and clustered Table