Write a SQL query to compare partner-reported counts with internal counts and flag mismatches.
Use the provided partner reports and internal event records. Count only internal events whose status is counted; treat missing counts as zero. Include dates present on either side.
Output
- One row per partner and date, including unmatched dates.
- Columns:
partner_id, partner_name, comparison_date, reported_count, internal_count, count_difference, mismatch_flag, and previous_mismatch.
count_difference is reported minus internal. Sort by partner_id, then comparison_date.
Output details
- Sort
partner_id and comparison_date in ascending order.
- Retain duplicate partner-report rows for the same partner and date rather than deduplicating them.
- Compute
previous_mismatch from the preceding output row for the same partner_id, including rows sharing the same comparison_date.
- Leave
previous_mismatch as NULL for the first row of each partner.
