Holistics-postgres public.financial agreements materialized view
DISPLAYTITLE:Holistics-postgres public.financial_agreements materialized view
Query
--cs.country AS customer_country,
WITH campaign_data AS (
SELECT cta.agreement_id,
array_agg(cp.id::text) AS related_camapaign_ids_list,
count(cp.id) AS n_related_campaigns,
sum(cp.tokens_usage) AS tokens_spent
FROM finance_campaign_to_agreement cta
LEFT JOIN wp_appq_evd_campaign cp ON cta.cp_id = cp.id
WHERE cta.agreement_id IS NOT NULL
GROUP BY cta.agreement_id
)
SELECT ag.customer_id,
cs.company AS customer,
cs.pm_id AS customer_pm_id,
concat(p.surname, ' ', p.name) AS customer_pm,
ag.id,
ag.title AS name,
ag.agreement_date AS start_date,
ag.agreement_close_date AS close_date,
ag.created_time AS creation_datetime,
ag.modification_time AS last_edit_datetime,
ag.last_editor_id AS last_editor_id,
concat(e.surname, ' ', e.name) AS last_editor,
ag.is_token_based,
ag.tokens,
ag.token_unit_price,
ag.token_unit_price * ag.tokens AS agreement_total_amount,
COALESCE(cd.tokens_spent, 0::double precision) AS tokens_spent,
ag.tokens - COALESCE(cd.tokens_spent, 0::double precision) AS remaining_tokens,
COALESCE(cd.n_related_campaigns, 0::bigint) AS n_related_campaigns,
cd.related_camapaign_ids_list AS related_camapaign_ids_list,
ag.additional_note AS agreement_additional_note
FROM finance_agreements ag
LEFT JOIN wp_appq_customer cs ON cs.id = ag.customer_id
LEFT JOIN wp_appq_evd_profile p ON p.id = cs.pm_id
LEFT JOIN wp_appq_evd_profile e ON e.id = ag.last_editor_id
LEFT JOIN campaign_data cd ON ag.id = cd.agreement_id;
Keys and Indexes
create unique index financialagreements_uniqueindex_id on financial_agreements (id);