HCLTech's retail analytics team needs a quarterly product leaderboard for executive reporting. Write a PostgreSQL query that evaluates product sales during the previous completed calendar quarter relative to the current date.
Calculate total units sold and total revenue for each product, associate every product with its category, and assign a position within that category. Revenue is calculated as quantity * unit_price for each order item.
Output
- One row per qualifying product, with columns
category_name, product_name, total_units_sold, total_revenue, and revenue_rank.
- Include only products with sales in the previous completed calendar quarter.
- Return no more than three products per category.
- Sort by category name alphabetically, then leaderboard position ascending.
- Within each category, order by total revenue descending, total units sold descending, and product name ascending to resolve ties.
