with crm_expenditures as (
SELECT
name,
purpose,
coalesce(itemized, 0) + coalesce(non_itemized, 0) as amount,
ar_payer_payee.rpt_id
FROM
ar_payer_payee
left join ar_disbursements_genrl using (payer_payee_id)
WHERE
payer_payee_type = 1002
and (
name LIKE '%salesforce%'
OR name LIKE '%aptify%'
OR name LIKE '%unionware%'
OR name LIKE '%winmill%'
OR name LIKE '%union.dev%'
OR name LIKE '%advanced solutions%'
OR name LIKE '%uniontrack%'
OR name LIKE '%union track%'
OR name LIKE '%paragon corp%'
OR name LIKE '%INTEGRATED COMP%'
OR lower(name) = 'incom'
OR name LIKE '%kmr system%'
OR name LIKE '%strategic organizing system%'
or name like '%john sladkus%'
OR purpose LIKE '%salesforce%'
OR purpose LIKE '%unionware%'
OR purpose LIKE '%aptify%'
OR (
purpose like '%IMIS%'
and purpose not like '%ADIMIS%'
)
OR purpose LIKE '%UNIONTRACK%'
)
UNION ALL
SELECT
description,
null as purpose,
cash_paid,
rpt_id
FROM
ar_disbursements_inv_purchases
WHERE
(
description LIKE '%salesforce%'
OR description LIKE '%aptify%'
OR description LIKE '%unionware%'
OR description LIKE '%winmill%'
OR description LIKE '%union.dev%'
OR description LIKE '%advanced solutions%'
OR description LIKE '%uniontrack%'
OR description LIKE '%union track%'
OR description LIKE '%paragon corp%'
OR description LIKE '%INTEGRATED COMP%'
OR lower(description) = 'incom'
OR description LIKE '%kmr system%'
OR description LIKE '%strategic organizing system%'
or description like '%john sladkus%'
)
),
ranked as (
SELECT
f_num,
members,
name,
purpose,
amount,
row_number() OVER (
PARTITION BY f_num
ORDER BY
pd_covered_from DESC,
amount desc
) AS rnk
from
crm_expenditures
inner join lm_data using (rpt_id)
where
pd_covered_from >= '2010'
AND desig_name = 'LU'
)
SELECT
CASE
WHEN members < 1000 THEN 'small'
WHEN members < 10000 THEN 'medium'
WHEN members >= 10000 THEN 'large'
END as size,
CASE
WHEN purpose like '%salesforce%'
OR name LIKE '%salesforce%' THEN 'SalesForce'
WHEN purpose like '%unionware%'
OR name LIKE '%unionware%' THEN 'UnionWare'
WHEN name LIKE '%winmill%' THEN 'eMembership'
WHEN name LIKE '%union.dev%' THEN 'union.dev'
WHEN purpose like '%IMIS%'
OR name LIKE '%advanced solutions%' THEN 'iMIS'
WHEN purpose like '%uniontrack%'
OR name LIKE '%uniontrack%'
OR name LIKE '%union track%' THEN 'UnionTrack'
WHEN name LIKE '%paragon corp%' THEN 'MemTrack'
WHEN name LIKE '%INTEGRATED COMP%'
OR lower(name) = 'incom' THEN 'MTP'
WHEN name LIKE '%kmr system%' THEN 'KMR Systems'
WHEN name LIKE '%strategic organizing system%'
or name like '%john sladkus%' THEN 'Strategic Organizing System'
WHEN purpose like '%aptify%'
OR name LIKE '%aptify%' THEN 'Aptify'
ELSE NULL
END AS company,
count(*) as cnt,
sum(amount) as spend
FROM
ranked
WHERE
rnk = 1
GROUP BY
size,
company