172|SQL – Skyline Subquery Sweepstakes

BYU Student Author: @Sterling_Lane
Reviewers: @James_Gerstner, @Andrew_Wilson
Estimated Time to Solve: 35 minutes

We provide the solution to this challenge using:

  • SQL

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.

  1. 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.
  2. 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!
  3. 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