DatabaseCreation: Customer full usage activity.sql

create view public.v_customer_usage_360_summary
            (customer_id, customer_name, account_number, subscription_number, plan_name, total_call_seconds,
             total_call_minutes, total_sms, total_data_used_gb, total_usage_charges)
as
SELECT c.customer_id,
       COALESCE((c.first_name || ' '::text) || c.last_name, c.company_name) AS customer_name,
       a.account_number,
       s.subscription_number,
       p.plan_name,
       sum(uad.total_call_seconds)                                          AS total_call_seconds,
       round(sum(uad.total_call_seconds) / 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_used_gb,
       sum(uad.total_charge_amount)                                         AS total_usage_charges
FROM customers c
         JOIN accounts a ON a.customer_id = c.customer_id
         JOIN subscriptions s ON s.account_id = a.account_id
         JOIN plans p ON p.plan_id = s.plan_id
         JOIN usage_aggregates_daily uad ON uad.subscription_id = s.subscription_id
GROUP BY c.customer_id, c.first_name, c.last_name, c.company_name, a.account_number, s.subscription_number, p.plan_name;

alter table public.v_customer_usage_360_summary
    owner to postgres;