Daily Route View

BigQuery View: Daily Route Summary

This view aggregates orders by delivery date and route, giving a daily snapshot of each route's activity, assigned driver, vehicle, and total orders delivered.

How it works

The query is built in 2 steps:

1. Deduplicated orders — ROW_NUMBER() partitioned by orderId + deliveryDate, ordered by _modified DESC, keeps only the most recent version of each order per delivery date. Orders modified after their delivery date are excluded.

2. Deduplicated routes — ROW_NUMBER() partitioned by routeId, ordered by _modified DESC, keeps only the most recent version of each route.

The final SELECT joins both with businesses, contracts, vehicles, and staff, then groups by date and route fields to produce a totalOrders count per route per day.

Query

SELECT
    DATE(o.deliveryDate) AS deliveryDate,
    route.name,
    route.routeId,
    route.routeType,
    b.name AS businessName,
    con.name AS contractName,
    v.licensePlate,
    staff.name AS driverName,
    route.totalRouteDistance,
    route.startedRouteDate,
    route.endedRouteDate,
    COUNT(o.orderId) AS totalOrders
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY orderId, deliveryDate ORDER BY _modified DESC) AS rn
    FROM `dingoo-fleet-prod.mdb_sync.orders`
    WHERE DATE(_modified) <= DATE(deliveryDate)
) AS o
LEFT JOIN (
    SELECT routeId, vehicle, driver, routeType, totalRouteDistance, name, startedRouteDate, endedRouteDate,
           ROW_NUMBER() OVER (PARTITION BY routeId ORDER BY _modified DESC) AS rn
    FROM `dingoo-fleet-prod.mdb_sync.routes`
) AS route
    ON o.route = route.routeId AND route.rn = 1
LEFT JOIN `dingoo-fleet-prod.mdb_sync.businesses` AS b
    ON o.business = b.businessId
LEFT JOIN `dingoo-fleet-prod.mdb_sync.contracts` AS con
    ON o.contract = con.contractId
LEFT JOIN `dingoo-fleet-prod.mdb_sync.vehicles` AS v
    ON route.vehicle = v.vehicleId
LEFT JOIN (
    SELECT staffId, name,
           ROW_NUMBER() OVER (PARTITION BY staffId ORDER BY _modified DESC) AS rn
    FROM `dingoo-fleet-prod.mdb_sync.staffs`
) AS staff
    ON route.driver = staff.staffId AND staff.rn = 1
WHERE o.rn = 1
GROUP BY 1,2,3,4,5,6,7,8,9,10,11

Source tables

Table	Description
dingoo-fleet-prod.mdb_sync.orders	Orders synced from MongoDB via triggers
dingoo-fleet-prod.mdb_sync.routes	Route records with vehicle and driver
dingoo-fleet-prod.mdb_sync.businesses	Business records
dingoo-fleet-prod.mdb_sync.contracts	Contract records
dingoo-fleet-prod.mdb_sync.vehicles	Vehicle records with licence plate
dingoo-fleet-prod.mdb_sync.staffs	Staff/driver records

Key fields

Field	Source	Description
deliveryDate	orders.deliveryDate	Delivery date (DATE, truncated from timestamp)
name	routes.name	Route name
routeId	routes.routeId	Route identifier
routeType	routes.routeType	Route type
businessName	businesses.name	Business name
contractName	contracts.name	Contract name
licensePlate	vehicles.licensePlate	Vehicle licence plate
driverName	staffs.name	Driver name (latest version)
totalRouteDistance	routes.totalRouteDistance	Total distance of the route
startedRouteDate	routes.startedRouteDate	Timestamp when the route started
endedRouteDate	routes.endedRouteDate	Timestamp when the route ended
totalOrders	computed	Number of orders assigned to the route on that delivery date

Deduplication logic

Since BigQuery receives every insert/update from MongoDB triggers (append-only), the same order or route can have multiple rows. Two separate ROW_NUMBER() window functions handle this:

• Orders — partitioned by orderId + deliveryDate, ordered by _modified DESC. The filter DATE(_modified) <= DATE(deliveryDate) excludes modifications that happened after the delivery date.
• Routes — partitioned by routeId, ordered by _modified DESC, keeping only the latest state of each route regardless of date.