Catalog Windowing and Royalty Allocation SQL Assessment

Role: Senior Content Performance Analyst & Royalties Database Specialist with 10+ years modeling media rights, windowing logic, and digital distributor royalties.

Context

• Content Studio / Distributor: {{studio_name}}
• Database Engine: {{target_sql_dialect}}
• Catalog Metadata Schema: {{catalog_metadata_schema}}
• Windowing & Rights Rules: {{windowing_contract_rules}}
• Consumption Metric: {{consumption_metric_type}}
• Licensing Partner List: {{licensing_partner_list}}

Task

Produce a rigorous analysis of the SQL models responsible for evaluating catalog performance, windowing compliance, and royalty revenue share distribution for {{studio_name}}, identifying calculation errors and query bottlenecks.

Method

1. Review the entity-relationship joins between {{catalog_metadata_schema}} and consumption logs for title-level ID collision.
2. Verify contract effective date overlaps against {{windowing_contract_rules}} to detect accidental multi-exclusive territory allocations.
3. Audit SQL window functions calculating {{consumption_metric_type}} across regional tier splits for license partners in {{licensing_partner_list}}.
4. Check NULL handling in revenue share calculation formulas during mid-month licensing tier transitions.
5. Inspect aggregation performance on high-volume asset stream history tables under {{target_sql_dialect}}.
6. Validate tier-capped royalty thresholds using recursive CTEs or analytical ranking functions.
7. Formulate audit test queries that highlight content viewed outside authorized release windows.

Constraints

• MUST ensure SQL logic strictly enforces rights window start and end boundaries without date truncation leakage.
• MUST NOT aggregate metrics at the studio level before applying partner-specific licensing minimums.
• All query refactorings must be fully compliant with {{target_sql_dialect}} syntax.
• Any edge cases where metadata is missing must be handled defensively without silent row drops.

Output format

Format the deliverable into four sequential sections:

1. Rights Logic & SQL Architecture Findings (200-300 words)
2. Royalty Calculation Vulnerabilities (table: Contract Rule, Query Logic Flaw, Royalty Distortion Risk)
3. Refactored Royalty Allocation SQL Model (a complete, production-ready CTE-based script)
4. Catalog Analytics Validation Checklist (5-7 actionable technical testing steps)

Self-review

• Confirm all boundary date joins in {{windowing_contract_rules}} avoid double-counting active windows.
• Verify syntax and analytical functions run natively in {{target_sql_dialect}}.
• Ensure all licensing partners in {{licensing_partner_list}} are represented in the validation model.