DatabaseCreation: Customer data usage history.sql

create view public.v_customer_data_usage_history
            (customer_id, customer_name, email, account_number, subscription_number, plan_name, contract_number,
             contract_status, data_cdr_id, session_start, session_end, session_duration_minutes, data_used_mb,
             data_used_gb, apn, ip_address, charge_amount, is_roaming, network_country, sector_label, frequency_band,
             network_generation)
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,
       con.status                                                           AS contract_status,
       d.data_cdr_id,
       d.session_start,
       d.session_end,
       round(EXTRACT(epoch FROM d.session_end - d.session_start) / 60.0, 2) AS session_duration_minutes,
       d.data_used_mb,
       round(d.data_used_mb / 1024.0, 4)                                    AS data_used_gb,
       d.apn,
       d.ip_address,
       d.charge_amount,
       CASE
           WHEN d.roaming_partner_id IS NOT NULL THEN true
           ELSE false
           END                                                              AS is_roaming,
       COALESCE(rp.country, 'Home'::text)                                   AS network_country,
       ts.sector_label,
       ts.frequency_band,
       nt.generation                                                        AS network_generation
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_cdr_data d ON d.subscription_id = s.subscription_id
         LEFT JOIN roaming_partners rp ON rp.roaming_partner_id = d.roaming_partner_id
         LEFT JOIN tower_sectors ts ON ts.sector_id = d.sector_id
         LEFT JOIN network_technologies nt ON nt.technology_id = ts.technology_id;

alter table public.v_customer_data_usage_history
    owner to postgres;