| 1 | create view public.v_customer_roaming_activity
|
|---|
| 2 | (customer_id, customer_name, email, account_number, subscription_number, plan_name, contract_number,
|
|---|
| 3 | roaming_country, usage_type, event_time, call_duration_minutes, data_used_gb, direction, charge_amount)
|
|---|
| 4 | as
|
|---|
| 5 | SELECT c.customer_id,
|
|---|
| 6 | COALESCE((c.first_name || ' '::text) || c.last_name, c.company_name) AS customer_name,
|
|---|
| 7 | c.email,
|
|---|
| 8 | a.account_number,
|
|---|
| 9 | s.subscription_number,
|
|---|
| 10 | p.plan_name,
|
|---|
| 11 | con.contract_number,
|
|---|
| 12 | rp.country AS roaming_country,
|
|---|
| 13 | 'Call'::text AS usage_type,
|
|---|
| 14 | cdr.event_start_time AS event_time,
|
|---|
| 15 | round(cdr.duration_seconds::numeric / 60.0, 2) AS call_duration_minutes,
|
|---|
| 16 | NULL::numeric AS data_used_gb,
|
|---|
| 17 | cdr.direction,
|
|---|
| 18 | cdr.charge_amount
|
|---|
| 19 | FROM customers c
|
|---|
| 20 | JOIN accounts a ON a.customer_id = c.customer_id
|
|---|
| 21 | JOIN subscriptions s ON s.account_id = a.account_id
|
|---|
| 22 | JOIN plans p ON p.plan_id = s.plan_id
|
|---|
| 23 | LEFT JOIN contracts con ON con.contract_id = s.contract_id
|
|---|
| 24 | JOIN usage_cdr_calls cdr ON cdr.subscription_id = s.subscription_id
|
|---|
| 25 | JOIN roaming_partners rp ON rp.roaming_partner_id = cdr.roaming_partner_id
|
|---|
| 26 | UNION ALL
|
|---|
| 27 | SELECT c.customer_id,
|
|---|
| 28 | COALESCE((c.first_name || ' '::text) || c.last_name, c.company_name) AS customer_name,
|
|---|
| 29 | c.email,
|
|---|
| 30 | a.account_number,
|
|---|
| 31 | s.subscription_number,
|
|---|
| 32 | p.plan_name,
|
|---|
| 33 | con.contract_number,
|
|---|
| 34 | rp.country AS roaming_country,
|
|---|
| 35 | 'SMS'::text AS usage_type,
|
|---|
| 36 | sms.event_time,
|
|---|
| 37 | NULL::numeric AS call_duration_minutes,
|
|---|
| 38 | NULL::numeric AS data_used_gb,
|
|---|
| 39 | sms.direction,
|
|---|
| 40 | sms.charge_amount
|
|---|
| 41 | FROM customers c
|
|---|
| 42 | JOIN accounts a ON a.customer_id = c.customer_id
|
|---|
| 43 | JOIN subscriptions s ON s.account_id = a.account_id
|
|---|
| 44 | JOIN plans p ON p.plan_id = s.plan_id
|
|---|
| 45 | LEFT JOIN contracts con ON con.contract_id = s.contract_id
|
|---|
| 46 | JOIN usage_cdr_sms sms ON sms.subscription_id = s.subscription_id
|
|---|
| 47 | JOIN roaming_partners rp ON rp.roaming_partner_id = sms.roaming_partner_id
|
|---|
| 48 | UNION ALL
|
|---|
| 49 | SELECT c.customer_id,
|
|---|
| 50 | COALESCE((c.first_name || ' '::text) || c.last_name, c.company_name) AS customer_name,
|
|---|
| 51 | c.email,
|
|---|
| 52 | a.account_number,
|
|---|
| 53 | s.subscription_number,
|
|---|
| 54 | p.plan_name,
|
|---|
| 55 | con.contract_number,
|
|---|
| 56 | rp.country AS roaming_country,
|
|---|
| 57 | 'Data'::text AS usage_type,
|
|---|
| 58 | d.session_start AS event_time,
|
|---|
| 59 | NULL::numeric AS call_duration_minutes,
|
|---|
| 60 | round(d.data_used_mb / 1024.0, 3) AS data_used_gb,
|
|---|
| 61 | NULL::text AS direction,
|
|---|
| 62 | d.charge_amount
|
|---|
| 63 | FROM customers c
|
|---|
| 64 | JOIN accounts a ON a.customer_id = c.customer_id
|
|---|
| 65 | JOIN subscriptions s ON s.account_id = a.account_id
|
|---|
| 66 | JOIN plans p ON p.plan_id = s.plan_id
|
|---|
| 67 | LEFT JOIN contracts con ON con.contract_id = s.contract_id
|
|---|
| 68 | JOIN usage_cdr_data d ON d.subscription_id = s.subscription_id
|
|---|
| 69 | JOIN roaming_partners rp ON rp.roaming_partner_id = d.roaming_partner_id;
|
|---|
| 70 |
|
|---|
| 71 | alter table public.v_customer_roaming_activity
|
|---|
| 72 | owner to postgres;
|
|---|
