Check for reseller threshold breach on Spanner

reseller_purchase_breach.sql

SELECT		
    p.ref_type,		
    p.ref_code,		
    p.sku,		
    rt.id_product_fulltype,		
    rt.sku_category,		
    COALESCE(ANY_VALUE(rt.threshold), rp.default_threshold) AS threshold,		
    SUM(item_status) AS purchased_count		
FROM reseller.purchase_history p
LEFT JOIN reseller.reseller_sku_category rsc ON rsc.sku = p.value_code AND 'sku' = p.value_type
LEFT JOIN ref.reseller_threshold rt	
    ON 'id_product_fulltype' = rt.value_type
    AND rsc.id_product_fulltype = rt.value_code	
    AND rsc.sku_category = rt.sku_category
    AND p.ref_type = rt.ref_type		
    AND p.country_code = rt.country_code		
    AND p.mp_code = rt.mp_code		
LEFT JOIN ref.reseller_ref rr ON rt.ref_type = rr.ref_type		
WHERE (p.ref_code, p.ref_type, p.country_code, p.mp_code) IN (('8cab6a8e-7d59-401e-8a04-b61f1491b1ca', 'customer_code', 'ae', 'noon'), ...)		
AND (rt.id_product_fulltype, rt.sku_category) IN ((:id_product_fulltype, :sku_category), ...)		
AND rt.is_active		
AND rr.is_active		
GROUP BY 1,2,3,4,5