Holistics-postgres public.campaigns materialized view

Query

 WITH campaign_payout AS (
         SELECT wp_appq_payment.campaign_id,
            sum(wp_appq_payment.amount) AS total_payout,
            sum(
                CASE
                    WHEN wp_appq_payment.is_requested = 0 AND wp_appq_payment.is_paid = 0 THEN wp_appq_payment.amount
                    ELSE 0::double precision
                END) AS unclaimed_payout,
            sum(
                CASE
                    WHEN wp_appq_payment.is_requested = 1 AND wp_appq_payment.is_paid = 0 THEN wp_appq_payment.amount
                    ELSE 0::double precision
                END) AS pending_payout,
            sum(
                CASE
                    WHEN wp_appq_payment.is_paid = 1 THEN wp_appq_payment.amount
                    ELSE 0::double precision
                END) AS cashed_payout
           FROM wp_appq_payment
          GROUP BY wp_appq_payment.campaign_id
        ), campaign_people AS (
         SELECT wp_appq_exp_points.campaign_id,
            sum(wp_appq_exp_points.amount) AS generated_experience_points,
            sum(
                CASE
                    WHEN wp_appq_exp_points.activity_id = 1 AND wp_appq_exp_points.amount > 0::double precision THEN 1
                    ELSE NULL::integer
                END) AS n_testers
           FROM wp_appq_exp_points
          GROUP BY wp_appq_exp_points.campaign_id
        ), payable_testers AS (
         SELECT tmp.campaign_id,
            count(*) AS n_payable_testers
           FROM ( SELECT wp_appq_payment.campaign_id,
                    wp_appq_payment.tester_id,
                    sum(wp_appq_payment.amount) AS balance
                   FROM wp_appq_payment
                  GROUP BY wp_appq_payment.campaign_id, wp_appq_payment.tester_id
                 HAVING sum(wp_appq_payment.amount) > 0::double precision) tmp
          GROUP BY tmp.campaign_id
        ), report_data AS (
         SELECT wp_appq_report.campaign_id,
            count(wp_appq_report.id) AS report_count
           FROM wp_appq_report
          GROUP BY wp_appq_report.campaign_id
        )
 SELECT pj.customer_id,
    cs.company AS customer,
    cs.country AS customer_country,
    cta.agreement_id,
    cp.project_id,
    pj.display_name AS project,
    cp.pm_id,
    concat(p.surname, ' ', p.name) AS pm,
    cp.id,
    cp.title AS name,
    cptype.category_id,
    cpcat.name AS category,
    cp.campaign_type_id AS type_id,
    cptype.name AS type,
    cp.class,
    cp.family,
    cp.status_id,
        CASE
            WHEN cp.status_id = 1 THEN 'Open'::text
            WHEN cp.status_id = 2 THEN 'Closed'::text
            ELSE 'Unknown'::text
        END AS status,
    cp.status_details,
    cp.effort,
    cp.ux_effort,
    cp.tokens_usage,
    fa.token_unit_price * cp.tokens_usage as campaign_value,
    rp.report_count,
    COALESCE(cpppl.n_testers, 0::bigint) AS n_testers,
    cpppl.generated_experience_points,
    COALESCE(pt.n_payable_testers, 0::bigint) AS n_payable_testers,
    cppay.total_payout,
    cppay.unclaimed_payout,
    cppay.pending_payout,
    cppay.cashed_payout,
    cp.start_date,
    cp.end_date,
    cp.close_date,
        CASE
            WHEN cp.start_date IS NULL AND cp.end_date IS NULL AND cp.close_date IS NULL THEN NULL::timestamp with time zone
            WHEN cp.start_date IS NOT NULL AND cp.end_date IS NULL AND cp.close_date IS NULL THEN cp.start_date::timestamp with time zone
            WHEN cp.start_date IS NOT NULL AND cp.end_date IS NOT NULL AND cp.close_date IS NULL THEN to_timestamp(round((date_part('epoch'::text, cp.start_date) + date_part('epoch'::text, cp.end_date)) / 2::double precision))
            WHEN cp.start_date IS NOT NULL AND cp.end_date IS NOT NULL AND cp.close_date IS NOT NULL THEN to_timestamp(round((date_part('epoch'::text, cp.start_date) + date_part('epoch'::text, cp.end_date) + date_part('epoch'::text, cp.close_date)) / 3::double precision))
            ELSE NULL::timestamp with time zone
        END AS unique_assigned_date
   FROM wp_appq_evd_campaign cp
     LEFT JOIN wp_appq_evd_profile p ON p.id = cp.pm_id
     LEFT JOIN wp_appq_campaign_type cptype ON cp.campaign_type_id = cptype.id
     LEFT JOIN wp_appq_campaign_category cpcat ON cptype.category_id = cpcat.id
     LEFT JOIN campaign_payout cppay ON cp.id = cppay.campaign_id
     LEFT JOIN campaign_people cpppl ON cp.id = cpppl.campaign_id
     LEFT JOIN payable_testers pt ON cp.id = pt.campaign_id
     LEFT JOIN wp_appq_project pj ON cp.project_id = pj.id
     LEFT JOIN wp_appq_customer cs ON pj.customer_id = cs.id
     LEFT JOIN finance_campaign_to_agreement cta ON cp.id = cta.cp_id
     LEFT JOIN finance_agreements fa ON cta.agreement_id = fa.id
     LEFT JOIN report_data rp ON cp.id = rp.campaign_id;

Keys and Indexes

create unique index campaigns_uniqueindex_id on campaigns (id);

create index campaigns_index_class on campaigns (class);

create index campaigns_index_family on campaigns (family);

create index campaigns_index_type on campaigns (type);

create index campaigns_index_status on campaigns (status);

create index campaigns_index_statusdetails on campaigns (status_details);

create index campaigns_index_pm on campaigns (pm);

create index campaigns_index_customer on campaigns (customer);

create index campaigns_index_startdate on campaigns using brin(start_date);

create index campaigns_index_enddate on campaigns using brin(end_date);

create index campaigns_index_closedate on campaigns using brin(close_date);

create index campaigns_index_uniqueassigneddate on campaigns using brin(unique_assigned_date);

create index campaigns_index_agreement_id on campaigns (agreement_id);

create index campaigns_index_customer_id on campaigns (customer_id); 

create index campaigns_index_project_id on campaigns (project_id); 

create index campaigns_index_project on campaigns (project); 

create index campaigns_index_pm_id on campaigns (pm_id); 

Refreshing policy

Holistics-postgres materialized view refresh jobs