69|SQL – Bike Shop Shipping Delays

Challenges
, ,
1

BYU Student Author: @Andrew
Reviewers: @Jae
Estimated Time to Solve: 30 Minutes

We provide the solution to this challenge using:

  • SQL (Azure Data Studio)

Need a program? Click here.

Overview
You are an accountant for a small bicycle company. Recently, you received an urgent email from the company’s sales team. They noticed a backlog of sales orders that have not yet been shipped, and they need a report to understand the scope of the problem.

They have requested a report including the following items:

  1. A table displaying the number of unshipped orders, the average time between the order and the current date (use 2018-12-29 for the current date) for each store for all unshipped orders.
  2. A table showing each order ID, order date, store name, customer name, and time between the order and the current date (use 2018-12-29 for the current date) for all unshipped orders. Sort the table by oldest orders to newest orders.

You will deliver this report to the sales manager in an excel file containing the relevant data. Format column names into readable formats for the sales manager.

Instructions
First, follow the instructions below to install Azure Data Studio and then load the database. If you already have Azure Data Studio installed and a local server of Microsoft SQL Server then skip to loading the database.

Azure Installation Instructions

  1. Install Microsoft SQL Server (if not already installed) found here. Download the developer version (seen in the image below) and install package. When prompted select the basic installation package.
    Challenge69_ImageA

  2. Install Azure Data Studio (if not already installed). Installation instructions can be found here.

  3. Open Azure Data Studio and set up a new local server instance (if not already done):

    Challenge69_ImageB
    Challenge69_ImageB996×691 94.2 KB

    Challenge69_ImageC

Database Loading Instructions
  1. Download the database setup file: Challenge69_Database.sql.
  2. Open it in Azure Data Studio, make sure the selected server is localhost, and run the file. Running the file will create a database named BikeStores, then create tables and insert data into those tables.

For more information on the database structure, tables, and fields see SQLServerTutorial.net’s data dictionary here.

This database, ERD, and data dictionary are all owned by SQLServerTutorial.net and used within this challenge under their terms of use.

Second, review the entity-relationship diagram for the BikeStores database. The ERD will be useful in writing your queries.

Challenge69_ImageD

Third, setup a new query (CTRL + N shortcut) to complete your data retrieval to build the requested tables and export them to an excel file.

The first table will output 3 rows and columns named:

  • Store Name
  • Number of Orders
  • Avg Days Since Order
Table 1 Hints

The general structure of this query is:

SELECT

  • Store Name
  • Number of Orders => COUNT(order_id)
  • Avg Days Since Order => AVG(DATEDIFF(day, order_date,'2018-12-29'))

FROM

  • Join the sales orders table with the sales stores table on store ID

WHERE

  • Include your filter to only capture the orders that have not shipped => shipped_date IS NULL

GROUP BY

  • Group by store name

ORDER BY

  • Order by store name

The second table will output 170 rows and columns named:

  • Order ID
  • Order Date
  • Store
  • Customer
  • Days Since Order
Table 2 Hints

The general structure of this query is:

SELECT

  • Order ID
  • Order Date
  • Store
  • Customer => (first_name + ' ' + last_name)
  • Days Since Order => DATEDIFF(day,order_date,'2018-12-29')

FROM

  • Required tables:
    • Sales.Orders
    • Sales.Customers
    • Sales.Stores

WHERE

  • Include your filter to only capture the orders that have not shipped => shipped_date IS NULL

ORDER BY

  • Order by order date

To export tables to excel from Azure Data Studio, click the icon (displayed below) on the right of the result table. You will be prompted to select a location for your table to be saved in a .xlsx file.
Challenge69_ImageE

Solution

Solution Code 
--TABLE 1 
SELECT  
    ss.store_name AS 'Store Name', 
    COUNT(so.order_id) AS 'Number of Orders', 
    AVG(DATEDIFF(day,so.order_date,'2018-12-29')) AS 'Avg Days Since Order' 

    FROM  
        sales.orders so  
        JOIN sales.stores ss 
            ON so.store_id = ss.store_id 

    WHERE so.shipped_date IS NULL 

    GROUP BY ss.store_name 

    ORDER BY ss.store_name; 
-- 

--TABLE 2 
SELECT  
    so.order_id AS 'Order ID', 
    so.order_date AS 'Order Date', 
    ss.store_name AS 'Store', 
    (sc.first_name + ' ' + sc.last_name) AS 'Customer', 
    DATEDIFF(day,so.order_date,'2018-12-29') AS 'Days Since Order' 

    FROM 
        sales.orders so 
        JOIN sales.customers sc 
            ON so.customer_id = sc.customer_id 
        JOIN sales.stores ss 
            ON  so.store_id = ss.store_id 

    WHERE so.shipped_date IS NULL 

    ORDER BY so.order_date;

Challenge69_SQL_Solution.sql
Challenge69_Excel_Solution.xlsx
Solution Video: Challenge 69|SQL – Bike Shop Shipping Delays

2

I totally couldn’t figure out that the table names had two names for the longest time. I’m sure I’m the only one, but eventually, I did get it. Thank goodness. I also learning about the DATEDIFF function. I was unaware of the previously. I couldn’t figure out to upload, so I just pasted my code below.

