Question 95667 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

How to find last active day for each user in SQL? – Amazon web services

I have a table which looks like this:

date user_id active_dt last_active_day
2020-01-01 1 null null
2020-01-02 1 null null
2020-01-03 1 null null
2020-01-04 1 2020-01-05 null
2020-01-05 1 2020-01-08 2020-01-05 (as of 2020-01-05, the last actve date was 2020-01-05).
2020-01-06 1 null 2020-01-05 (as of 2020-01-06, the last active date was 2020-01-05).
2020-01-07 1 null 2020-01-05 (as of 2020-01-07, the last active date was 2020-01-05).
2020-04-18 1 null 2020-01-08 (as of 2020-04-18, the last active date was 2020-01-08).
Tags:

2

Answers


  1. 0

    Simple

    SELECT user_id, last_active_day
FROM table_name
WHERE last_active_day IS NOT NULL
GROUP BY user_id
ORDER BY last_active_day DESC

    query.

    Login or Signup to reply.
  2. 0

    You can use MAX and GROUP BY:

    SELECT user_id, MAX(last_active_day) FROM table_name GROUP BY user_id;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search