Can you write a complex SQL query involving window functions and multiple joins to identify top-performing product categories?
Use the provided order, product, category, line-item, and return data. Consider completed orders from the current and prior comparison periods, subtract returned quantities, and return the three highest-ranked categories, including ties.
Output
- One row per qualifying category with
category_name, sales_rank, current_net_sales, prior_net_sales, sales_growth_pct, latest_month, latest_month_sales, previous_observed_month_sales, and month_over_month_change
- Include categories with current-period sales only, ordered by rank ascending and category name ascending
Output details
- Use current dates from
2025-01-01 inclusive through 2025-07-01 exclusive, and prior dates from 2024-01-01 inclusive through 2025-01-01 exclusive.
- Treat missing return quantities as
0, and never allow returned quantities to reduce a line's net quantity below 0.
- Include only categories whose current-period net sales are greater than
0.
- Round
sales_growth_pct to two decimal places; return it as NULL when prior net sales are zero or absent.
- Treat missing prior net sales as
0, and treat missing previous observed-month sales as 0 for month_over_month_change.
- Define
latest_month and previous_observed_month_sales using the latest and immediately preceding months containing sales, not necessarily consecutive calendar months.
