Hi All,
Data is not the same when Duplicate Visual.
How to fix it ?
this is my query.
with dib_salesforce as (
select *
-- join table
row_number() over(partition by af.id order by af.lastmodifieddate desc) idx
from "scbpt_dl_persistent"."sfdc_crm_application_form" as af
left JOIN "scbpt_dl_persistent"."sfdc_crm_ap_payment" as ap
on af.id = ap.Application_Form__c
left join "scbpt_dl_persistent"."sfdc_crm_lead" as l
on af.Lead__c = l.Id
where (l.Status = 'Close Won - Issued Policy' or l.Status = 'Inspection' or l.Status = 'QC' or l.Status = 'Submitted App' or l.Status = 'Waiting for Payment')
and (ap.isParent__c = true) and (l.Campaign_Code_F__c = 'HOTLEAD' or l.Campaign_Code_F__c = 'Digital Insurance')
order by af_submit_date desc
)
select * FROM dib_salesforce
where (idx = 1) and (af_submit_date >= CAST('2023-01-01' AS DATE)) and (Payment_Status != 'Canceled' and Payment_Status != 'Failed')
Thanks for help.