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
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.
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:
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
Based on the question, it appears that you do have enough to identify the target users.
Slightwv's code above should work just fine.
Slightwv's code above should work just fine.
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
sample1.csv
sample expected result with maximum sales date data point
USERID | ENROLLMENT_DT | SALES_DT | MUMBER_SALES |
40213621 | 4/18/2018 | 30-Nov-21 | 0 |
45974065 | 6/6/2016 | 30-Nov-21 | 0 |
41027895 | 12/27/2006 | 30-Nov-21 | 0 |
Curious about your data file:
You have sales dates with no sales? Is that correct?
If so, something like this should work:
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
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)
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.
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.
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.
@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?
Our question is:
How can you have a sales_dt if nothing was sold?
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
https://www.experts-exchange.com/questions/29231223/List-of-Customers-who-did-not-make-A-Purchase-Within-a-Period.html