CODEWITHSUNDEEP
mysql
haneulkim
haneulkim

Reputation: 4928

find first transaction after created date and added to a column MySQL

I am using MySQL version 8.0

MRE:

create table users(
    user varchar(5),
    work_type varchar(20),
    time datetime
);

insert into users(user, work_type, time)
Values ("A", "create", "2020-01-01 11:11:11")
     , ("A", "bought", "2020-01-04 16:11:11")
     , ("A", "bought", "2020-01-07 18:10:10")
     , ("A", "bought", "2020-01-08 12:00:11")
     , ("A", "create", "2020-02-02 15:17:11")
     , ("A", "bought", "2020-02-02 16:11:11");

In my table for each user there is a "work_type" column which specifies what user does.

user     work_type           time
 A        create        2020-01-01 11:11:11
 A        bought        2020-01-04 16:11:11
 A        bought        2020-01-07 18:10:10
 A        bought        2020-01-08 12:00:11
 A        create        2020-02-02 15:17:11
 A        bought        2020-02-02 16:11:11

Since after user A "create" their account I want to find only first bought time and add it to new column 

user     work_type           time              bought_time
 A        create        2020-01-01 11:11:11   2020-01-04 16:11:11
 A        create        2020-02-02 15:17:11   2020-02-02 16:11:11

Notice that user A can have multiple create work_type. Above is the desired output however there will be multiple user as well.

Upvotes: 0

Views: 140

Answers (2)

Shadow
Shadow

Reputation: 34294

A correlated subquery in the select list can retrieve a single value. I use the order by time asc limit 1 clauses to limit the number of returned rows to 1:

select t.*, (select t2.`time` from yourtable t2 where t2.user=t.user and t2.`time` > t.`time` and t2.work_type='bought' order by t2.`time` asc limit 1) as bought_time
from yourtable t
where work_type='create'

The above query is fine, as long as you have at least 1 bought record after each create one. If you cannot guarantee this and you have no other fields to link a create with the subsequent bought, then you have to complicate things to check for the type of the next record after the create. Note: I do not filter on the work_type field in the subquery any longer:

select t.*, (select if(t2.work_type='bought',t2.`time`,null) from yourtable t2 where t2.user=t.user and t2.`time` > t.`time` order by t2.`time` asc limit 1) as bought_time
from yourtable t
where work_type='create'

If the create and subsequent bought records form part of a set, then I would definitely create a field that links them together, meaning that this field would have the same value for all records belonging to the same set. This way it would be really easy to identify which records form part of the set.

Upvotes: 1

Nishant Gupta
Nishant Gupta

Reputation: 3656

Solution for your problem:

SELECT * FROM
(
SELECT 
user
,work_type
,CASE WHEN UPPER(work_type) = 'CREATE' THEN time END time
,CASE WHEN UPPER(work_type) = 'CREATE' 
THEN LEAD(time) OVER(PARTITION BY user ORDER BY time) END bought_time
FROM 
Table1) A
WHERE UPPER(work_type) = 'CREATE';

Link for demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ac0cf9375025b964769fd28514db0ce1

Upvotes: 0

Related Questions