DatabaseCreation: 05 Customer daily usage summary.sql

DROP VIEW IF EXISTS public.v_customer_daily_usage_summary;

CREATE VIEW public.v_customer_daily_usage_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,
    uad.usage_date,
    ROUND(uad.total_call_seconds::numeric / 60.0, 2) AS total_call_minutes,
    uad.total_sms_count,
    ROUND(uad.total_data_mb / 1024.0, 4) AS total_data_gb,
    uad.total_charge_amount,
    SUM(uad.total_data_mb) OVER (
        PARTITION BY s.subscription_id, date_trunc('month', uad.usage_date::timestamp)
        ORDER BY uad.usage_date
    ) AS month_to_date_data_mb
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;