Data Analytics Projects - SQL, Excel, Tableau, Python & Power BI Interview Resources
SQL Project Series #4
E-Commerce Sales Analysis – Advanced SQL with Window Functions 🚀
Window functions are widely used by Data Analysts to calculate rankings, running totals, moving averages, and customer insights without losing row-level details.
Business Questions
21. Rank Customers by Total Revenue
WITH customer_revenue AS (
SELECT
o.customer_id,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.customer_id
)
SELECT
customer_id,
revenue,
DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank
FROM customer_revenue;
22. Find the Top Selling Product in Each Category
WITH product_sales AS (
SELECT
p.category,
p.product_name,
SUM(oi.quantity) AS total_sold
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.category, p.product_name
)
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY category ORDER BY total_sold DESC
) AS rn
FROM product_sales
) t
WHERE rn = 1;
23. Calculate Running Revenue by Order Date
WITH daily_sales AS (
SELECT
o.order_date,
SUM(oi.quantity * oi.unit_price) AS daily_revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_date
)
SELECT
order_date,
daily_revenue,
SUM(daily_revenue) OVER (ORDER BY order_date) AS running_revenue
FROM daily_sales;
24. Find the Previous Order Date for Each Customer
SELECT
customer_id,
order_id,
order_date,
LAG(order_date) OVER (
PARTITION BY customer_id ORDER BY order_date
) AS previous_order_date
FROM orders;
25. Find the Next Order Date for Each Customer
SELECT
customer_id,
order_id,
order_date,
LEAD(order_date) OVER (
PARTITION BY customer_id ORDER BY order_date
) AS next_order_date
FROM orders;
26. Calculate Days Between Consecutive Orders
SELECT
customer_id,
order_date,
order_date - LAG(order_date) OVER (
PARTITION BY customer_id ORDER BY order_date
) AS days_between_orders
FROM orders;
Note: For Postgres use order_date - LAG(order_date) OVER(...). For MySQL use DATEDIFF(order_date, LAG(order_date) OVER(...))
27. Find the Top 3 Customers by Revenue
WITH customer_revenue AS (
SELECT
o.customer_id,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.customer_id
)
SELECT *
FROM (
SELECT *,
DENSE_RANK() OVER (ORDER BY revenue DESC) AS rnk
FROM customer_revenue
) t
WHERE rnk <= 3;
28. Find Each Product's Contribution to Total Revenue
WITH product_revenue AS (
SELECT
p.product_name,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_name
)
SELECT
product_name,
revenue,
ROUND(100.0 * revenue / SUM(revenue) OVER (), 2) AS revenue_percentage
FROM product_revenue
ORDER BY revenue DESC;
437 ·