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

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

CodePudding user response：

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.

CodePudding user response：

You can use MAX and GROUP BY:

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