The Retail Sales Data Set

Using queries from Chapter 3 of “SQL for Data Analysis” by Cathy Tanimura to illustrate ggsql.

The code to create this note can be found here.

SELECT sales_month, sales
FROM 'retail_sales.parquet'
WHERE kind_of_business = 'Retail and food services sales, total'
ORDER BY 1 
VISUALIZE sales_month AS x, sales AS y
DRAW line

SELECT date_part('year',sales_month) as sales_year, 
  kind_of_business, sum(sales) as sales
FROM 'retail_sales.parquet'
WHERE kind_of_business IN 
          ('Book stores',
           'Sporting goods stores',
           'Hobby, toy, and game stores')
GROUP BY 1,2
ORDER BY 1
VISUALIZE sales_year AS x, sales AS y, kind_of_business AS color
DRAW line

SELECT sales_month, kind_of_business, sales
FROM 'retail_sales.parquet'
WHERE kind_of_business IN ('Men''s clothing stores','Women''s clothing stores')
ORDER BY 1, 2
VISUALIZE sales_month AS x, sales AS y, kind_of_business AS color
DRAW line

SELECT date_part('year',sales_month) as sales_year,
  kind_of_business, sum(sales) as sales
FROM 'retail_sales.parquet'
WHERE kind_of_business IN 
        ('Men''s clothing stores',
        'Women''s clothing stores')
GROUP BY 1, 2
ORDER BY 1, 2
VISUALIZE sales_year AS x, sales AS y, kind_of_business AS color
DRAW line

Figure 1: Yearly women’s and men’s clothing store sales

CREATE OR REPLACE TABLE wide_data AS
WITH long_data AS (
    SELECT date_part('year',sales_month) as sales_year,
        CASE WHEN (kind_of_business = 'Women''s clothing stores')
            THEN 'womens'
            ELSE 'mens'
        END AS kind_of_business,
        sum(sales) as sales
    FROM 'retail_sales.parquet'
    WHERE kind_of_business IN
            ('Men''s clothing stores',
            'Women''s clothing stores')
    GROUP BY 1, 2)
PIVOT long_data ON kind_of_business USING sum(sales)

SELECT sales_year, womens - mens AS womens_minus_mens
FROM wide_data
WHERE sales_year <= 2019
ORDER BY 1
VISUALIZE sales_year AS x, womens_minus_mens AS y
DRAW line

Figure 2: Difference between women’s to men’s annual retail clothing sales

SELECT sales_year, womens / mens AS womens_times_of_mens
FROM wide_data
WHERE sales_year <= 2019
ORDER BY 1
VISUALIZE sales_year AS x, womens_times_of_mens AS y
DRAW line

Figure 3: Ratio of women’s to men’s annual retail clothing sales