Bicycle Company SQL Project v3

ANNUAL PERFORMANCE YOY

DataFrameas

df5

variable

WITH order_data AS (
	SELECT 
		DATEPART(YEAR, o.shipped_date) AS ship_year,
		o.order_id,
	    oi.product_id,
		oi.quantity,
	    oi.list_price,
	    oi.discount
	FROM sales.orders AS o
	INNER JOIN sales.order_items AS oi
	ON o.order_id = oi.order_id
	WHERE YEAR(o.shipped_date) BETWEEN 2016 AND 2017 AND o.shipped_date IS NOT NULL
),
annual_sales AS (
	SELECT 
		order_data.ship_year AS ship_year,
		CAST(COUNT(DISTINCT order_data.order_id) AS DECIMAL) AS total_orders,
	    CAST(SUM(order_data.quantity) AS DECIMAL) AS total_quantity,
	    SUM(order_data.quantity * order_data.list_price * (1 - order_data.discount)) AS total_net_sales,
	    (SUM(order_data.quantity * order_data.list_price) - SUM(order_data.quantity * order_data.list_price * order_data.discount)) / SUM(order_data.quantity) AS net_average_selling_price
	FROM order_data
	GROUP BY order_data.ship_year
), 
change AS (
	SELECT 
		ship_year,
		total_orders,
	    total_orders - LAG(total_orders) OVER (ORDER BY ship_year) AS total_orders_change,
	    total_quantity,
	    total_quantity - LAG(total_quantity) OVER (ORDER BY ship_year) AS total_quantity_change,
	    total_net_sales,
	    total_net_sales - LAG(total_net_sales) OVER (ORDER BY ship_year) AS total_net_sales_change,
	    net_average_selling_price,
	    net_average_selling_price - LAG(net_average_selling_price) OVER (ORDER BY ship_year) AS net_average_selling_price_change
	FROM annual_sales
),
percentage_change AS (
	SELECT
		ship_year,
		total_orders,
	    total_orders_change,
	    (CAST(total_orders - LAG(total_orders) OVER (ORDER BY ship_year) AS DECIMAL) / LAG(total_orders) OVER (ORDER BY ship_year) * 100) AS orders_percentage_change,
	    total_quantity,
	    total_quantity_change,
	    (total_quantity - LAG(total_quantity) OVER (ORDER BY ship_year)) / 	LAG(total_quantity) OVER (ORDER BY ship_year) * 100 AS quantity_percentage_change,
	    total_net_sales,
	    total_net_sales_change,
		((total_net_sales - LAG(total_net_sales) OVER (ORDER BY ship_year)) / LAG(total_net_sales) OVER (ORDER BY ship_year)) * 100 AS net_sales_percentage_change,
	    net_average_selling_price,
	    net_average_selling_price_change,
		((net_average_selling_price - LAG(net_average_selling_price) OVER (ORDER BY ship_year)) / LAG(net_average_selling_price) OVER (ORDER BY ship_year)) * 100 AS net_asp_percentage_change
	FROM change
)
SELECT
		ship_year,
		total_orders,
	    total_orders_change,
		ROUND(orders_percentage_change,2) AS orders_percentage_change,
	    total_quantity,
	    total_quantity_change,
	    ROUND(quantity_percentage_change,2) AS quantity_percentage_change,
	    total_net_sales,
	    total_net_sales_change,
		ROUND(net_sales_percentage_change,2) AS net_sales_percentage_change,
	    net_average_selling_price,
	    net_average_selling_price_change,
		ROUND(net_asp_percentage_change,2) AS net_asp_percentage_change
FROM percentage_change
ORDER BY ship_year;

MONTHLY ORDER PERFORMANCE YOY

DataFrameas

df8

variable

