How can we rank months by revenue?
Encapsulating monthly revenue in a CTE and applying RANK() OVER (ORDER BY revenue DESC) ranks peak revenue months.
Seasonality peak detection, highest grossing month league tables, financial performance scoring.
WITH monthly_revenue AS (
SELECT
EXTRACT(YEAR FROM order_date) AS year,
EXTRACT(MONTH FROM order_date) AS month,
SUM(amount) AS revenue
FROM orders
GROUP BY
EXTRACT(YEAR FROM order_date),
EXTRACT(MONTH FROM order_date)
)
SELECT
year,
month,
revenue,
RANK() OVER (ORDER BY revenue DESC) AS revenue_rank
FROM monthly_revenue;Practice typing production-grade SQL code for Revenue Ranking.