Extract a date substring for month-level aggregation using SUBSTR
I need a monthly volume trend using `planned_pickup_date`. Extract the YYYY-MM prefix as `pickup_month`, count shipments as `shipment_count`, sort chronologically. This shows seasonality patterns.
| Column | Type | Key |
|---|---|---|
| shipment_id | INT | PK |
| carrier_id | INT | FK → dim_carriers |
| lane_id | INT | FK → dim_lanes |
| planned_pickup_date | TEXT | |
| actual_pickup_date | TEXT | |
| planned_delivery_date | TEXT | |
| actual_delivery_date | TEXT | |
| qty_ordered | INT | |
| qty_shipped | INT | |
| weight_lbs | REAL | |
| freight_cost | REAL | |
| fuel_surcharge | REAL | |
| accessorial_cost | REAL | |
| dwell_hours_at_origin | REAL | |
| status | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Dates are stored as TEXT in 'YYYY-MM-DD'. You can slice the year-month prefix from the string with a substring function.
GROUP BY the extracted month string, then count rows per group.
ORDER BY pickup_month ASC for chronological order.