WITH order_data AS (
	SELECT 
		DATEPART(YEAR, o.shipped_date) AS ship_year,
	    DATEPART(MONTH, o.shipped_date) AS ship_month,
		o.order_id
	FROM sales.orders AS o
	INNER JOIN sales.order_items AS oi
	ON o.order_id = oi.order_id
	WHERE YEAR(o.shipped_date) BETWEEN 2016 AND 2017 AND o.shipped_date IS NOT NULL
),
total_orders_current_year AS (
	SELECT 
		order_data.ship_year,
		order_data.ship_month,
		CAST(COUNT(DISTINCT order_data.order_id) AS DECIMAL) AS total_orders_current_year
	FROM order_data
	GROUP BY order_data.ship_year, order_data.ship_month
),
total_orders_last_year AS (
	SELECT
		ship_year,
		ship_month,
		total_orders_current_year,
		LAG(total_orders_current_year) OVER (PARTITION BY ship_month ORDER BY ship_year) AS total_orders_last_year
	FROM total_orders_current_year
),
total_orders_change AS (
	SELECT
		ship_year,
		ship_month,
		total_orders_current_year,
		total_orders_last_year,
		(total_orders_current_year - total_orders_last_year) AS total_orders_change
	FROM total_orders_last_year
),
total_orders_percentage_change AS (
	SELECT
		ship_year,
		ship_month,
		total_orders_current_year,
		total_orders_last_year,
		total_orders_change,
		(total_orders_change / total_orders_last_year) * 100 AS total_orders_percentage_change
	FROM total_orders_change
),
cleanup_nulls AS (
	SELECT
		ship_year,
		ship_month,
		total_orders_current_year,
		total_orders_last_year,
		total_orders_change,
		CASE
			WHEN total_orders_percentage_change IS NULL THEN 0
		    ELSE total_orders_percentage_change
	        END AS total_orders_percentage_change
	FROM total_orders_percentage_change
)
SELECT
	ship_year,
	ship_month,
	total_orders_current_year,
	total_orders_last_year,
	total_orders_change,
	ROUND(total_orders_percentage_change,2) AS total_orders_percentage_change
FROM cleanup_nulls
WHERE ship_year = 2017;

MONTHLY QUANTITY PERFORMANCE YOY

DataFrameas

df13

variable

WITH order_data AS (
	SELECT 
		DATEPART(YEAR, o.shipped_date) AS ship_year,
	    DATEPART(MONTH, o.shipped_date) AS ship_month,
		o.order_id,
		oi.quantity
	FROM sales.orders AS o
	INNER JOIN sales.order_items AS oi
	ON o.order_id = oi.order_id
	WHERE YEAR(o.shipped_date) BETWEEN 2016 AND 2017 AND o.shipped_date IS NOT NULL
),
total_quantity_current_year AS (
	SELECT 
		order_data.ship_year,
		order_data.ship_month,
		CAST(SUM(order_data.quantity) AS DECIMAL) AS total_quantity_current_year
	FROM order_data
	GROUP BY order_data.ship_year, order_data.ship_month
),
total_quantity_last_year AS (
	SELECT
		ship_year,
		ship_month,
		total_quantity_current_year,
		LAG(total_quantity_current_year) OVER (PARTITION BY ship_month ORDER BY ship_year) AS total_quantity_last_year
	FROM total_quantity_current_year
),
total_quantity_change AS (
	SELECT
		ship_year,
		ship_month,
		total_quantity_current_year,
		total_quantity_last_year,
		(total_quantity_current_year - total_quantity_last_year) AS total_quantity_change
	FROM total_quantity_last_year
),
total_quantity_percentage_change AS (
	SELECT
		ship_year,
		ship_month,
		total_quantity_current_year,
		total_quantity_last_year,
		total_quantity_change,
		(total_quantity_change / total_quantity_last_year) * 100 AS total_quantity_percentage_change
	FROM total_quantity_change
)
SELECT
	ship_year,
	ship_month,
	total_quantity_current_year,
	total_quantity_last_year,
	total_quantity_change,
	ROUND(total_quantity_percentage_change,2) AS total_quantity_percentage_change
FROM total_quantity_percentage_change
WHERE ship_year = 2017;

MONTHLY SALES PERFORMANCE YOY

DataFrameas

df9

variable

