Bicycle Sales (SQL)

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

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

1. Which product has the highest and lowest price?

Unknown integration

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

Unknown integration

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.

Unknown integration

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

Unknown integration

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)

Unknown integration

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.

Unknown integration

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;

‌
‌
‌