`USE BikeStores;
GO

SELECT 
    ss.store_name AS 'StoreName',
    COUNT(so.order_id) AS 'NumberofOrders',
    AVG(DATEDIFF(day,so.order_date, '2018-12-29')) AS TimeWaiting

    FROM 
        sales.orders AS so INNER JOIN sales.stores AS ss ON so.store_id = ss.store_id  
    WHERE so.shipped_date IS NULL
    GROUP BY ss.store_name
    ORDER BY ss.store_name;


    SELECT so.order_id AS OrderID, 
    so.order_date AS OrderDate, 
    ss.store_id AS StoreID, 
    (sc.first_name + sc.last_name) AS CustomerName,
    DATEDIFF(day,so.order_date, '2018-12-29') AS TimeWaiting
    FROM (Sales.Orders AS so INNER JOIN Sales.Stores AS ss ON so.store_id = ss.store_id) INNER JOIN Sales.customers AS sc ON so.customer_id=sc.customer_id
    WHERE so.shipped_date IS NULL
    ORDER BY so.order_date`
18 Likes
3

:heart: I like this solution

4

With a little help from ChatGPT, here’s the code I ended with:
SELECT
ss.store_name AS StoreName,
COUNT(so.order_id) AS NumberofOrders,
AVG(DATEDIFF(‘2018-12-29’, so.order_date)) AS TimeWaiting
FROM
sales.orders so
JOIN sales.stores ss
ON so.store_id = ss.store_id
WHERE so.shipped_date IS NULL
GROUP BY ss.store_name
ORDER BY ss.store_name;
SELECT
so.order_id AS OrderID,
so.order_date AS OrderDate,
ss.store_id AS StoreID,
CONCAT(sc.first_name, ’ ', sc.last_name) AS CustomerName,
DATEDIFF(‘2018-12-29’, so.order_date) AS TimeWaiting
FROM
sales.orders so
JOIN sales.stores ss
ON so.store_id = ss.store_id
JOIN sales.customers sc
ON so.customer_id = sc.customer_id
WHERE so.shipped_date IS NULL
ORDER BY so.order_date;

1 Like
5

Here is the code I came up with!

– First statement–
SELECT
ss.store_name AS StoreName,
COUNT(so.order_id) AS NumberOfOrders,
AVG(DATEDIFF(day, so.order_date, ‘2018-12-29’)) AS TimeWaiting
FROM
sales.orders AS so
INNER JOIN
sales.stores AS ss ON so.store_id = ss.store_id
WHERE
so.shipped_date IS NULL
GROUP BY
ss.store_name
ORDER BY
ss.store_name;

– Second statement–
SELECT
so.order_id AS OrderID,
so.order_date AS OrderDate,
ss.store_id AS StoreID,
(sc.first_name + sc.last_name) AS CustomerName,
DATEDIFF(day, so.order_date, ‘2018-12-29’) AS TimeWaiting
FROM
(Sales.Orders AS so
INNER JOIN
Sales.Stores AS ss ON so.store_id = ss.store_id)
INNER JOIN
Sales.customers AS sc ON so.customer_id = sc.customer_id
WHERE
so.shipped_date IS NULL
ORDER BY
so.order_date;

6

SELECT
ss.store_name
,COUNT(so.order_id) AS Total_Unshipped_Orders
,AVG(DATEDIFF(day,so.order_date, ‘2018-12-29’)) AS Avg_Time_Waiting
FROM
sales.orders so
INNER JOIN sales.stores ss ON so.store_id = ss.store_id
WHERE so.shipped_date IS NULL
GROUP BY ss.store_name;

SELECT
so.order_id
,so.order_date
,ss.store_name
,CONCAT(sc.first_name, ’ ', sc.last_name)
,DATEDIFF(day,order_date,‘2018-12-29’)
FROM sales.orders so
INNER JOIN sales.stores ss ON so.store_id = ss.store_id
INNER JOIN sales.customers sc ON so.customer_id = sc.customer_id
Where so.shipped_date IS NULL
ORDER BY so.order_date;

7

SELECT
ss.store_name,
COUNT(so.order_id) AS Unshipped_Orders,
AVG(DATEDIFF(day, so.order_date, ‘2018 - 12 - 29’)) AS Avg_Days_Since_Order
FROM
sales.orders so
INNER JOIN
sales.stores ss ON so.store_id = ss.store_id
WHERE
so.shipped_date IS NULL
GROUP BY ss.store_name;

SELECT
so.order_id,
so.order_date,
ss.store_name,
CONCAT(sc.first_name, ’ ', sc.last_name) as Customer_Name,
DATEDIFF(day, order_date, ‘2018 - 12 - 29’) as Days_Since_Order
FROM
sales.orders so
INNER JOIN
sales.stores ss ON so.store_id = ss.store_id
INNER JOIN
sales.customers sc ON so.customer_id = sc.customer_id
WHERE
so.shipped_date IS NULL
ORDER BY so.order_date;

8

SELECT
ss.store_name
COUNT(so.order_id) AS Total_Unshipped_Orders
AVG(DATEDIFF(day, so.order_date, ‘2018-12-29’)) AS Avg_Time_Waiting
FROM
sales.orders so
INNER JOIN
sales.stores ss ON so.store_id = ss.store_id
WHERE
so.shipped_date IS NULL
GROUP BY
ss.store_name;

SELECT
so.order_id
so.order_date
ss.store_name
CONCAT(sc.first_name, ’ ', sc.last_name)
DATEDIFF(day, order_date, ‘2018-12-29’)
FROM
sales.orders so
INNER JOIN
sales.stores ss ON so.store_id = ss.store_id
INNER JOIN
sales.customers sc ON so.customer_id = sc.customer_id
WHERE
so.shipped_date IS NULL
ORDER BY
so.order_date;