Link: https://awsrestart.instructure.com/courses/1632/modules/items/886885

Lab Overview

Scenario

The sales department has once again come to you for a request for a few more pieces of information. They would like to target the customers that spend the most money in a single purchase based on the month of purchase. The primary product that the sales team would like to market to the high rolling customers would be the top 20 most expensive books that have three words or more in the title. The sales team brought a research paper that shows that this was a great marketing gimmick for other stores. See if you can accommodate the market research requests the sales team are looking for.

Objectives

In this lab, you will:

• Rank each purchase by the quantity sold by the month that the product was sold in
• Select the top 20 most expensive books with three words in the title

Exercise 1: Rank Purchases by Quantity Sold and Month

In order to provide the information the sales team requires, you need to build a list of customers that spend the most money in a single purchase. Rank the purchases based on quantity by the month they were sold in.

TODO

Rank each purchase made based on the quantity sold per month. Create a list of customer IDs to provide to the sales team.

Steps

1. Ensure that you are logged into the workspace and have MySQL Workbench open and are connected to the pub1 database.

2. In Schemas section of the navigation pane in MySQL Workbench, right-click the sales table and select the Select Rows – Limit 1000 option.

3. Edit the query in the sales table query pane by typing SELECT sldate, partnum, qty, custnum,.

4. After the proper columns have been selected, use RANK() in order to create a new column called quantity_rank. This will be ordered by qty and partitioned by the month name of the date in the sldate column. To do this, type the following query:

    SELECT
    sldate, partnum, qty, custnum,
    RANK() OVER (PARTITION by MONTHNAME(sldate) ORDER BY qty DESC) quantity_rank
    FROM pub1.sales ORDER BY quantity_rank;
   

5. Click the lightning bolt to execute the query.

Output