Hello Tom,
Please refer to my data below:
create table transactions (
order_id int,
cust_id int,
order_date date,
amount int
);
alter session set nls_date_format = 'yyyy-mm-dd';
insert into transactions values (1,1,'2020-01-15',150);
insert into transactions values (2,1,'2020-02-10',150);
insert into transactions values (3,2,'2020-01-16',150);
insert into transactions values (4,2,'2020-02-25',150);
insert into transactions values (5,3,'2020-01-10',150);
insert into transactions values (6,3,'2020-02-20',150);
insert into transactions values (7,4,'2020-01-20',150);
insert into transactions values (8,5,'2020-02-20',150);
How do I get the data of customer retention from the above table? For Example, The Customer with ID: 1 has transactions from Jan & Feb 2020. In order words, This customer has been retained by the company since he is a repeat buyer.
How do I write a query in Oracle SQL using either self join or analytical functions to check if the customer has made a purchase in the previous month or not? The end result is to determine the percentage of customers retained from the previous month and reduce churning.
Thanks in advance
There are many ways you can do this.
Here's an example using LAG to see if the customer has made a previous transaction:
with rws as (
select t.*,
lag ( amount ) over (
partition by cust_id
order by order_date
) prev_month_amount
from transactions t
)
select count (*) total_custs_in_month,
count ( prev_month_amount ) num_with_trans_prev_month
from rws r
where order_date >= date'2020-02-01';
TOTAL_CUSTS_IN_MONTH NUM_WITH_TRANS_PREV_MONTH
-------------------- -------------------------
4 3If there's no previous transaction, LAG returns NULL. So you can count the non-null occurrences of this to see the number retained and compare this to the total for the month.
Exactly how you write the query will depend on which questions you need to answer, such as:
- Do you consider any previous transactions for a customer or only those in the previous month?
- How should customer 4 with a transaction in Jan but not Feb be reported?
- What happens if a customer makes 2+ transactions in a month?
If you need more help please give more details on what it is you're trying to do.