How to solve & fix query for new or returning customer?

Question & Answer
,
1

Hi All,

I would like to indentify who is new or returning customer?

this is my query

with dib_salesforce as (
    select 
    DATE(SUBSTRING(af.createddate,1,10)) createddate,
--personal data  
    p.National_ID__c as NationID,
    p.MobilePhone__c as Mobile,
    af.product_type__c as product_type,   
    ap.TotalPremium__c as TotalPremium,

-- 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_ap_personal" as p 
    on af.id = p.Application_Form__c
    where  ((ap.Status__c = 'Success' or ap.Status__c is null)) and ((af.Status__c = 'Get ePolicy File Success' or af.Status__c = 'Get ePolicy File Failed' or af.Status__c = 'Issue Policy Completed' or af.Status__c = 'Issue Policy Failed')) and (af.CreatedById='0055g00000CVzYfAAL')
)

select * 

   CASE NationID
          WHEN COUNT(*) = 1 THEN 'New Customer'
    ELSE 'Returning Customer'
    END 
    customer_type,

FROM dib_salesforce 
group by NationID
where (idx = 1) and (createddate >= CAST('2023-01-01' AS DATE))
limit 50

this is error what i got.

Error details

region: ap-southeast-1
timestamp: 1687334215865
requestId: bded30b7-7230-4d43-8d11-30a590729574
sourceErrorCode: 100071
sourceErrorMessage: [Simba]AthenaJDBC An error has been thrown from the AWS Athena client. line 24:4: mismatched input ‘CASE’. Expecting: ‘)’, ‘,’, ‘AS’, ‘CROSS’, ‘EXCEPT’, ‘FETCH’, ‘FROM’, ‘FULL’, ‘GROUP’, ‘HAVING’, ‘INNER’, ‘INTERSECT’, ‘JOIN’, ‘LEFT’, ‘LIMIT’, ‘MATCH_RECOGNIZE’, ‘NATURAL’, ‘OFFSET’, ‘ORDER’, ‘RIGHT’, ‘TABLESAMPLE’, ‘UNION’, ‘WHERE’, ‘WINDOW’, , [Execution ID not available]
sourceErrorState: HY000
sourceException: java.sql.SQLException
sourceType: ATHENA

My Data Set is

image
image1490×642 59.8 KB

Thanks for help
Siriphon

2

Hi @Siriphon - Can you please try the below.

with dib_salesforce as (
    select 
    DATE(SUBSTRING(af.createddate,1,10)) createddate,
--personal data  
    p.National_ID__c as NationID,
    p.MobilePhone__c as Mobile,
    af.product_type__c as product_type,   
    ap.TotalPremium__c as TotalPremium,

-- 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_ap_personal" as p 
    on af.id = p.Application_Form__c
    where  ((ap.Status__c = 'Success' or ap.Status__c is null)) and ((af.Status__c = 'Get ePolicy File Success' or af.Status__c = 'Get ePolicy File Failed' or af.Status__c = 'Issue Policy Completed' or af.Status__c = 'Issue Policy Failed')) and (af.CreatedById='0055g00000CVzYfAAL')
),
dib_salesforce_temp as (select NationID, count(1) as rec_cnt from dib_salesforce  group by NationID)
select A.*,
       case B.rec_cnt when 1 then 'New Customer'
       else 'Returning Customer'
       end customer_type
FROM dib_salesforce A,dib_salesforce_temp B
where A.NationID = B.NationID AND 
A.idx = 1 and A.createddate >= CAST('2023-01-01' AS DATE)
limit 50
3

it’s working but data is look a bit incorrect.
it should be new customer higher more than return customer.

image

4

Hi @Siriphon - Can you remove the A.idx = 1 condition, I am not sure why you kept that. All I did have a separate sub query and do the joining. Not sure about other conditions.

Regards - Sanjeeb

5

Hi sanjeeb

data set without A.idx = 1

image
image1422×666 80 KB

data set with A.idx = 1

image
image1462×550 68.1 KB

6

Hi @Siriphon - Thanks, You need to put the condition A.idx=1 to have unique customers.
Sorry I did not know other conditions, please validate those details.
Also validate sample data which is missed in the data.

Regards - Sanjeeb

1 Like