DatabaseCreation: Customer daily usage summary.sql

create view public.v_customer_daily_usage_summary
            (customer_id, customer_name, email, account_number, subscription_number, plan_name, contract_number,
             usage_date, total_call_minutes, total_sms_count, total_data_gb, total_charge_amount)
as
SELECT c.customer_id,
       COALESCE((c.first_name || ' '::text) || c.last_name, c.company_name) AS customer_name,
       c.email,
       a.account_number,
       s.subscription_number,
       p.plan_name,
       con.contract_number,
       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, 3)                                 AS total_data_gb,
       uad.total_charge_amount
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
         LEFT JOIN contracts con ON con.contract_id = s.contract_id
         JOIN usage_aggregates_daily uad ON uad.subscription_id = s.subscription_id;

alter table public.v_customer_daily_usage_summary
    owner to postgres;