Write a SQL query to identify the top 10% of performing retailers over the last fiscal quarter, accounting for seasonal sales spikes.
Use the most recently completed fiscal quarter. Treat seasonally adjusted sales as each sale's net sales divided by the seasonal index for its calendar month. Include retailers with no qualifying sales when determining the 10% cutoff.
retailer_id, retailer_name, adjusted_sales, and performance_rankadjusted_sales descending, then retailer_id ascending| Column | Type | Description |
|---|---|---|
| quarter_idPK | INT | Fiscal quarter identifier |
| quarter_name | VARCHAR(20) | Fiscal quarter label |
| start_date | DATE | First date in the fiscal quarter |
| end_date | DATE | Last date in the fiscal quarter |
| is_last_completed | BOOLEAN | Whether this is the most recently completed quarter |
| Column | Type | Description |
|---|---|---|
| retailer_idPK | INT | Retailer identifier |
| retailer_name | VARCHAR(100) | Retailer name |
| region | VARCHAR(50) | Retailer operating region |
| is_active | BOOLEAN | Whether the retailer is currently active |
| Column | Type | Description |
|---|---|---|
| sale_idPK | INT | Sale identifier |
| retailer_id | INT | Retailer associated with the sale |
| sale_date | DATE | Sale date |
| net_sales | DECIMAL(12,2) | Net sales amount after adjustments |
| Column | Type | Description |
|---|---|---|
| calendar_monthPK | INT | Calendar month number |
| seasonal_index | DECIMAL(6,3) | Seasonality multiplier for the month |
| index_source | VARCHAR(50) | Source of the seasonal index |