11.9

Consider the star schema from Figure 11.2. Suppose an analyst finds that monthly total sales (sum of the price values of all sales tuples) have decreased, instead of growing, from April 2018 to May 2018. The analyst wishes to check if there are specific item categories, stores, or customer countries that are responsible for the decrease.

  1. What are the aggregates that the analyst would start with, and what are the relevant drill-down operations that the analyst would need to execute?

  2. Write an SQL query that shows the item categories that were responsible for the decrease in sales, ordered by the impact of the category on the sales decrease, with categories that had the highest impact sorted first.

  1. What are the aggregates that the analyst would start with, and what are the relevant drill-down operations that the analyst would need to execute?

For each item category the analyst should look at how much income was generated in April 2018 and May 2018 by that item category. The item category/categories that shows huge decrease is responsible for the overall income decrease.

For each store the analyst should look at how much income was generated in April 2018 and May 2018 by that store. The store that shows huge decrease is responsible for the overall income decrease.

For each customer country the analyst should look at how much income was generated in April 2018 and May 2018 from that country. The country that shows decrease is responsible for the overall income decrease.

  1. Write an SQL query that shows the item categories that were responsible for the decrease in sales, ordered by the impact of the category on the sales decrease, with categories that had the highest impact sorted first.
WITH category_sale_2018(category, month, price) AS (
    SELECT item_info.category, date_info.month, SUM(price)
    FROM sales 
        INNER JOIN item_info ON (sales.item_id = item_info.item_id)
        INNER JOIN date_info ON (sales.date = date_info.date)
    WHERE date_info.month IN ('April', 'May') AND date_info.year = 2018
    GROUP BY item_info.category, date_info.month
), x(category, april2018, may2018) AS ( 
    SELECT t.category, 
        (
            SELECT price
            FROM category_sale_2018 AS t2
            WHERE t2.category = t.category AND t2.month = 'April'
        ) AS april2018, 
        (
            SELECT price
            FROM category_sale_2018 AS t3
            WHERE t3.category = t.category AND t3.month = 'May'
        ) AS may2018, 
    FROM category_sale_2018 as t
)
SELECT category, april2018, may2018, (may2018 - april2018) AS impact
FROM x
ORDER BY impact ASC;