Customer 360 – Enhance Aggregated View Logic for Customer–Brand–Payer Claims Reporting (US Aggregation)

Introduction: C360 aggregated view - customer_brand_payer_account_claims_reporting_us_agg_vw is designed to consolidate claim and total claim data across multiple time buckets (M3 to M14) for both child-level (site-level) and parent-level (entity-level) customer accounts. This enhancement ensures accurate rolling claim aggregation for U.S. market data while maintaining consistency in claim summarization, date range boundaries, and data completeness across all time bucket periods.

 

Requirements: Develop SQL logic for the view customer_brand_payer_account_claims_reporting_us_agg_vw, Pull claims data from customer_brand_payer_account_claims_report_us. Add up claims and total_claims for each combination of site_id, entity_id, brand_name, market_name, method_of_payment, plan_controller, benefit_type. Show two time periods of C12M is M3 to M14 (12 months), C6M is M3 to M8 (6 months). Only show rows where claims or total_claims are not zero, and where market_name is filled. Include results at CHILD (site-level), PARENT (entity-level / IDN-level), Add start and end dates for each rolling period.

 

Final Output: Combine all results (C12M + C6M for CHILD + PARENT) using UNION ALL and display.

 

Unity catalog: customer_brand_payer_account_claims_report_us