SQL tricks
September 1

How to get user last activity record?

Data: user_activity

user_id activity activity_date   
1       login    2020-01-01 15:16:17 
2       login    2020-01-01 15:18:19 
3       login    2020-01-01 19:24:41 
3       logout   2020-01-01 20:08:01 
1       logout   2020-01-02 03:16:17

Question: What are the possible ways to receive last activity for each user from users activities log table?

Solution 1: Using window functions

SELECT 
  user_id,
  activity,
  activity_date
FROM (
  SELECT
    user_id,
    activity,
    activity_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY activity_date DESC) rn
  FROM user_activity
) activities 
WHERE rn = 1
ORDER BY activity_date DESC;

SQL Fiddle: https://sqlize.online/sql/mysql80/dc2988c4aebe11560031689525bb4091/

Solution 2: Using sub-query

SELECT 
  user_activity.user_id,
  user_activity.activity,
  last_activity_date
FROM user_activity
JOIN (
  SELECT
    user_id,
    MAX(activity_date) last_activity_date
  FROM user_activity
  GROUP BY user_id
) last_activity ON 
  last_activity.user_id = user_activity.user_id AND
  last_activity.last_activity_date = user_activity.activity_date
ORDER BY activity_date DESC;

MySQL Fiddle: https://sqlize.online/sql/mysql57/9bb3ba88aa4eb7aed3a3392756addc51/