Embedded Files
This is a full SQL project on a supply chain company dedicated to fulfilling customer orders. In this project, the customers segment, orders, categories & shipment styles of this company is critically analysed with insights and necessary recommendations.
This is a full SQL project on a supply chain company dedicated to fulfilling customer orders. In this project, the customers segment, orders, categories & shipment styles of this company is critically analysed with insights and necessary recommendations.
INSIGHTS - CUSTOMER ANALYSIS
Average Sales Per Customer Segment
SELECT c."customer segment",
Round(Avg(o."sales")) AS "avg segment sales"
FROM customers c
right join orders o
ON c."order id" = o."order id"
GROUP BY c."customer segment"
2. Late Deliveries Per Customer Segment
SELECT Count(o."order id") AS "total orders",
Count(CASE
WHEN s."late_delivery_risk" = 1 THEN s."late_delivery_risk"
END) AS count_ldr_1,
c."customer segment",
Round(Avg(s."late_delivery_risk"), 3) AS "late_delivery_risk"
FROM customers c
right join shipment s
ON c."order id" = s."order id"
right join orders o
ON c."order id" = o."order id"
GROUP BY c."customer segment"
3. Late Deliveries Per Customer Segment & Market
SELECT o."market",
c."customer segment",
Count(o."order id"),
Count(CASE
WHEN s."late_delivery_risk" = 1 THEN s."late_delivery_risk"
END) AS count_ldr_1,
Round(Avg(s."late_delivery_risk"), 3)
FROM customers c
right join shipment s
ON c."order id" = s."order id"
right join orders o
ON c."order id" = o."order id"
GROUP BY c."customer segment",
O."market"
ORDER BY count_ldr_1 DESC
INSIGHTS - PRODUCT CATEGORIES ANALYSIS
Highest & Lowest Categories
WITH ranked_categories
AS (SELECT c."category name",
c."orders",
Rank()
over (
ORDER BY "orders" DESC) AS rank_orders_desc,
Rank()
over (
ORDER BY "orders" ASC) AS rank_orders_asc
FROM categories c)
SELECT "category name",
"orders"
FROM ranked_categories
WHERE rank_orders_desc = 1
OR rank_orders_asc = 1;
2. Highest & Lowest Categories In Order Volume Brackets
WITH cat_group
AS (SELECT c."category name",
c."orders",
CASE
WHEN c."orders" > 10000 THEN 'High-Volume'
WHEN c."orders" > 1000 THEN 'Mid-Volume'
ELSE 'Regular-Volume'
END AS "Order Volume"
FROM categories c),
high_ranked_categories
AS (SELECT "category name",
"order volume",
Row_number()
over(
PARTITION BY "order volume"
ORDER BY "orders" DESC) AS category_rank,
"orders"
FROM cat_group),
low_ranked_categories
AS (SELECT "category name",
"order volume",
Row_number()
over(
PARTITION BY "order volume"
ORDER BY "orders") AS category_rank,
"orders"
FROM cat_group) SELECT "category name",
"order volume",
"orders"
FROM high_ranked_categories
WHERE category_rank = 1
UNION ALL
SELECT "category name",
"order volume",
"orders"
FROM low_ranked_categories
WHERE category_rank = 1
ORDER BY "order volume",
"orders" DESC
3. Categories & Prefered Their Shipping Mode
WITH categories_cte
AS (SELECT c."category name",
s."shipping mode",
Count(s."order id") AS "orders count"
FROM categories c
right join orders o
ON c."category id" = o."category id"
right join shipment s
ON o."order id" = s."order id"
GROUP BY c."category name",
s."shipping mode"),
category_preferred_shipping
AS (SELECT "category name",
"shipping mode",
Row_number()
over(
PARTITION BY "category name"
ORDER BY "orders count" DESC) AS preferred_category_rank,
"orders count"
FROM categories_cte)
SELECT *
FROM category_preferred_shipping
WHERE preferred_category_rank = 1
OR preferred_category_rank = 2
Results capped.
INSIGHTS - SHIPMENT ANALYSIS
Real vs Scheduled Shipping Days
SELECT Avg("days for shipment (scheduled)") AS "scheduled days",
Avg("days for shipping (real)") AS "real days"
FROM shipment s
GROUP BY "days for shipment (scheduled)"
2. Actual Shipping Dates & Related Late Delivery Risks
SELECT "days for shipping (real)",
"days for shipment (scheduled)",
"late_delivery_risk",
"delivery status"
FROM shipment s
WHERE "delivery status" = 'Late delivery'
AND "late_delivery_risk" > 0
Results Capped
3. Late Deliveries Per Year, Month & Week
SELECT Count("late_delivery_risk") AS
count_ldr,
To_char("shipping date (dateorders)", 'YYYY') AS
shipping_year,
Extract(month FROM Cast("shipping date (dateorders)" AS DATE)) AS
shipping_month,
Extract(week FROM Cast("shipping date (dateorders)" AS DATE)) AS
shipping_week
FROM shipment s
WHERE "late_delivery_risk" > 0
GROUP BY shipping_year,
shipping_month,
shipping_week
ORDER BY count_ldr DESC
4. Delivery Status vs Dates
WITH order_states
AS (SELECT To_char("shipping date (dateorders)", 'YYYY')
AS
shipping_year,
Extract(month FROM Cast("shipping date (dateorders)" AS DATE))
AS
shipping_month,
Extract(week FROM Cast("shipping date (dateorders)" AS DATE))
AS
shipping_week,
SUM(CASE
WHEN "delivery status" = 'Advance shipping' THEN 1
ELSE 0
END)
AS
advanced_cnt,
SUM(CASE
WHEN "delivery status" = 'Late delivery' THEN 1
ELSE 0
END)
AS
late_cnt,
SUM(CASE
WHEN "delivery status" = 'Shipping on time' THEN 1
ELSE 0
END)
AS
on_time_cnt,
SUM(CASE
WHEN "delivery status" = 'Shipping canceled' THEN 1
ELSE 0
END)
AS
canceled_cnt
FROM shipment s
GROUP BY shipping_year,
shipping_month,
shipping_week)
SELECT shipping_year,
shipping_month,
shipping_week,
advanced_cnt,
late_cnt,
on_time_cnt,
canceled_cnt
FROM order_states
ORDER BY shipping_year,
shipping_month,
shipping_week
Results Capped
Page updated
Google Sites
Report abuse