WITH n_users_per_customer AS (
SELECT wp_appq_user_to_customer.customer_id,
count(wp_appq_user_to_customer.wp_user_id) AS n_users
FROM wp_appq_user_to_customer
GROUP BY wp_appq_user_to_customer.customer_id
), latest_campaign_per_customer AS (
SELECT rk.customer_id,
rk.campaign_id AS latest_campaign_id,
rk.campaign AS latest_campaign,
rk.start_date AS latest_campaign_start_date,
rk.end_date AS latest_campaign_end_date,
rk.close_date AS latest_campaign_close_date
FROM ( SELECT pj.customer_id,
cp.id AS campaign_id,
cp.title AS campaign,
cp.start_date,
cp.end_date,
cp.close_date,
rank() OVER (PARTITION BY pj.customer_id ORDER BY cp.start_date DESC, cp.id DESC) AS cp_start_rank
FROM wp_appq_evd_campaign cp
LEFT JOIN wp_appq_project pj ON cp.project_id = pj.id) rk
WHERE rk.cp_start_rank = 1
), projects_and_campaigns_per_customer AS (
SELECT pj.customer_id,
count(DISTINCT pj.id) AS n_projects,
count(cp.id) AS n_campaigns
FROM wp_appq_evd_campaign cp
LEFT JOIN wp_appq_project pj ON cp.project_id = pj.id
GROUP BY pj.customer_id
), latest_agreement_per_customer AS (
SELECT rk.customer_id,
rk.agreement_id AS latest_agreement_id,
rk.agreement AS latest_agreement,
rk.agreement_date AS latest_agreement_start_date,
rk.tokens AS latest_agreement_tokens,
rk.token_unit_price AS latest_agreement_token_unit_price
FROM ( SELECT finance_agreements.customer_id,
finance_agreements.id AS agreement_id,
finance_agreements.title AS agreement,
finance_agreements.agreement_date,
finance_agreements.tokens,
finance_agreements.token_unit_price,
rank() OVER (PARTITION BY finance_agreements.customer_id ORDER BY finance_agreements.agreement_date DESC, finance_agreements.id DESC) AS ag_date_rank
FROM finance_agreements) rk
WHERE rk.ag_date_rank = 1
), agreements_per_customer AS (
SELECT finance_agreements.customer_id,
count(finance_agreements.id) AS n_agreements,
sum(finance_agreements.tokens) AS aggregated_tokens,
avg(finance_agreements.token_unit_price) AS average_token_unit_price,
sum(finance_agreements.tokens * finance_agreements.token_unit_price) AS aggregated_agreements_amount
FROM finance_agreements
GROUP BY finance_agreements.customer_id
)
SELECT cs.id,
cs.company AS name,
cs.country,
cs.pm_id,
concat(p.surname, ' ', p.name) AS pm,
COALESCE(nupcs.n_users, 0::bigint) AS n_users,
COALESCE(pjcpcs.n_projects, 0::bigint) AS n_projects,
COALESCE(pjcpcs.n_campaigns, 0::bigint) AS n_campaigns,
lcpcs.latest_campaign_id,
lcpcs.latest_campaign,
lcpcs.latest_campaign_start_date,
lcpcs.latest_campaign_end_date,
lcpcs.latest_campaign_close_date,
COALESCE(agpcs.n_agreements, 0::bigint) AS n_agreements,
agpcs.aggregated_tokens,
agpcs.average_token_unit_price,
agpcs.aggregated_agreements_amount,
lagpcs.latest_agreement_id,
lagpcs.latest_agreement,
lagpcs.latest_agreement_start_date,
lagpcs.latest_agreement_tokens,
lagpcs.latest_agreement_token_unit_price
FROM wp_appq_customer cs
LEFT JOIN wp_appq_evd_profile p ON cs.pm_id = p.id
LEFT JOIN n_users_per_customer nupcs ON cs.id = nupcs.customer_id
LEFT JOIN projects_and_campaigns_per_customer pjcpcs ON cs.id = pjcpcs.customer_id
LEFT JOIN latest_campaign_per_customer lcpcs ON cs.id = lcpcs.customer_id
LEFT JOIN agreements_per_customer agpcs ON cs.id = agpcs.customer_id
LEFT JOIN latest_agreement_per_customer lagpcs ON cs.id = lagpcs.customer_id;