Customer360 - Aggregate Brand-Payer Claims Data for 6-Month & 12-Month Cohort Views at Site & Parent Levels (CHILD & PARENT)

Introduction: To enhance payer-level visibility in Customer360, we must aggregate claims data across provider sites and parent health networks for brand-payer analysis. The goal is to create rolling 6-month (C6M) and rolling 12-month (C12M) summarized views for each brand, payer method, benefit type, and market. This enables commercial access teams and payer strategy partners to identify payer performance, network patterns, and potential reimbursement challenges across healthcare systems.

 

Requirements: Develop the SQL logic, Read claims data from c360_customer_brand_payer_account_claims_reporting_us.Aggregate claims & total claims for Time buckets, C6M using M3–M8, C12M using M3–M14, Customer Levels CHILD (Site-Level), PARENT (Entity-Level), For each segment, compute Start & end date of rolling window, Sum of claims, Sum of total claims, Include only rows where market_name is not null, claims != 0 OR total_claims != 0. Output View Name c360_customer_brand_payer_account_claims_reporting_us_agg_vw

 

Final Output: Show the result.

 

Unity Catalog: “c360_customer_brand_payer_account_claims_reporting_us“