In this assignment, you will express “complex” OLAP queries in SQL. The key point of the exercise is to observe the complexity of expressing the type of such queries despite relatively simple ideas of the queries themselves. Your mission (in addition to writing the SQL queries) is to consider the reasons for the complexity of the expression of these queries.
Generate separate reports/output based on the following queries (one report for each of the queries):
- For each customer, compute the minimum and maximum sales quantities along with the corresponding products, dates (i.e., dates of those maximum and minimum sales quantities) and the states in which the sale transactions took place. For the same customer, also compute the average sales quantity.
- For each combination of customer and product, output the maximum sales quantities for October (regardless of the year, that is, both 10/11/2016 and 10/23/2019 are considered sales transactions for October) and minimum sales quantities for November and December (again, regardless of the year) in 3 separate columns. Like the first report, display the corresponding dates (i.e., dates of those maximum and minimum sales quantities). Furthermore, for October (MAX), include only the sales that occurred after 2017 (that is, not to include sales that occurred in 2017 or earlier); for November (MIN) and December (MIN), include all sales.
- For each of the 12 months (regardless of the year), find the most “popular” and least “popular” products (those products with most and least total sales quantities) and the corresponding total sales quantities (i.e., SUMs).
- For each product, find the “most favorable” month (when most amount of the product was sold) and the “least favorable” month (when the least amount of the product was sold).
- For the years 2016, 2017, 2018, 2019 and 2020, show, for each product and customer combination, the average sales quantities for the 4 states, ‘CT’, ‘NY’, ‘NJ’ and ‘PA’ (in four separate columns). Also compute the average for the “whole” year (again ignoring the YEAR component, meaning simply compute AVG) along with the total quantities (SUM) and the counts (COUNT).
The following are sample output reports – quantities displayed are for illustration only (not the actual values).
Fall 2021
Report #1:
CUSTOMER ======== Bloom Sam Emily ….
Report #2:
CUSTOMER ======== Sam Helen Bloom
….
MIN_Q MIN_PROD MIN_DATE ST MAX_Q MAX_PROD ===== ======== ========== == ===== ========
MAX_DATE ST AVG_Q ========== == ===== 09/25/2019 NY 1435 03/23/2018 CT 56 02/02/2016 NJ 1512
12 Pepsi 1 Milk 1 Bread
01/01/2016 NJ 2893 Apple 02/15/2017 NJ 259 Banana 07/01/2018 NY 3087 Milk
PRODUCT OCT_MAX OCT_DATE NOV_MIN NOV_DATE DEC_MIN DEC_DATE
======= ======= ========== ======= ==========
======= ========== 2432 12/03/2018 9483 12/23/2017 2596 12/11/2016
Page 1 of 2
Egg Cookies Butter
8 10/11/2019 92 10/22/2018 45 10/31/2020
3234 11/24/2016 4342 11/14/2020 1923 11/10/2017
Report #3:
MONTH MOST_POPULAR_PROD MOST_POP_TOTAL_Q LEAST_POPULAR_PROD LEAST_POP_TOTAL_Q ===== ================= ================ ================== =================
1 Eggs
2 Milk
3 Pepsi
….
Report #4:
PRODUCT MOST_FAV_MO ======= =========== Egg 4 12
497214 Pepsi 1874794 Banana
974531 Milk
55526 23126 19958
Apple Banana ….
Report #5:
1 11 3 2
LEAST_FAV_MO ============
Grading
Submission
NOTE: A query with syntax errors will lose 50% of the points for the query.
PRODUCT
=======
Pepsi Sam 1923 4241 2383 1325 2988 38848 13
Milk Bread
….
Emily 239 9872 142 2435 2663 21307 8 Helen 2534 981 4239 1987 2781 25032 9
