Hi guys, I have this PostgreSQL query, can you guys help me running this on alteryx?
I have all the conections working 100%. An make some more simple querys, but this one is not working.
with salesforce AS (
select
sc.billingaccountnumber__c AS account_number,
sd.value__c AS sf_disconnect_reason,
sd.createddate
from
ods.sfamero_servicecontract AS sc
inner join ods.sfamero_order AS so
on sc.id = so.servicecontract__c
and sc.billingaccountnumber__c is not null
and sc.servicecountrynoshield__c = 'Brazil'
and so.servicecountrynoshield__c = 'Brazil'
and so.type = 'Disconnect'
left join ods.sfamero_interaction__c AS sd
on sd.account__c = so.accountid
and sd.currencyisocode = 'BRL'
and sd.reasondetails__c = 'Disconnect'
and sd.label__c = 'Disconnect Reason'
group by 1, 2, 3
),
customer_contact AS (
SELECT
ira,
partyid,
MAX(CASE WHEN rn_phone <= 6 AND phonenumber IS NOT NULL THEN phonenumber END) AS phonenumber,
MAX(CASE WHEN rn_email <= 5 AND email IS NOT NULL THEN email END) AS email,
MAX(CASE WHEN rn_name <= 6 AND name IS NOT NULL THEN name END) AS name
FROM (
SELECT
relnId AS ira,
partyid,
phonenumber,
email,
name,
ROW_NUMBER() OVER (PARTITION BY relnId ORDER BY CASE
WHEN labelname IN ('Invoice', 'WhatsApp', 'Primary', 'Secondary', 'MobileNumber', 'Shipping') AND phonenumber IS NOT NULL THEN 1
END) AS rn_phone,
ROW_NUMBER() OVER (PARTITION BY relnId ORDER BY CASE
WHEN labelname IN ('Invoice', 'Primary', 'Secondary', 'MobileNumber', 'Shipping') AND email IS NOT NULL THEN 1
END) AS rn_email,
ROW_NUMBER() OVER (PARTITION BY relnId ORDER BY CASE
WHEN labelname IN ('Invoice', 'Primary', 'Secondary', 'WhatsApp', 'MobileNumber', 'Shipping') AND name IS NOT NULL THEN 1
END) AS rn_name
FROM
bep_ods.irao_customer_contact
WHERE
groups_groupname = 'BrazilResidential'
AND relnId IS NOT NULL
) x
GROUP BY 1, 2
), documents AS (
select
partyid,
externalids_value as taxid,
row_number() over(partition by partyid ORDER BY version DESC, publishdate DESC) as rn
from
bep_ods.irao_party_externalids
where
externalids_typename = 'brazil_tin'
), cpf_contact AS (
select
c.ira as relnid,
c.email,
c.phonenumber,
c.name,
d.taxid,
d.partyid
from
customer_contact c
left join documents d
on c.partyid = d.partyid
and d.rn = 1
)
select distinct
fsl.rb_account_num,
fsl.relnid,
fsl.partyid,
fsl.current_status,
fsl.svc_agreement_activation_date,
fsl.svc_agreement_disconnect_date,
case
when fsl.current_status <> 'DISCO' then null
when fsl.disconnect_reason_code is not null then fsl.disconnect_reason_code
when sf.sf_disconnect_reason is not null then sf.sf_disconnect_reason
else 'Non Pay'
end as disconnect_reason,
cc.name,
cc.email,
cc.phonenumber,
cc.taxid,
fsl.tenure_days,
fsl.tenure_months,
fsl.market_segment,
fsl.customer_service_city,
fsl.customer_service_state,
fsl.customer_service_zip,
fsl.customer_service_address,
fsl.latitude,
fsl.longitude,
fsl.satellite_name,
fsl.beam_number,
fsl.satellite_name,
fsl.productinstanceid,
fsl.current_package_detail as product_name,
fsl.original_salesagreement_salespolicy_name as sales_channel,
fsl.dealer_id,
fsl.dealer_name
from
rpt.full_subscriber_list_plus_latest_vw fsl
left join salesforce as sf on fsl.rb_account_num = sf.account_number and cast(fsl.svc_agreement_disconnect_date as date) = cast(sf.createddate as date)
left join dw.contact_dim cd on fsl.rb_account_num = cd.rb_account_num
left join cpf_contact cc on fsl.relnid = cc.relnid
where
fsl.customer_service_country = 'Brazil'
and
fsl.market_segment = 'Retail'
and
fsl.current_status in ('ACTIVE', 'DISCO')
