CODEWITHSUNDEEP
sqlamazon-web-servicesamazon-redshift
titutubs
titutubs

Reputation: 365

How to find last active day for each user in SQL?

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).

Upvotes: 0

Views: 51

Answers (2)

Lucas Martiniano
Lucas Martiniano

Reputation: 261

You can use MAX and GROUP BY:

SELECT user_id, MAX(last_active_day) FROM table_name GROUP BY user_id;

Upvotes: 1

Kushtra
Kushtra

Reputation: 76

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.

Upvotes: 0

Related Questions