Design a dimensional star schema to track global device telemetry and recurring subscription lifecycle events at Apple.
Represent the design with device and subscription dimensions plus a combined event fact table. Write a query that summarizes January 2025 telemetry for subscriptions whose latest lifecycle status is available by the end of that month.
Output
- One row per region, device type, and subscription tier
- Columns:
region, device_type, subscription_tier, active_subscriptions, telemetry_event_count, and avg_cpu_percent
- Include canceled subscriptions when telemetry exists, with zero active subscriptions
- Sort by
region, device_type, and subscription_tier ascending
