Getting sql data based on row value

I have a table like so:

date       |    id
------------------------
2022-04-01 |    1
2022-04-02 |    1
2022-04-03 |    1
2022-04-01 |    2
2022-04-03 |    2
2022-04-02 |    3

I’m trying to get the last date when the account was active, not counting today. If the id is not present on today’s date, it is not included in the final table. So for the case of id 1, that would be 2022-04-02, and for id 2, it would be 2022-04-01

I can do a select max(ds) from table where date != "2022-04-03" to get the last date, but how can I get the last date for each account, in the following format:

date       |   id
------------------
2022-04-02 |   1
2022-04-01 |   2

Even if I try to subquery it, I’m not sure what the subquery should be. I’ve tried:

with ids as (select id from table where date="2022-04-03")
select max(date), id from table where id in ids.id

but it gives me the following error: cannot recognize input near 'ids' '.' 'id' in expression specification

Answer

you can work with the window function ROW_NUMBER to get the date you want

WITH CTE AS (select id,date, ROW_NUMBER() OVER(PARTITION BY id ORDER BY Date DESC) rn from table)
SELECT id,date FROM CTE where rn = 2

Torpas is right, to exclude todays logins you and add another condition to the with clause

WITH CTE AS (select id,date, ROW_NUMBER() OVER(PARTITION BY id ORDER BY Date DESC) rn from table WHERE id NOT IN ( SELECT id from table WHERE date = current_date()))
SELECT id,date FROM CTE where rn = 2