69|SQL – Bike Shop Shipping Delays

Challenges
, ,
21

Time to complete: 20mins
Rating: Beginner

Q1: 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;

Q2: 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

22

Q1:
SELECT
s.store_name AS [Store Name],
COUNT(o.order_id) AS [Number of Orders],
AVG(DATEDIFF(day, o.order_date, ‘2018-12-29’)) AS [Avg Days Since Order]
FROM Sales.Orders o
INNER JOIN Sales.Stores s
ON o.store_id = s.store_id
WHERE o.shipped_date IS NULL
GROUP BY s.store_name
ORDER BY s.store_name;

Q2:
SELECT
o.order_id AS [Order ID],
o.order_date AS [Order Date],
s.store_name AS [Store],
(c.first_name + ’ ’ + c.last_name) AS [Customer],
DATEDIFF(day, o.order_date, ‘2018-12-29’) AS [Days Since Order]
FROM Sales.Orders o
INNER JOIN Sales.Stores s
ON o.store_id = s.store_id
INNER JOIN Sales.Customers c
ON o.customer_id = c.customer_id
WHERE o.shipped_date IS NULL
ORDER BY o.order_date;

Time: 15 minutes

Difficulty:ok

23

Time: 15 minutes
Rating: Beginner
1.
SELECT ss.store_name, COUNT (so.order_id) AS Toatal_Unshipped_Orders, AVG(DATEDIFF(day,so.order_date, '2018-12-29)) AS Avg_Time_Waiting
FROM sales.orders 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

SELECTso.order_id, so.order_date, ss.store_name, CONCAT(sc.first_name, ’ ', sc.last_name), DATEDIFF(day,order_date,'2018-12-29)
FROM sales.order 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

24

Intermediate
1.
SELECT ss.store_name
FROM sales.orders AS so
COUNT(so.order_id) AS Total_Unshipped_Orders
AVG(DATEDIFF(day, so.order_date, ‘2018-12-29’)) AS Avg_Time_Waiting
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
DATEDIFF(day, so.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;