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 AS orderId,
        COUNT(DISTINCT o.orderDate) AS NumberOfDistinctDates
    FROM
        `dingoo-prod.mdb_trigger.orders` AS o
    GROUP BY
        o.orderId
),
RankedOrders AS (
    SELECT
        o.mongoId as mongoId,
        o.orderId AS orderId,
        o.optimizerOrderId AS optimizerOrderId,
        o.orderDate AS orderDate,
        o.business AS businessId,
        o.contract AS contractId,
        o.lot as sector,
        c.name AS customerName,
        con.name AS contractName,
        con.type AS contractType,
        o._created AS createDate,
        o.deleted as deleted,
        o._modified AS modifyDate,
        o.orderAddress.address AS address,
        u.name AS userName,
        u.addresses[SAFE_OFFSET(0)].location.lng AS longitude,
        u.addresses[SAFE_OFFSET(0)].location.lat AS latitude,
        o.orderFlows[SAFE_OFFSET(0)].deliveredDate AS dd,
        o.orderFlows[SAFE_OFFSET(0)].notDeliveredReason AS notDeliveredReason,
        o.orderFlows[SAFE_OFFSET(0)].status AS status,
        o.attachments[SAFE_OFFSET(0)].url AS attachments,
        CONCAT(
            '{',
            ARRAY_TO_STRING(
                ARRAY(
                SELECT JSON_VALUE(box, '$.id')
                FROM UNNEST(
                    JSON_QUERY_ARRAY(o.orderFlows[SAFE_OFFSET(0)].boxes)
                ) AS box
                WHERE JSON_VALUE(box, '$.id') IS NOT NULL
                ),
                ','
            ),
            '}'
            ) AS boxes,
        o.orderFlows[SAFE_OFFSET(0)].bagId AS bag,
        o.totalWeight AS totalWeight,
        o.notes as notes,
        ARRAY_LENGTH(
        JSON_QUERY_ARRAY(o.orderFlows[SAFE_OFFSET(0)].boxes)
        ) AS nbBoxes,
        o.orderFlows[SAFE_OFFSET(0)].deliveryDate as deliveryDate,
        ROW_NUMBER() OVER (
            PARTITION BY o.orderId, o.orderFlows[SAFE_OFFSET(0)].deliveryDate
            ORDER BY o._modified DESC
        ) AS rn
    FROM
        `dingoo-prod.mdb_trigger.orders` AS o
    LEFT OUTER JOIN `dingoo-prod.mdb_trigger.businesses_latest` AS c
        ON o.business = c.mongoId
    LEFT OUTER JOIN `dingoo-prod.mdb_trigger.contracts_latest` AS con
        ON o.contract = con.mongoId
    LEFT OUTER JOIN `dingoo-prod.platform_mdb.users` AS u
        ON u.mongoId = o.user
    WHERE
        DATE(o._modified) <= DATE(o.orderFlows[SAFE_OFFSET(0)].deliveryDate)
)
SELECT
    ro.mongoId,
    ro.attachments,
    ro.orderId,
    ro.optimizerOrderId,
    ro.orderDate,
    ro.businessId,
    ro.contractId,
    ro.customerName,
    ro.contractName,
    ro.contractType,
    ro.createDate,
    ro.modifyDate,
    ro.status,
    ro.latitude,
    ro.longitude,
    ro.address,
    ro.userName,
    ro.dd,
    ro.notDeliveredReason,
    ro.totalWeight,
    ro.bag,
    ro.boxes,
    ro.nbBoxes,
    ro.notes,
    ro.deliveryDate,
    ro.sector,
    ro.deleted,

    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-prod.mdb_trigger.orders	Orders synced from MongoDB via triggers
dingoo-prod.mdb_trigger.businesses_latest	Latest version of each business
dingoo-prod.mdb_trigger.contracts_latest	Latest version of each contract
dingoo-prod.platform_mdb.users	Users with addresses and coordinates

Key fields

Field	Source	Description
mongoId	orders	MongoDB document ID
status	orderFlows[0]	Current order status
dd	orderFlows[0].deliveredDate	Actual delivery date
deliveryDate	orderFlows[0].deliveryDate	Planned delivery date
boxes	orderFlows[0].boxes	Box IDs concatenated as {id1,id2,...}
nbBoxes	orderFlows[0].boxes	Number of boxes
bag	orderFlows[0].bagId	Bag identifier
sector	orders.lot	Delivery sector/lot
RedeliveredStatus	computed	'redelivered' if the order has multiple distinct dates, 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.