BYU Student Author: @Sterling_Lane
Reviewers: @James_Gerstner, @Andrew_Wilson
Estimated Time to Solve: 35 minutes
We provide the solution to this challenge using:
Need a program? Click here.
Overview
Welcome to Skyline, LLC, an illustrious e-commerce empire renowned for its diverse product offerings, from high-tech gadgets to home essentials. Skyline has recently encountered a series of intriguing anomalies in its vast database. Amidst the bustling digital marketplace, a set of curious patterns have emerged, challenging the analytical prowess of the company’s data team. You’ve been hired by Skyline’s CFO to delve into the company’s data and solve this conundrum by using your SQL prowess. The charismatic CFO has given you a list of his concerns in his usual emotional tone. Your task? Answer the CFO’s questions using your knowledge of SQL. Let’s jump into it!
Instructions
Download the starting Microsoft Access file. This file contains all the relevant tables loaded into Access with the relationships already defined and ready for you to begin your journey into analyzing Skyline’s inventory and sales data. When you’re ready, click Query Design in the Create window, switch to the SQL view, and write code to answer the questions below. Make sure to save your answers to these questions as separate queries in Access.
- Some of our products are seriously underperforming, and we can’t find which products they are!! Give me the names of the five most underwhelming products in terms of total number sold.
a. We’ll have to seriously re-evaluate whether we continue to sell these products if they are homegrown Skyline original products (products with the word “Sky” in the name). Give me a separate report showing which of the underwhelming products are Skyline originals.
- Why are some of our customers showing up as duplicates?! Is our database correctly recording who is placing each specific order? Give me a report showing all customers who are duplicated in our system. Remember, each customer has an email that is unique to only them, so a list of emails is fine!
a. For all the duplicate customers, I want a report showing the customer’s first and last name in one column and their email in another column. With this report, I can talk to our ERP team and get this issue fixed quickly!
- Why are we giving discounts of 10% to some customers? That’s way too generous! How much money has my company lost because of these 10% discounts? I don’t want any of the fluff breaking it down by product or anything; just give me the number!
a. My accounting staff tells me we are actually in danger of giving out too many 5% discounts, but I don’t believe them. Just in case, go ahead and give me a report showing just the difference between the amount we are losing from the 10% discount and the amount we are losing from the 5% discount. I can’t wait to prove them wrong!
Data Files
Suggestions and Hints
- For Question 1, consider writing two queries for this question and using the result of your first query as a subquery for your second query. The LIKE statement may be helpful as well.
- When using a subquery in the FROM statement, remember to use an alias for the table.
- FROM (table) AS {aliasname}
- Using a subquery in the WHERE statement may be useful to help you with question 2a. If you can create a query showing a list of results you want to include in your output (similar to the output for the first part of question 2), you can use the IN function within the WHERE statement to help you filter out records in your outer query.
- Subqueries can be used in the SELECT statement to perform calculations as long as each subquery produces one result. Thus, once you get your answer to the total amount lost to 10% customers for question 3, try using some copy and paste to figure out the magnitude of the differences between the two types of discounts.
Solution
Time to Complete: 2 hours
Difficulty: Intermediate
Comments: Had some extra time this summer and wanted to brush up on SQL. This was a fantastic challenge to quickly relearn SQL, which is why it took me 2 hours because I had to relearn SQL. I was able to quickly regain confidence via this challenge. I got tripped up on aggregating the sum of discounts, but it was more simple than I realized. Thanks for the great challenge!
Time to Complete: 30 min
Difficulty Intermediate
Comments: I learned that Access doesn’t allow for the LIMIT clause. Your solution video helped with question 3b, I wouldn’t have thought to use the DISTINCT clause!
SOLUTION:
-
SELECT TOP 5 p.ProductName, SUM(od.Quantity) AS NumberSold
FROM Product AS p
INNER JOIN OrderDetail AS od ON p.ProductID=od.ProductID
GROUP BY p.ProductName
ORDER BY SUM(od.Quantity)
-
SELECT c.FirstName & c.LastName AS CustomerName, c.Email
FROM Customer AS c
GROUP BY c.FirstName & c.LastName, c.Email
HAVING COUNT (CustomerID) >1
-
SELECT SUM ((p.Price*od.Quantity)*od.Discount) AS DiscountTaken
FROM OrderDetail AS od
INNER JOIN Product AS p ON od.ProductID=p.ProductID
WHERE od.discount=0.1
3b. SELECT DISTINCT (SELECT SUM ((p.Price*od.Quantity)od.Discount) AS DiscountTaken
FROM OrderDetail AS od
INNER JOIN Product AS p ON od.ProductID=p.ProductID
WHERE od.discount=0.1) - (SELECT SUM ((p.Priceod.Quantity)*od.Discount) AS DiscountTaken
FROM OrderDetail AS od
INNER JOIN Product AS p ON od.ProductID=p.ProductID
WHERE od.discount=0.05) AS DiscountDifference
FROM OrderDetail AS od
Time to Complete: 30 minutes
Difficulty: Intermediate
Notes: This was an awesome example. This will be so useful in a business setting!
Solution:
1a:
SELECT TOP 5 p.ProductName, SUM (od.Quantity) AS TotalSold
FROM Product AS p
INNER JOIN OrderDetail AS od ON p.ProductID = od.ProductID
GROUP BY p.ProductName
ORDER BY 2;
1b:
SELECT *
FROM (SELECT TOP 5 p.ProductName, SUM (od.Quantity) AS TotalSold
FROM Product AS p
INNER JOIN OrderDetail AS od ON p.ProductID = od.ProductID
GROUP BY p.ProductName
ORDER BY 2) AS anna
WHERE ProductName LIKE ‘Sky’;
2a:
SELECT Email
FROM Customer
GROUP BY Email
HAVING COUNT(CustomerID) > 1;
2b:
SELECT DISTINCT FirstName& ’ ’ & LastName AS CustFullName, Email
FROM Customer
WHERE Email IN (SELECT Email
FROM Customer
GROUP BY Email
HAVING COUNT(CustomerID) > 1);
3a:
SELECT SUM((p.Price*od.Quantity) * od.Discount) AS TotalDiscount
FROM OrderDetail AS od
INNER JOIN Product AS p ON od.ProductId=p.ProductID
WHERE od.Discount = 0.1;
3b:
SELECT DISTINCT (SELECT SUM((p.Priceod.Quantity) * od.Discount) AS TotalDiscount
FROM OrderDetail AS od
INNER JOIN Product AS p ON od.ProductId=p.ProductID
WHERE od.Discount = 0.1) - (SELECT SUM((p.Priceod.Quantity) * od.Discount) AS TotalDiscount
FROM OrderDetail AS od
INNER JOIN Product AS p ON od.ProductId=p.ProductID
WHERE od.Discount = 0.05) AS DiscountDifference
From OrderDetail;
Time to Complete: 30 minutes
Difficulty: Intermediate
1a:
SELECT TOP 5 p.ProductName, SUM (od.Quantity) AS TotalSold
FROM Product AS p
INNER JOIN OrderDetail AS od ON p.ProductID = od.ProductID
GROUP BY p.ProductName
ORDER BY 2;
1b:
SELECT *
FROM (SELECT TOP 5 p.ProductName, SUM (od.Quantity) AS TotalSold
FROM Product AS p
INNER JOIN OrderDetail AS od ON p.ProductID = od.ProductID
GROUP BY p.ProductName
ORDER BY 2) AS anna
WHERE ProductName LIKE ‘Sky’;
2a:
SELECT Email
FROM Customer
GROUP BY Email
HAVING COUNT(CustomerID) > 1;
2b:
SELECT DISTINCT FirstName& ’ ’ & LastName AS CustFullName, Email
FROM Customer
WHERE Email IN (SELECT Email
FROM Customer
GROUP BY Email
HAVING COUNT(CustomerID) > 1);
3a:
SELECT SUM((p.Price*od.Quantity) * od.Discount) AS TotalDiscount
FROM OrderDetail AS od
INNER JOIN Product AS p ON od.ProductId=p.ProductID
WHERE od.Discount = 0.1;
3b:
SELECT DISTINCT (SELECT SUM((p.Priceod.Quantity) * od.Discount) AS TotalDiscount
FROM OrderDetail AS od
INNER JOIN Product AS p ON od.ProductId=p.ProductID
WHERE od.Discount = 0.1) - (SELECT SUM((p.Priceod.Quantity) * od.Discount) AS TotalDiscount
FROM OrderDetail AS od
INNER JOIN Product AS p ON od.ProductId=p.ProductID
WHERE od.Discount = 0.05) AS DiscountDifference
From OrderDetail;
Time to Complete: 30 min
Difficulty: Intermediate
SELECT TOP 5 p.ProductName, SUM(od.Quantity) AS TotalOrdered
FROM Product p
LEFT JOIN OrderDetail od ON p.ProductID = od.ProductID
GROUP BY p.ProductID, p.ProductName
ORDER BY TotalOrdered;
SELECT email, COUNT(email) AS Duplicates
FROM Customer
GROUP BY email
HAVING COUNT(email) > 1
ORDER BY Duplicates DESC;
SELECT FirstName & ’ ’ & LastName AS Name, email, COUNT(email) AS Duplicates
FROM Customer
GROUP BY FirstName, LastName, email
HAVING COUNT(email) > 1;
SELECT SUM(od.discount=0.1*p.price) AS TotalDIscounted@10
FROM OrderDetail AS od INNER JOIN Product AS p ON od.productid=p.productid
WHERE od.discount = 0.1
SELECT
SUM(CASE WHEN od.discount = 0.1 THEN 0.1 * p.price ELSE 0 END) AS TotalDiscountedAt10,
SUM(CASE WHEN od.discount = 0.05 THEN 0.05 * p.price ELSE 0 END) AS TotalDiscountedAt5
FROM
OrderDetail AS od
INNER JOIN
Product AS p
ON
od.productid = p.productid
WHERE
od.discount IN (0.1, 0.05);
Time to Complete: 30 min
Difficulty: Intermediate
Comment: Great problem! I enjoyed the nuances to each problem that made it more difficult than a basic query. Great challenge!
Solution:
- SELECT TOP 5 ProductName, SUM(Quantity) AS TotalSold
FROM Product AS p LEFT JOIN OrderDetail AS od ON p.ProductID=od.ProductID
GROUP BY ProductName
ORDER BY Sum(Quantity);
1a) SELECT ProductName, TotalSold
FROM (SELECT TOP 5 ProductName, SUM(Quantity) AS TotalSold
FROM Product p LEFT JOIN OrderDetail od ON p.ProductID=od.ProductID
GROUP BY ProductName
ORDER BY Sum(Quantity)) AS [%$##@_Alias]
WHERE ProductName LIKE “sky”;
-
SELECT (FirstName & " " & LastName) AS NAME, Email
FROM Customer AS c
GROUP BY FirstName, LastName, Email
HAVING COUNT(email) >= 2;
-
SELECT Sum((Quantity * Price)*.1) AS MoneyLost
FROM Product AS p INNER JOIN OrderDetail AS od ON od.ProductID=p.ProductID
WHERE Discount = .1;
3a) SELECT ((SELECT
Sum((Quantity * Price).1) AS MoneyLost
FROM Product AS p INNER JOIN OrderDetail AS od ON od.ProductID=p.ProductID
WHERE Discount = .1)
- SUM((Quantity * Price).05)) AS TotalDiff
FROM Product AS p INNER JOIN OrderDetail AS od ON p.ProductID=od.ProductID
WHERE Discount=.05;