---
# System prepended metadata

title: dddd

---

WITH shipment_counts AS (
    SELECT 
        CONCAT(pickup_state, '-', dropoff_state) AS lane,
        EXTRACT(MONTH FROM date_time) AS calendar_month,
        COUNT(*) AS monthly_shipments
    FROM convoy
    WHERE date_time BETWEEN '2020-01-01' AND '2020-04-30'
    GROUP BY pickup_state, dropoff_state, calendar_month
),
running_totals AS (
    SELECT 
        lane,
        calendar_month,
        monthly_shipments,
        SUM(monthly_shipments) OVER (
            PARTITION BY lane 
            ORDER BY calendar_month 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS shipments_to_date
    FROM shipment_counts
)
SELECT * 
FROM running_totals
ORDER BY lane, calendar_month;



-----------------------------------------------------------------------
Explanation:
shipment_counts CTE:

Extracts lane by concatenating pickup_state and dropoff_state.
Extracts calendar_month from date_time.
Filters data from January to April 2020.
Groups by lane and calendar_month and calculates monthly_shipments.
running_totals CTE:

Computes the rolling sum (shipments_to_date) using the window function SUM().
Uses PARTITION BY lane to calculate the cumulative sum separately for each lane.
Orders by calendar_month to ensure correct running totals.
Final Selection & Ordering:

Retrieves the required columns and sorts by lane and calendar_month.
This will return the shipment count per lane and month, along with the rolling sum up to each month. 🚀 Let me know if you need modifications!
































