I have a MySQL table with user_id, action and date(timestamp format) columns.

user_id   |action     |date
1234      |update     |2011-10-18
1234      |transfer   |2011-10-22 
1235      |log in     |2011-11-15
1235      |update     |2011-11-16
1235      |log out    |2011-11-29
1236      |log in     |2021-11-29

What I want to achieve is to see the number of days between the last two actions by each user_id. If there is only one action for some user, return the number of days from the date of action to today. The result should be:

user_id   |num_days
1234      |4
1235      |13
1236      |3

Hope this makes sense, any help is appreciated. I have searched all the relevant topics here.

CodePudding user response：

You can use a self-join:

select a1.user_id, abs(datediff(a1.m, max(case when a2.date is null then date(now()) else a2.date end))) num_days from 
   (select a.user_id, max(a.date) m from actions a group by a.user_id) a1 left join actions a2 on a1.user_id = a2.user_id and a2.date < a1.m 
group by a1.user_id, a1.m

Output:

CodePudding user response：

the self-join, see answer from Ajax1234 with some changes:

SELECT
   u1.user_id,
   u1.`date`,
   COALESCE(u2.`date`, current_date()) D,
   DATEDIFF(COALESCE(u2.`date`, current_date()), u1.`date`) num_days
FROM actions u1
LEFT JOIN actions u2 on u2.user_id=u1.user_id and u2.`date`<u1.`date`;

output: