Getting sql data based on row value

Question

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 1

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

Answer 2

If I understand correctly, you can try to use subquery with IN

select max(date), id 
from table 
where id IN (
 select id 
 from table 
 where date='2022-04-03'
)
GROUP BY id

Answer 3

You can use the Sub-query to get your desired result -

SELECT MAX(date_), id    -- I have used date column as date_ as date is reserved word.
  FROM (SELECT date_, id
          FROM your_table
         WHERE date_ < DATE '2022-04-03'); -- Since you need the last day excluding today's date.
 GROUP BY id;