Untitled

        
SELECT 
    ag.custom_category,
    ag.sum_units,
    ag.sum_dollar,
    ag.sum_base_units,
    ag.sum_base_dollar,
    wd.*,
    '2024-06-30' as event_date
FROM (
    SELECT 
        sc.smm_cleaned,
        sc.week,
        sc.custom_category,
        SUM(sc.units) AS sum_units,
        SUM(sc.dollar) AS sum_dollar,
        SUM(sc.base_units) AS sum_base_units,
        SUM(sc.base_dollar) AS sum_base_dollar
    FROM (
        SELECT 
            sd.smm_cleaned,
            sd.week,
            cc.custom_category,
            sd.units,
            sd.dollar,
            sd.base_units,
            sd.base_dollar
        FROM 
            default.nielsen_sales_fct_partitioned sd
        JOIN 
            custom_category cc 
        ON 
            sd.department = cc.department AND 
            sd.super_category = cc.super_category AND
            sd.category = cc.category AND 
            sd.sub_category = cc.sub_category AND 
            sd.brand = cc.brand
    ) sc
    GROUP BY 
        sc.smm_cleaned,
        sc.week,
        sc.custom_category
) ag
JOIN 
    sagemaker_featurestore.hdat_weekly_1716560073 wd 
ON 
    ag.smm_cleaned = wd.smm_cleaned AND 
    ag.week = wd.week