DatabaseCreation: 13 Customer usage 360 summary.sql

DROP VIEW IF EXISTS public.v_customer_usage_360_summary;

CREATE VIEW public.v_customer_usage_360_summary AS
SELECT
    c.customer_id,
    CASE WHEN c.customer_type = 'business' THEN c.company_name ELSE c.first_name || ' ' || c.last_name END AS customer_name,
    a.account_number,
    s.subscription_id,
    s.subscription_number,
    p.plan_name,
    MIN(uad.usage_date) AS first_usage_date,
    MAX(uad.usage_date) AS last_usage_date,
    COUNT(*) AS active_usage_days,
    ROUND(SUM(uad.total_call_seconds)::numeric / 60.0, 2) AS total_call_minutes,
    SUM(uad.total_sms_count) AS total_sms,
    ROUND(SUM(uad.total_data_mb) / 1024.0, 2) AS total_data_gb,
    ROUND(AVG(uad.total_data_mb) / 1024.0, 4) AS average_daily_data_gb,
    SUM(uad.total_charge_amount) AS total_usage_charges,
    DENSE_RANK() OVER (ORDER BY SUM(uad.total_data_mb) DESC) AS data_usage_rank
FROM public.customers c
JOIN public.accounts a ON a.customer_id = c.customer_id
JOIN public.subscriptions s ON s.account_id = a.account_id
JOIN public.plans p ON p.plan_id = s.plan_id
JOIN public.usage_aggregates_daily uad ON uad.subscription_id = s.subscription_id
GROUP BY c.customer_id, c.customer_type, c.company_name, c.first_name, c.last_name,
         a.account_number, s.subscription_id, s.subscription_number, p.plan_name;