Create Reports Using SQL

WITH recent_events AS (
    SELECT
        f.epochEventDateTime,
        f.epochWorkflowProcessedTimestamp,
        f.correlationIdentifier,
        f.eventName,
        f.packagingItemCode,
        f.packagingItemCodeType,
        f.lotNumber,
        f.serialNumberState,
        f.itemState,
        f.locationIdentifierValue,
        f.locationIdentifierType,
        f.locationAddress.businessName AS eventBusinessName,
        f.shipToLocation.businessIdentifiers AS shipToBusinessIdentifiers,
        f.shipToLocation.businessName AS shipToBusinessName,
        f.serialNumberInformationList
    FROM ttlm_lakehouse.lakeHouseSerializationEvent f
    WHERE f.epochEventDateTime >= DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH)
      AND f.epochEventDateTime < DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY)
),
ranked_serial_events AS (
    SELECT
        sn.serial_info.serialNumber AS serialNumber,
        e.eventName,
        e.packagingItemCode,
        e.packagingItemCodeType,
        e.lotNumber,
        e.serialNumberState,
        e.itemState,
        e.locationIdentifierValue,
        e.locationIdentifierType,
        e.eventBusinessName,
        e.shipToBusinessIdentifiers,
        e.shipToBusinessName,
        ROW_NUMBER() OVER (
            PARTITION BY sn.serial_info.serialNumber
            ORDER BY
                e.epochEventDateTime DESC,
                e.epochWorkflowProcessedTimestamp DESC,
                e.correlationIdentifier DESC
        ) AS event_rank
    FROM recent_events e,
         UNNEST(e.serialNumberInformationList) AS sn(serial_info)
),
location_identifiers AS (
    SELECT
        r.serialNumber,
        r.locationIdentifierValue,
        r.locationIdentifierType,
        r.eventBusinessName AS businessName,
        r.packagingItemCode,
        r.packagingItemCodeType,
        r.lotNumber,
        r.serialNumberState,
        r.itemState
    FROM ranked_serial_events r
    WHERE r.event_rank = 1
      AND r.eventName NOT IN ('SHIPMENT', 'RECEIPT')

    UNION ALL

    SELECT
        r.serialNumber,
        identifier.identifierValue AS locationIdentifierValue,
        identifier.locationIdentifierType AS locationIdentifierType,
        r.shipToBusinessName AS businessName,
        r.packagingItemCode,
        r.packagingItemCodeType,
        r.lotNumber,
        r.serialNumberState,
        r.itemState
    FROM ranked_serial_events r,
         UNNEST(r.shipToBusinessIdentifiers) AS ids(identifier)
    WHERE r.event_rank = 1
      AND r.eventName IN ('SHIPMENT', 'RECEIPT')
)
SELECT
    locationIdentifierValue,
    locationIdentifierType,
    businessName,
    packagingItemCode,
    packagingItemCodeType,
    lotNumber,
    serialNumberState,
    itemState,
    COUNT(DISTINCT serialNumber) AS serialNumberCount
FROM location_identifiers
GROUP BY
    locationIdentifierValue,
    locationIdentifierType,
    businessName,
    packagingItemCode,
    packagingItemCodeType,
    lotNumber,
    serialNumberState,
    itemState
ORDER BY
    businessName,
    locationIdentifierType,
    locationIdentifierValue,
    packagingItemCode,
    packagingItemCodeType,
    lotNumber,
    serialNumberState,
    itemState;