Holistics-postgres public.customers campaigns agreements link table materialized view
DISPLAYTITLE:Holistics-postgres public.customers_campaigns_agreements_link_table materialized view
Query
WITH cp AS (
SELECT cp.id AS campaign_id,
prj.customer_id
FROM wp_appq_evd_campaign cp
LEFT JOIN wp_appq_project prj ON cp.project_id = prj.id
), agr AS (
SELECT a.id AS agreement_id,
cta.cp_id,
a.customer_id
FROM finance_agreements a
LEFT JOIN finance_campaign_to_agreement cta ON a.id = cta.agreement_id
)
SELECT cp.campaign_id,
agr.agreement_id,
CASE
WHEN agr.customer_id IS NULL THEN cp.customer_id
ELSE agr.customer_id
END AS customer_id
FROM cp
FULL JOIN agr ON cp.campaign_id = agr.cp_id
UNION ALL
SELECT NULL::integer AS campaign_id,
NULL::integer AS agreement_id,
cst.id AS customer_id
FROM wp_appq_customer cst
WHERE NOT (cst.id IN ( SELECT DISTINCT wp_appq_project.customer_id
FROM wp_appq_project)) AND NOT (cst.id IN ( SELECT DISTINCT finance_agreements.customer_id
FROM finance_agreements));
Keys and Indexes
create index linkt_campaignid on customers_campaigns_agreements_link_table (campaign_id);
create index linkt_customerid on customers_campaigns_agreements_link_table (customer_id);
create index linkt_agreementid on customers_campaigns_agreements_link_table (agreement_id);