How can I find the latest date where a particular query returns no rows in a table?

Question

I have a SQL query:

select max(date) 
    from index_constituents 
    where (opening_closing ='O' 
        and index_code ='buk350n' 
        and issuer = 'cboe') and date = '2024-04-25';

id	date	issuer	index_code	opening_closing
1393	2024-04-25	cboe	buk350n	O
1394	2024-04-25	cboe	buk350n	O

When I run the same query but for the day before, I get:

```sql
select max(date) 
    from index_constituents 
    where (opening_closing ='O' 
        and index_code ='buk350n' 
        and issuer = 'cboe') and date = '2024-04-24';
```

 id    |   date        |  issuer |  index_code   | opening_closing
 -     |  -            | -       | -             | -
1402   |   2024-04-24  |   cboe  |   buk350n     |   O

When I run

```sql
select max(date) 
    from index_constituents 
    where (opening_closing ='O' 
        and index_code ='buk350n' 
        and issuer = 'cboe') and date = '2024-04-23';
```

NOTHING IS RETURNED.

So I want to return 2024-04-23

i.e. I want to find the most recent date BEFORE A GIVEN DATE where there is no data when I run my query

Answer 1

If you need to find latest date with no record after existing record's date then you can use 1st query:

select 
"date"-1 latest_unfound_record
from 
(
select 
"date" - LEAD(date) OVER (ORDER BY date desc) date_difference_in_days,
* 
from index_constituents
) as temp
where date_difference_in_days>1
order by date desc
LIMIT 1;

If you need to find latest date before today with no records then use this second query:

select 
next_date-1 latest
from 
(
select 
"date" - COALESCE (LEAD(date) OVER (ORDER BY date), CURRENT_DATE) date_difference_in_days,
COALESCE (LEAD(date) OVER (ORDER BY date), CURRENT_DATE) AS next_date , 
* 
from index_constituents where date <= CURRENT_DATE
) as temp
where date_difference_in_days<-1
order by date desc
LIMIT 1;