Top-Performing Segments Query

Volkswagen Group Italia needs a quarterly view of its strongest product segments across the latest three quarters of available sales data. Write a PostgreSQL query that ranks segments by net revenue and returns the top two segments for each quarter.

Requirements

1. Determine the latest quarter from the available sale_date values and include that quarter plus the two preceding quarters.
2. Join sales to products and product segments, then aggregate net revenue and units by quarter and segment.
3. Rank segments within each quarter by total revenue in descending order and return only ranks 1 and 2.
4. Return results ordered chronologically by quarter and then by revenue rank.

Output details

• Return columns quarter_start, segment_name, total_revenue, total_units, and revenue_rank in that order.
• Use quarter-start dates, ordered chronologically ascending, then order equal-rank rows by segment_name ascending.
• Assign equal revenues the same rank and include every segment whose revenue_rank is at most 2.
• Include sale_date values from six months before the latest quarter start inclusively through three months after it exclusively.
• Represent NULL aggregated revenue as 0.00; leave aggregated units NULL when all contributing unit values are NULL.