How would you design a SQL query with multiple joins to build a carrier pricing dashboard at Uber Freight?
Write a PostgreSQL query that summarizes valid carrier quotes for delivered loads during Q1 2025. Include fuel adjustments when available, carrier acceptance rates, monthly lane-level price rankings, and each carrier's prior monthly adjusted average where applicable.
carrier_name, origin, destination, pricing_month, quote_count, accepted_quote_count, acceptance_rate, average_adjusted_rate, price_rank, prior_month_average_adjusted_rate.| Column | Type | Description |
|---|---|---|
| carrier_idPK | INT | Unique carrier identifier |
| carrier_name | VARCHAR(100) | Carrier display name |
| is_active | BOOLEAN | Whether the carrier is active |
| Column | Type | Description |
|---|---|---|
| lane_idPK | INT | Unique freight lane identifier |
| origin | VARCHAR(80) | Origin market |
| destination | VARCHAR(80) | Destination market |
| Column | Type | Description |
|---|---|---|
| load_idPK | INT | Unique load identifier |
| lane_id | INT | Referenced freight lane |
| pickup_date | DATE | Scheduled pickup date |
| load_status | VARCHAR(20) | Operational load status |
| Column | Type | Description |
|---|---|---|
| quote_idPK | INT | Unique quote identifier |
| load_id | INT | Referenced load |
| carrier_id | INT | Quoted carrier |
| quoted_rate | NUMERIC(12,2) | Carrier quoted linehaul rate |
| quote_status | VARCHAR(20) | Quote decision status |
| Column | Type | Description |
|---|---|---|
| effective_monthPK | DATE | First day of effective month |
| surcharge_pct | NUMERIC(6,4) | Fuel surcharge as a decimal percentage |