SQL Course Case Study

I I N N F F O O P P O O W W E E R R A A C C A A D D E E M M Y Y

Learning to Fly:

Technical Essentials

SQL COURSE Case Study Module

Case Studies

This section will cost about 1 hour. (Estimated time)

Case 1:

Company A would like to perform revenue and profitability analysis on the customers who frequently purchase products (ie. to whom sales are made on average 3 times a month or more) for a minimum amount of $20,000 on the period of interest.

To enable that, create a single SQL query that will return the following fields (named in the same way as hereunder):

Customer | Sales Amount | Cost Amount | Sales Margin (Sales Amount –Cost Amount)

for year 2000 and 2001 for customers who have bought an average of at least 3 times a month1 in 2000 and 2001 and to whom the total sales amount has been greater than 20,000 over the period.

Order this query in ascending order based on the customer name.

Note: 1 which means that there is an average of 3 days or more a month in 2000 and 2001 where these customers have transactions in the sales table.

Advices before proceeding:

∙Use a progressive approach to isolate each major concept

∙Use Subselect queries to get the final result

∙Be careful with average, especially if a customer does not purchase a product every month (no data in the Sales fact for that month!)

Hints:

1. You may need at least two HAVING clauses, and two or three subqueries,

in either WHERE or FROM clauses.

2. You may find another solution that AVG to calculate the average. Why is

that?

Case 2:

∙Company A would like to know more about their product s’ sales performance in 2001, and view the products in descending order starting with the products that

have the best contribution margin. Products that did not sell during this period

should be displayed as well.

Final Reports Fields:

Product Name | Sales Amount | Margin | Contribution Margin

Main KPIs definitions:

- Margin = Sales Amount – Cost Amount (Unit Cost * Quantity Sold)

- Margin Contribution = (Margin Amount / Sales Amount)* 100 ∙Refine the previous query to view only the TOP 5 products in terms of Margin (use TOP x function from Oracle)

∙Refine the last query to include the Sales Weight of the top 5 products (Sales for the product / Sales for All products)

Product Name | Sales Amount | Sales_Weight | Margin | Contribution Margin What can you conclude from this?

Hint: The last question may require a Subselect query