CODEWITHSUNDEEP
mysqlsql
ebuzz168
ebuzz168

Reputation: 1194

How to Find People Who did Transaction Twice on The Same Day and Who did Transaction Consecutively in Less than 10 Minutes

I have another table which looked like this:

+--------+--------+------------------------+-----------------+------------+
|orderid | userid | create_time            | payment_amount  | product    |
+--------+--------+------------------------+-----------------+------------+
|20001   | 1001   | 2018-04-02 5.26.21     |       48        |  key chain |   
|20002   | 1002   | 2018-04-02 7.44.13     |       25        |  pin       |     
|20003   | 1001   | 2018-04-02 8.34.48     |      320        |  tote bag  |    
|20004   | 1001   | 2018-04-02 8.37.23     |      180        |  mug       |   
|20005   | 1003   | 2018-04-02 9.32.08     |       21        |  key chain |   
|20006   | 1002   | 2018-04-02 9.33.10     |      200        |  tumblr    |   
|....... | ...    |      ...               |      ...        |     ...    |   
+--------+--------+------------------------+-----------------+------------+

I need to write two queries:

  1. Find users who did transactions twice on the same day, what product were they bought and how much were they spend on the 1st transaction.

  2. Find which users did transaction consecutively less than 10 minutes!

I am very appreciating for your help.

Upvotes: 0

Views: 394

Answers (1)

zip
zip

Reputation: 4061

1st query: Gives product were they bought and how much were they spend on the 1st and secund transactions

with cte as
(
  select userid, cast(create_time as date) as trx_Day 
  from tbl
  group by userid, cast(create_time as date) 
  having count(*) = 2
)
select a.userid, product, payment_amount, a.create_time            
from tbl a inner join cte b on a.userid = b.userid and 
cast(a.create_time as date) = b.trx_Day
order by a.create_time

2nd Query:

    select distinct userid 
    from tbl a where exists(select 1 from tbl b where a.userid  = b.userid  
and ROUND((UNIX_TIMESTAMP(a.create_time) - UNIX_TIMESTAMP(b.create_time)) / 60)< 10
)

Upvotes: 4

Related Questions