Advanced: 06 Sector traffic map.sql

-- CDR activity summarized per map polygon without multiplying event rows.
-- QGIS polygon layer. Unique key: coverage_zone_id; geometry: coverage_area.
CREATE OR REPLACE VIEW public.v_sector_traffic_map AS
WITH call_stats AS
(
    SELECT
        sector_id,
        count(*) AS call_count,
        coalesce(sum(duration_seconds), 0) AS call_seconds,
        max(event_start_time) AS last_call_at
    FROM public.usage_cdr_calls
    WHERE sector_id IS NOT NULL
    GROUP BY sector_id
),
sms_stats AS
(
    SELECT
        sector_id,
        count(*) AS sms_count,
        max(event_time) AS last_sms_at
    FROM public.usage_cdr_sms
    WHERE sector_id IS NOT NULL
    GROUP BY sector_id
),
data_stats AS
(
    SELECT
        sector_id,
        count(*) AS data_session_count,
        coalesce(sum(data_used_mb), 0) AS data_used_mb,
        max(session_start) AS last_data_at
    FROM public.usage_cdr_data
    WHERE sector_id IS NOT NULL
    GROUP BY sector_id
)
SELECT
    coverage.coverage_zone_id,
    coverage.site_id,
    coverage.site_code,
    coverage.site_name,
    coverage.region,
    coverage.tower_id,
    coverage.tower_code,
    coverage.sector_id,
    coverage.sector_label,
    coverage.technology_name,
    coverage.generation,
    coalesce(calls.call_count, 0) AS call_count,
    coalesce(calls.call_seconds, 0) AS call_seconds,
    coalesce(messages.sms_count, 0) AS sms_count,
    coalesce(data_usage.data_session_count, 0) AS data_session_count,
    coalesce(data_usage.data_used_mb, 0) AS data_used_mb,
    coalesce(calls.call_count, 0)
      + coalesce(messages.sms_count, 0)
      + coalesce(data_usage.data_session_count, 0) AS total_events,
    greatest(calls.last_call_at, messages.last_sms_at, data_usage.last_data_at)
        AS last_event_at,
    coverage.coverage_area
FROM public.v_network_coverage_map coverage
LEFT JOIN call_stats calls ON calls.sector_id = coverage.sector_id
LEFT JOIN sms_stats messages ON messages.sector_id = coverage.sector_id
LEFT JOIN data_stats data_usage ON data_usage.sector_id = coverage.sector_id;