Customer Dashboard

BigQuery View: Ranked Orders for Reporting

This query creates the main reporting view used by the customer dashboard. It deduplicates orders and enriches them with business, contract, and user data.

How it works

The query is built in 3 steps:

1. OrderDateOccurrence — Counts how many distinct orderDate values exist per orderId. This is used later to flag orders that have been rescheduled (redelivered).

2. RankedOrders — Joins orders with businesses, contracts, and users, then assigns a row number (rn) partitioned by orderId + deliveryDate, ordered by _modified DESC. This keeps only the most recent version of each order per delivery date. It also filters out rows where _modified is after deliveryDate to exclude late updates.

3. Final SELECT — Picks only rn = 1 (latest version per order/delivery date) and adds the RedeliveredStatus flag: if an order has more than one distinct date, it is marked as 'redelivered'.

Query

WITH OrderDateOccurrence AS (
    SELECT
        o.orderId,
        COUNT(DISTINCT o.orderDate) AS NumberOfDistinctDates
    FROM
        `dingoo-fleet-prod.mdb_sync.orders` AS o
    GROUP BY
        o.orderId
),
RankedOrders AS (
    SELECT
        o.dingooOrderId,
        o.orderId,
        o.optimizerOrderId,
        o.orderDate,
        o.business AS businessId,
        o.contract AS contractId,
        o.lot AS sector,
        b.name AS businessName,
        con.name AS contractName,
        con.contractType AS contractType,
        DATETIME(o._created, 'Europe/Lisbon') AS createDate,
        o.deleted AS deleted,
        DATETIME(o._modified, 'Europe/Lisbon') AS modifyDate,
        o.orderAddress.address AS address,
        u.name AS endCustomerName,
        ST_X(u.location) AS longitude,
        ST_Y(u.location) AS latitude,
        DATETIME(o.deliveredDate,'Europe/Lisbon') AS deliveredDate,
        o.notDeliveredReason,
        o.status,
        o.attachments[SAFE_OFFSET(0)].url AS attachments,
        o.previousStatus AS lastState,
        staff.name AS driverName,
        route.routeType AS routeType,
        route.routeId AS routeId,
        route.totalRouteDistance AS totalRouteDistance,
        v.licensePlate,
        CONCAT(
            '{',
            ARRAY_TO_STRING(
                ARRAY(
                    SELECT JSON_VALUE(box, '$.id')
                    FROM UNNEST(JSON_QUERY_ARRAY(o.boxes)) AS box
                    WHERE JSON_VALUE(box, '$.id') IS NOT NULL
                ),
                ','
            ),
            '}'
        ) AS boxes,
        o.bagId AS bag,
        o.totalWeight AS totalWeight,
        o.notes AS notes,
        ARRAY_LENGTH(JSON_QUERY_ARRAY(o.boxes)) AS numberOfBoxes,
        o.deliveryDate AS deliveryDate,
        ROW_NUMBER() OVER (
            PARTITION BY o.orderId, o.deliveryDate
            ORDER BY o._modified DESC
        ) AS rn,
        ROW_NUMBER() OVER (
            PARTITION BY o.route
            ORDER BY o._modified DESC
        ) AS routeRn
    FROM `dingoo-fleet-prod.mdb_sync.orders` AS o
    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 (
        SELECT endCustomerId, name, location,
               ROW_NUMBER() OVER (PARTITION BY endCustomerId ORDER BY _modified DESC) AS rn
        FROM `dingoo-fleet-prod.mdb_sync.users`
    ) AS u
        ON u.endCustomerId = o.endCustomer AND u.rn = 1
    LEFT JOIN (
        SELECT routeId, vehicle, driver, routeType, totalRouteDistance,
               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.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 DATE(o._modified) <= DATE(o.deliveryDate)
)
SELECT
    ro.lastState,
    ro.dingooOrderId,
    ro.attachments,
    ro.orderId,
    ro.optimizerOrderId,
    ro.orderDate,
    ro.businessId,
    ro.contractId,
    ro.driverName,
    ro.licensePlate,
    ro.businessName,
    ro.contractName,
    ro.contractType,
    ro.createDate,
    ro.modifyDate,
    ro.status,
    ro.latitude,
    ro.longitude,
    ro.address,
    ro.endCustomerName,
    ro.deliveredDate,
    ro.notDeliveredReason,
    ro.totalWeight,
    ro.bag,
    ro.boxes,
    ro.numberOfBoxes,
    ro.notes,
    ro.deliveryDate,
    ro.sector,
    ro.routeType,
    ro.routeId,
    ro.totalRouteDistance,
    ro.deleted,
    ro.routeRn,
    CASE
        WHEN odo.NumberOfDistinctDates > 1 THEN 'redelivered'
        ELSE NULL
    END AS RedeliveredStatus
FROM RankedOrders ro
JOIN OrderDateOccurrence odo ON ro.orderId = odo.orderId
WHERE ro.rn = 1;

Source tables

Table	Description
`dingoo-fleet-prod.mdb_sync.orders`	Orders synced from MongoDB via triggers
`dingoo-fleet-prod.mdb_sync.businesses`	Business records
`dingoo-fleet-prod.mdb_sync.contracts`	Contract records
`dingoo-fleet-prod.mdb_sync.users`	End customers with addresses and coordinates
`dingoo-fleet-prod.mdb_sync.routes`	Route records with vehicle and driver
`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
`orderId`	orders.orderId	Main order identifier
`dingooOrderId`	orders.dingooOrderId	Internal Dingoo order ID
`optimizerOrderId`	orders.optimizerOrderId	Optimizer order ID
`status`	orders.status	Current order status
`lastState`	orders.previousStatus	Status before the last state change
`deliveredDate`	orders.deliveredDate	Actual delivery date (converted to `Europe/Lisbon`)
`deliveryDate`	orders.deliveryDate	Planned delivery date
`createDate`	orders._created	Order creation timestamp (converted to `Europe/Lisbon`)
`modifyDate`	orders._modified	Last modification timestamp (converted to `Europe/Lisbon`)
`boxes`	orders.boxes	Box IDs concatenated as `{id1,id2,...}` (extracted from JSON array)
`numberOfBoxes`	orders.boxes	Number of boxes (`ARRAY_LENGTH` of the boxes JSON array)
`bag`	orders.bagId	Bag identifier
`sector`	orders.lot	Delivery sector/lot
`address`	orders.orderAddress	Delivery address
`latitude`	users.location	End customer latitude (`ST_Y`)
`longitude`	users.location	End customer longitude (`ST_X`)
`endCustomerName`	users.name	End customer name (latest version)
`businessName`	businesses.name	Business name
`contractName`	contracts.name	Contract name
`contractType`	contracts.contractType	Contract type
`driverName`	staffs.name	Driver name (latest version)
`licensePlate`	vehicles.licensePlate	Vehicle licence plate
`routeId`	routes.routeId	Route identifier
`routeType`	routes.routeType	Route type
`totalRouteDistance`	routes.totalRouteDistance	Total route distance
`routeRn`	computed	Row number over route ordered by `_modified DESC` (1 = most recent route state)
`attachments`	orders.attachments	URL of the first attachment
`RedeliveredStatus`	computed	`'redelivered'` if the order has more than one distinct date, `NULL` otherwise

Deduplication logic

Since BigQuery receives every insert/update from MongoDB triggers (append-only), the same order can have multiple rows. The ROW_NUMBER() window function keeps only the most recent version (_modified DESC) per orderId + deliveryDate pair. The filter DATE(o._modified) <= DATE(deliveryDate) excludes modifications that happened after the delivery date.