Data Analytics Projects - SQL, Excel, Tableau, Python & Power BI Interview Resources
29. Find Monthly Revenue Growth
WITH monthly_sales AS (
SELECT
DATE_TRUNC('month', o.order_date) AS month,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY DATE_TRUNC('month', o.order_date)
)
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS previous_month_revenue,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month), 2) AS growth_percentage
FROM monthly_sales;
30. Find the Highest Value Order for Each Customer
WITH order_values AS (
SELECT
o.customer_id,
o.order_id,
SUM(oi.quantity * oi.unit_price) AS order_value
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.customer_id, o.order_id
)
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY order_value DESC
) AS rn
FROM order_values
) t
WHERE rn = 1;
Window Functions Covered
• Ranking: ROW_NUMBER(), RANK(), DENSE_RANK()
• Navigation: LAG(), LEAD()
• Aggregates: SUM() OVER()
• Analytics: Running Totals, Revenue Contribution, Month-over-Month Growth
💡 Double Tap ❤️ For More
514 ·