Skip to content
New Workbook
Sign up
Bicycle Sales (SQL)

In-depth analysis of the company's sales datasets with SQL

  1. Which product has the highest and lowest prices?
  2. Which state has the most numbers of orders? And the item that receives the most orders in this state
  3. Comparing annual sales.

1. Which product has the highest and lowest price?

Spinner
DataFrameavailable as
df1
variable

SELECT *
FROM production.products

As we can see in the table

  • The cheapest product is Strider Classic 12 Balance Bike - 2018, ID 263 (89.99)
  • The most expensive is Trek Domane SLR 9 Disc - 2018, ID 155 (11999.99)

2. Which state has the most numbers of orders? And the item that receives the most orders in this state

Spinner
DataFrameavailable as
df4
variable
SELECT o.order_id, o.customer_id,o.order_date, s.store_name, s.state
FROM sales.orders AS o
INNER JOIN sales.stores AS s
ON o.store_id = s.store_id
GROUP BY o.order_id, o.customer_id,o.order_date, s.store_name, s.state;

According to the data

  • Store in NY have the highest number of orders compare to the other 2 stores in (CA and TX)
  • There only 2 items that got ordered more than 1 time, products with id 11 (Surly Straggler 650b - 2016) and 92 (Haro Shredder 20 Girls - 2017) in NY state.
Spinner
DataFrameavailable as
df3
variable
WITH ordered_product AS (
  SELECT o.order_id, o.customer_id, o.order_date, s.store_name, s.state
  FROM sales.orders AS o
  INNER JOIN sales.stores AS s ON o.store_id = s.store_id
  GROUP BY o.order_id, o.customer_id, o.order_date, s.store_name, s.state
)
SELECT p.product_id, o2.customer_id, COUNT(o2.order_id) AS order_count
FROM sales.order_items AS p
INNER JOIN ordered_product AS o2 ON p.order_id = o2.order_id
WHERE o2.state = 'NY'
GROUP BY p.product_id, o2.customer_id;

3. Comparing annual sales

The chart showed that the annual sales were higher from 2017 and 2018 compare to 2016

  • Let's explore further with the year 2017-2018
Spinner
DataFrameavailable as
df
variable
SELECT o1.product_id, o2.order_date
FROM sales.order_items AS o1
INNER JOIN sales.orders AS o2
ON o1.order_id = o2.order_id
GROUP BY o1.product_id, o2.order_date;

Further insight of the sale (2017-2018).

  • 2017 has higher sales compare to 2018
  • store_id(2) always has the highest sale, following is store_id(1), and the at the bottom is store_id(3)
Spinner
DataFrameavailable as
df2
variable
SELECT YEAR(o.order_date) AS Year, o.order_id, o.store_id
FROM sales.orders AS o
WHERE YEAR(o.order_date) IN ('2017', '2018')

Comparing between stores, from 2017 to 2018. There is a trend across 3 stores, and sales are rapidly declining in 2018.

  • Baldwin Bikes' (NY) has the highest sales. However, in 2017 the sales were significantly greater than they were in 2018 accross 3 stores
  • The lowest sales were recorded by Rowlett Bikes (TX), and in 2018 they were likewise lower than in 2017.
Spinner
DataFrameavailable as
df6
variable
WITH store_product AS(
    SELECT o.store_id, s.product_id, COUNT(o.order_id) AS orders, YEAR(o.order_date) AS Year
    FROM sales.orders AS o
    INNER JOIN production.stocks AS s
    ON o.store_id = s.store_id
    WHERE YEAR(o.order_date) IN (2017, 2018)
    GROUP BY o.store_id, s.product_id, YEAR(o.order_date)
)
SELECT s1.store_name, s1.city, s1.state, sp.product_id, sp.orders, sp.Year
FROM sales.stores AS s1
INNER JOIN store_product AS sp
ON s1.store_id = sp.store_id;