WITH order_data AS (
	SELECT 
		DATEPART(YEAR, o.shipped_date) AS ship_year,
	    DATEPART(MONTH, o.shipped_date) AS ship_month,
		oi.quantity,
		oi.list_price,
		oi.discount
	FROM sales.orders AS o
	INNER JOIN sales.order_items AS oi
	ON o.order_id = oi.order_id
	WHERE YEAR(o.shipped_date) BETWEEN 2016 AND 2017 AND o.shipped_date IS NOT NULL
),
total_net_sales_current_year AS (
	SELECT 
		order_data.ship_year,
		order_data.ship_month,
	    SUM(order_data.quantity * order_data.list_price * (1 - order_data.discount)) AS total_net_sales_current_year
	FROM order_data
	GROUP BY order_data.ship_year, order_data.ship_month
),
total_net_sales_last_year AS (
	SELECT
		ship_year,
		ship_month,
		total_net_sales_current_year,
		LAG(total_net_sales_current_year) OVER (PARTITION BY ship_month ORDER BY ship_year) AS total_net_sales_last_year
	FROM total_net_sales_current_year
),
total_net_sales_change AS (
	SELECT
		ship_year,
		ship_month,
		total_net_sales_current_year,
		total_net_sales_last_year,
		(total_net_sales_current_year - total_net_sales_last_year) AS total_net_sales_change
	FROM total_net_sales_last_year
),
total_net_sales_percentage_change AS (
	SELECT
		ship_year,
		ship_month,
		total_net_sales_current_year,
		total_net_sales_last_year,
		total_net_sales_change,
	    (total_net_sales_change / total_net_sales_last_year) * 100 AS total_net_sales_percentage_change
	FROM total_net_sales_change
)
SELECT
	ship_year,
	ship_month,
	total_net_sales_current_year,
	total_net_sales_last_year,
	total_net_sales_change,
	ROUND(total_net_sales_percentage_change, 2) AS total_net_sales_percentage_change
FROM total_net_sales_percentage_change
WHERE ship_year = 2017;

ORDERS SEASONALITY TRENDS

DataFrameas

df11

variable

WITH order_data AS (
	SELECT 
		DATEPART(YEAR, o.shipped_date) AS ship_year,
	    DATEPART(MONTH, o.shipped_date) AS ship_month,
		o.order_id
	FROM sales.orders AS o
	INNER JOIN sales.order_items AS oi
	ON o.order_id = oi.order_id
	WHERE YEAR(o.shipped_date) BETWEEN 2016 AND 2017 AND o.shipped_date IS NOT NULL
),
total_orders AS (
	SELECT 
		order_data.ship_year,
		order_data.ship_month,
		CAST(COUNT(DISTINCT order_data.order_id) AS DECIMAL) AS total_orders
	FROM order_data
	GROUP BY order_data.ship_year, order_data.ship_month
),
overall_orders AS (
	SELECT
		ship_year,
		ship_month,
		total_orders,
		SUM(total_orders) OVER (PARTITION BY ship_year ORDER BY ship_year) AS overall_orders
	FROM total_orders
),
orders_percentage_of_total AS (
	SELECT 
		ship_year,
		ship_month,
		total_orders,
		overall_orders,
		(total_orders / overall_orders) * 100 AS orders_percentage_of_total
	FROM overall_orders
),
orders_percent_of_total_last_year AS (
	SELECT
		ship_year,
		ship_month,
		total_orders,
		overall_orders,
		orders_percentage_of_total,
		LAG(orders_percentage_of_total) OVER (PARTITION BY ship_month ORDER BY ship_year) AS orders_percent_of_total_last_year
	FROM orders_percentage_of_total
),
orders_percentage_change AS (
	SELECT
		ship_year,
		ship_month,
		total_orders,
		overall_orders,
		orders_percentage_of_total,
		orders_percent_of_total_last_year,
	    CASE 
		   WHEN orders_percent_of_total_last_year IS NOT NULL THEN (orders_percentage_of_total - orders_percent_of_total_last_year) 	
	       ELSE NULL
           END AS orders_percentage_change
	FROM orders_percent_of_total_last_year
)
SELECT
	ship_year,
	ship_month,
	total_orders,
	overall_orders,
	ROUND(orders_percentage_of_total,2) AS orders_percentage_of_total,
	ROUND(orders_percent_of_total_last_year,2) AS orders_percent_of_total_last_year,
	ROUND(orders_percentage_change,2) AS orders_percentage_change
FROM orders_percentage_change
WHERE ship_year = 2017;

QUANTITY SEASONAILITY TRENDS

DataFrameas

df14

variable

