CODEWITHSUNDEEP
mysqlsql
OctavianWR
OctavianWR

Reputation: 227

SQL Insert end of day for user last activity

I have this table

user_id    activity   dt
1          login      2020-01-01 08:00:00
1          logout     2020-01-01 20:00:00
2          home       2020-01-01 19:00:00
1          profile    2020-01-02 08:00:00

I need to insert last day for every user and the event of the last day is the last activity of that user. for example if user A logout on 2020-01-01 20:00:00 then you insert user A logout on 2020-01-02 00:00:00. the example is like in the last 3 rows of this table:

user_id    activity   dt
1          login      2020-01-01 08:00:00
1          logout     2020-01-01 20:00:00
2          home       2020-01-01 19:00:00
1          profile    2020-01-02 08:00:00
1          logout     2020-01-02 00:00:00
2          home       2020-01-02 00:00:00
1          profile    2020-01-03 00:00:00

on 2020-01-01 there is 2 user that have activity, so you have to input last activity of user 1 and 2 and the time is 2020-01-02 00:00:00.

I already search this solution on internet but can't find out the way to do it. All I have done is insert it manual one by one

Upvotes: 1

Views: 216

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Hmmm . . . To get the last activity on each date with the appropriate new dt column:

select user_id, activity,
       date(dt) + interval 1 day
from (select t.*,
             row_number() over (partition by user_id, date(dt) order by dt desc) as seqnum
      from t
     ) t
where seqnum = 1;

For a result set, you can union this to the existing table. If you want to actually modify the table, then insert these rows into the table.

Upvotes: 1

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

In modern MySQL since version 8.0 you can use next approach:

insert into tbl 
select distinct
    user_id,
    last_value(activity) over (partition by user_id, date(dt)),
    date_add(date(dt), interval 1 day) dt
from tbl
order by dt
;

share SQL query

Upvotes: 1

Related Questions