Customer Dashboard

Postgres SQL Query for the Delta customer dashboard

CREATE VIEW dashboard.ranked_orders_view AS
WITH OrderDateOccurrence AS (
    SELECT
        o."orderId" AS orderId,
        COUNT(DISTINCT o."orderDate") AS NumberOfDistinctDates
    FROM
        dashboard.orders AS o
    GROUP BY
        o."orderId"
),
RankedOrders AS (
    SELECT
        o."orderId" AS orderId,
        o."optimizerOrderId" AS optimizerOrderId,
        o."orderDate" AS orderDate,
        o."business" AS businessId,
        o."contract" AS contractId,
        c.name AS customerName,
        con.name AS contractName,
        con.type AS contractType,
        o.created AS createDate,
        o.modified AS modifyDate,
        u.address AS address,
        u.name AS userName,
        u.longitude AS longitude,
        u.latitude AS latitude,
        o."deliveredDate" AS dd,
        o."notDeliveredReason" AS notDeliveredReason,
        o.status AS status,
        o."attachments" AS attachments,
        o."boxes" AS boxes,
        o."bag" AS bag,
        o."totalWeight" AS totalWeight,
        o."notes" as notes
        CARDINALITY(o."boxes") as nbBoxes,

        ROW_NUMBER() OVER (PARTITION BY o."orderId", o."orderDate" ORDER BY o.modified DESC) AS rn
    FROM
        dashboard.orders AS o
    LEFT OUTER JOIN dashboard.businesses AS c
        ON o.business = c."mongoId"
    LEFT OUTER JOIN dashboard.contracts AS con
        ON o.contract = con."mongoId"
    LEFT OUTER JOIN dashboard.users AS u
        ON u."mongoId" = o.user
)
SELECT
    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,

    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;