1.
a. Hourly Sales Trend
SELECT
DATE_TRUNC(timestamp, HOUR) AS hour,
SUM(quantity) AS total_quantity,
SUM(P.unit_price * quantity) AS total_revenue
FROM Fact_Sales_table AS F
INNER JOIN Products AS P ON F.product_id = P.product_id
GROUP BY DATE_TRUNC(F.timestamp, HOUR)
ORDER BY hour ASC;
b. Daily Sales Trend
SELECT
DATE(timestamp) AS date,
SUM(quantity) AS total_quantity,
SUM(P.unit_price * quantity) AS total_revenue
FROM Fact_Sales_table AS F
INNER JOIN Products AS P ON F.product_id = P.product_id
GROUP BY DATE(F.timestamp)
ORDER BY date ASC;
c. Weekly Sales Trend
SELECT
DATE_TRUNC(timestamp, WEEK) AS week,
SUM(quantity) AS total_quantity,
SUM(P.unit_price * quantity) AS total_revenue
FROM Fact_Sales_table AS F
INNER JOIN Products AS P ON F.product_id = P.product_id
GROUP BY DATE_TRUNC(F.timestamp, WEEK)
ORDER BY week ASC;
2.
a. Sales by store location
SELECT
S.location,
SUM(quantity) AS total_quantity,
SUM(P.unit_price * quantity) AS total_revenue
FROM Fact_Sales_table AS F
INNER JOIN Products AS P ON F.product_id = P.product_id
INNER JOIN Stores AS S ON F.store_id = S.store_id
GROUP BY S.location
ORDER BY total_revenue DESC;
b. Sales by region
SELECT
S.city,
SUM(quantity) AS total_quantity,
SUM(P.unit_price * quantity) AS total_revenue
FROM Fact_Sales_table AS F
INNER JOIN Products AS P ON F.product_id = P.product_id
INNER JOIN Stores AS S ON F.store_id = S.store_id
GROUP BY S.city
ORDER BY total_revenue DESC;
3.
WITH product_sales AS (
SELECT
P.product_id,
P.name,
P.category,
SUM(quantity) AS quantity_sold,
SUM(P.unit_price * quantity) AS revenue_generated
FROM Fact_Sales_table AS F
INNER JOIN Products AS P ON F.product_id = P.product_id
GROUP BY P.product_id, P.name, P.category
),
category_sales AS (
SELECT
category,
SUM(quantity_sold) AS category_quantity,
SUM(revenue_generated) AS category_revenue
FROM product_sales
GROUP BY category
SELECT
ps.product_id,
ps.name,
ps.category,
ps.quantity_sold,
ps.revenue_generated,
cs.category_quantity,
cs.category_revenue,
ps.quantity_sold / cs.category_quantity AS category_share_quantity,
ps.revenue_generated / cs.category_revenue AS category_share_revenue,
-- Replace placeholder with your logic for calculating expected sales
expected_sales,
ps.quantity_sold - expected_sales AS quantity_diff,
ps.revenue_generated - (expected_sales * P.unit_price) AS revenue_diff
FROM product_sales AS ps
INNER JOIN category_sales AS cs ON ps.category = cs.category
INNER JOIN Products AS P ON ps.product_id = P.product_id
ORDER BY revenue_generated DESC;
4.
5.
6.
7.
SELECT
S.location,
C.city AS region,
SUM(quantity * P.price) AS total_revenue
FROM Fact_Sales_table AS F
INNER JOIN Products AS P ON F.product_id = P.product_id
INNER JOIN Stores AS S ON F.store_id = S.store_id
INNER JOIN Cities AS C ON S.city = C.city
GROUP BY S.location, C.city
ORDER BY total_revenue DESC;
8.
WITH store_performance AS (
SELECT
S.store_id,
S.location,
SUM(quantity) AS total_quantity_sold,
SUM(quantity * P.price) AS total_revenue
FROM Fact_Sales_table AS F
INNER JOIN Products AS P ON F.product_id = P.product_id
INNER JOIN Stores AS S ON F.store_id = S.store_id
GROUP BY S.store_id, S.location
SELECT
store_id,
location,
RANK() OVER (ORDER BY total_quantity_sold DESC) AS sales_volume_rank,
RANK() OVER (ORDER BY total_revenue DESC) AS revenue_rank
FROM store_performance
ORDER BY sales_volume_rank, revenue_rank;
