1

I have data like this below

First-month transaction

User_id        trsaction_completed_date               user_type
 1234       7-Jan-19                New 
 5657       8-Jan-19                New 
 7890       9-Jan-19                                New 
 98456         20-Jan-19                                new

Second-month transaction

User_id         trsaction_completed_date         user_type 
1234            4-Feb-19         Existing 
5657            5-Feb-19         Existing 
567567          2/13/2019         New

Need to find repeated users for the current month from previous month data. The current month is Feb as per my data and Previous month is Jan.

As per my data, I need to get below output

User id 1234,5657 are transacted in Jan as well as Feb

Output:

-----------
Month count
Feb     2
leftjoin
  • 36,950
  • 8
  • 57
  • 116
sana
  • 11
  • 2
  • The problem here is that the date is not in the proper (sortable) format and even different format for the same month are present – leftjoin Nov 06 '19 at 07:16

1 Answers1

1

After converting the date to yyyy-MM-dd format, you can count user transactions per months, use lag() to get previous month count.

select month, user_id 
from
(
select month, user_id , cnt, 
      lag(cnt) over(partition by user_id order by month) prev_month_cnt
(
select month(trsaction_completed_date) as month, user_id 
       count(*) cnt
  from transaction_table 
 where trsaction_completed_date between '2019-01-01' and '2019-02-28'
 group by month(trsaction_completed_date), user_id 
)s
)s where month='02' --Feb users
     and  prev_month_cnt>0 --available in previous month

Add count if necessary to get total by month

leftjoin
  • 36,950
  • 8
  • 57
  • 116