Link to home
Start Free TrialLog in
Avatar of fb1990
fb1990

asked on

List of Customers who did not make A Purchase

Hello Experts,

Can someone please help with oracle SQL code to pull data for customers who enrolled in our services more that 2 months ago, but have not made any purchase.  My database has userid, enrollment_dt, count_sales fields

Avatar of Lee W, MVP
Lee W, MVP
Flag of United States of America image

If I'm reading your fields names correctly, you don't have the appropriate fields.  You would need either a sales table that links to userid with a date field in the sales table or you would need a field for "lastpurchase" or something like that.  "enrollment_dt" (as I GUESS it) is the date the customer signed up.  NOT a purchase date.  Without a last purchase date or a linked table with a sales date, you can't get the information you want.
Avatar of fb1990
fb1990

ASKER

@Lee.  You are correct.  I have lst_sales_dt field on the table. This is the last sales date field. Thanks for the observation.
Without actual tables, columns, sample data and expected results, we are guessing.

Pretty sure you'll need the months_between function:
https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/MONTHS_BETWEEN.html


Something like:
select userid
from your_table
   join some_other_table using (some_column)
   ... other joins if you need them
where count_sales=0 and months_between(trunc(sysdate), enrollment_dt) > 2

Open in new window

Based on the question, it appears that you do have enough to identify the target users.

Slightwv's code above should work just fine.
Avatar of fb1990

ASKER

Thanks, Slightwv and Kent.  I still need more help with this.  I have attached a sample file:
sample1.csv

sample expected result with maximum sales date data point 
USERIDENROLLMENT_DTSALES_DTMUMBER_SALES
402136214/18/201830-Nov-210
459740656/6/201630-Nov-210
4102789512/27/200630-Nov-210
Curious about your data file:
1102587,3/20/2009,1-Jul-13,1 
1102587,3/20/2009,1-Aug-13,2 
1102587,3/20/2009,30-Sep-13,1
1102587,3/20/2009,31-Mar-14,0

Open in new window


You have sales dates with no sales?  Is that correct?

If so, something like this should work:

SELECT *
FROM your_table t0
WHERE months_between(trunc(sysdate), enrollment_date) >= 2
  AND not exists (SELECT 1 FROM your_table t1 WHERE t0.user_id = t1.user_id AND sales_count > 0)

Open in new window



Yes, seems odd that you have a sales_dt and didn't sell anything.

You should be able to get it without hitting the table twice.

I'm busy right now.  Will try to get some time later today if no one else posts.
Avatar of fb1990

ASKER

Thanks, Kent and Slightwv.  If the customer does not have sales made, it will show as 0.

@Kent code selects the data, but I want to see one line per userid showing the most recent record.  I am also interested in Slightwv comment about hitting the table ones.
>> If the customer does not have sales made, it will show as 0.

Our question is:
How can you have a sales_dt if nothing was sold?
Avatar of fb1990

ASKER

@Slightwv: the sales_dt is treated as the data date or update date
SELECT user_id, enrollment_dt, max(sales_dt) 
FROM your_table t0
WHERE sales > 0
  AND months_between(trunc(sysdate), enrollment_dt) >= 0
GROUP BY user_id, enrollment_dt
HAVING months_between(trunc(sysdate), max(sales_dt)) >= 2

Open in new window


After thinking about this a bit more the question's a bit easier than I was making it out to be.

Filter out all users that have been enrolled less than 2 months, then select the those users with no sales in the past 2 months.

That should do well....
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of fb1990

ASKER

Thank you so much for the solution.  I have asked a follow up question and it is on the URL below:

https://www.experts-exchange.com/questions/29231223/List-of-Customers-who-did-not-make-A-Purchase-Within-a-Period.html