WITH order_data AS (
	SELECT 
		DATEPART(YEAR, o.shipped_date) AS ship_year,
	    DATEPART(MONTH, o.shipped_date) AS ship_month,
		oi.quantity
	FROM sales.orders AS o
	INNER JOIN sales.order_items AS oi
	ON o.order_id = oi.order_id
	WHERE YEAR(o.shipped_date) BETWEEN 2016 AND 2017 AND o.shipped_date IS NOT NULL
),
total_quantity AS (
	SELECT 
		order_data.ship_year,
		order_data.ship_month,
		CAST(SUM(order_data.quantity) AS DECIMAL) AS total_quantity
	FROM order_data
	GROUP BY order_data.ship_year, order_data.ship_month
),
overall_quantity AS (
	SELECT
		ship_year,
		ship_month,
		total_quantity,
		SUM(total_quantity) OVER (PARTITION BY ship_year ORDER BY ship_year) AS overall_quantity
	FROM total_quantity
),
quantity_percentage_of_total AS (
	SELECT 
		ship_year,
		ship_month,
		total_quantity,
		overall_quantity,
		(total_quantity / overall_quantity) * 100 AS quantity_percentage_of_total
	FROM overall_quantity
),
quantity_percent_of_total_last_year AS (
	SELECT
		ship_year,
		ship_month,
		total_quantity,
		overall_quantity,
		quantity_percentage_of_total,
		LAG(quantity_percentage_of_total) OVER (PARTITION BY ship_month ORDER BY ship_year) AS quantity_percent_of_total_last_year
	FROM quantity_percentage_of_total
),
quantity_percentage_change AS (
	SELECT
		ship_year,
		ship_month,
		total_quantity,
		overall_quantity,
		quantity_percentage_of_total,
		quantity_percent_of_total_last_year,
	    CASE 
		   WHEN quantity_percent_of_total_last_year IS NOT NULL THEN (quantity_percentage_of_total - quantity_percent_of_total_last_year) 	
	       ELSE NULL
           END AS quantity_percentage_change
	FROM quantity_percent_of_total_last_year
)
SELECT
	ship_year,
	ship_month,
	total_quantity,
	overall_quantity,
	ROUND(quantity_percentage_of_total,2) AS quantity_percentage_of_total,
	ROUND(quantity_percent_of_total_last_year,2) AS quantity_percent_of_total_last_year,
	ROUND(quantity_percentage_change,2) AS quantity_percentage_change
FROM quantity_percentage_change
WHERE ship_year = 2017;

SALES SEASONALITY TRENDS

DataFrameas

df12

variable

WITH order_data AS (
	SELECT 
		DATEPART(YEAR, o.shipped_date) AS ship_year,
	    DATEPART(MONTH, o.shipped_date) AS ship_month,
		oi.quantity,
		oi.list_price,
		oi.discount
	FROM sales.orders AS o
	INNER JOIN sales.order_items AS oi
	ON o.order_id = oi.order_id
	WHERE YEAR(o.shipped_date) BETWEEN 2016 AND 2017 AND o.shipped_date IS NOT NULL
),
total_net_sales AS (
	SELECT 
		order_data.ship_year,
		order_data.ship_month,
	 	SUM(order_data.quantity * order_data.list_price * (1 - order_data.discount)) AS total_net_sales
	FROM order_data
	GROUP BY order_data.ship_year, order_data.ship_month
),
overall_net_sales AS (
	SELECT
		ship_year,
		ship_month,
		total_net_sales,
		SUM(total_net_sales) OVER (PARTITION BY ship_year ORDER BY ship_year) AS overall_net_sales
	FROM total_net_sales
),
net_sales_percentage_of_total AS (
	SELECT 
		ship_year,
		ship_month,
		total_net_sales,
		overall_net_sales,
		(total_net_sales / overall_net_sales) * 100 AS net_sales_percentage_of_total
	FROM overall_net_sales
),
net_sales_percent_of_total_last_year AS (
	SELECT
		ship_year,
		ship_month,
		total_net_sales,
		overall_net_sales,
		net_sales_percentage_of_total,
		LAG(net_sales_percentage_of_total) OVER (PARTITION BY ship_month ORDER BY ship_year) AS net_sales_percent_of_total_last_year
	FROM net_sales_percentage_of_total
),
net_sales_percentage_change AS (
	SELECT
		ship_year,
		ship_month,
		total_net_sales,
		overall_net_sales,
		net_sales_percentage_of_total,
	    net_sales_percent_of_total_last_year,
		CASE 
		   WHEN net_sales_percent_of_total_last_year IS NOT NULL THEN (net_sales_percentage_of_total - net_sales_percent_of_total_last_year) 	
	       ELSE NULL
           END AS net_sales_percentage_change
	FROM net_sales_percent_of_total_last_year
)
SELECT
	ship_year,
	ship_month,
	total_net_sales,
	overall_net_sales,
	net_sales_percentage_of_total,
	net_sales_percent_of_total_last_year,
	net_sales_percentage_change
FROM net_sales_percentage_change
WHERE ship_year =  2017;

SALES BY CATEGORY 2017

‌
‌
‌