-- Author: [email protected]
-- Purpose: Flags any ct_product_id whose max fiscal_month is behind the overall max.
-- A product lagging behind means its financial feed is stale or broken.
 
{{
    config(severity='warn')
}}
 
-- Determine the maximum fiscal month available per product id
WITH PRODUCT_FRESHNESS AS (
    SELECT
        CT_PRODUCT_ID
        , MAX(FISCAL_MONTH) AS MAX_FISCAL_MONTH
    FROM {{ ref('bgs_financial_monthly_fact') }}
    GROUP BY CT_PRODUCT_ID
)
 
SELECT
    CT_PRODUCT_ID
    , MAX_FISCAL_MONTH
    , MAX(MAX_FISCAL_MONTH) OVER () AS MAX_FISCAL_MONTH_OVERALL
FROM PRODUCT_FRESHNESS
QUALIFY MAX_FISCAL_MONTH <> MAX(MAX_FISCAL_MONTH) OVER ()