How to query two values in different columns from different dates?-CodePudding

I'm trying to query a daily_price table by the following logic:

today's close > today's open and today's close > yesterdays high

The desired output is returning all rows from today that meet the criteria. With the below example, and assuming today is 2022-08-31, the query would return ACR but not NBI because the logic matches (i.e. 2022-08-31 close > 2022-08-31 open and 2022-08-31 close > 2022-08-30 high).

daily_price example:

symbol	date	open	high	low	close
ACR	2022-08-30	0.061	0.063	0.06	0.06
ACR	2022-08-31	0.066	0.07	0.066	0.07
NBI	2022-08-30	1.52	1.52	1.51	1.52
NBI	2022-08-31	1.51	1.52	1.505	1.515

I tried this query with no luck:

SELECT * 
FROM daily_price 
WHERE close > open
AND date = '2022-08-31' 
AND (SELECT close FROM daily_price WHERE date = '2022-08-31') > (SELECT high FROM daily_price WHERE date = '2022-08-30')

CodePudding user response：

I'm using lag to compare today's close with yesterday's high and filtering by date.

select  symbol  
       ,date    
       ,open    
       ,high    
       ,low 
       ,close
from    (
        select *
              ,lag(high)    over (partition by symbol order by date)      as pre
        from   t
        ) t

where   close > pre 
  and   close > open 
  and   date = '2022-08-31'

symbol	date	open	high	low	close
ACR	2022-08-31	0.066	0.07	0.066	0.07

Fiddle

CodePudding user response：

You may use Exists with a correlated subquery as the following:

Select T.symbol, T.date_, T.open, T.high, T.low, T.close 
From daily_price T
Where T.close > T.open And
Exists (Select 1 From daily_price D 
        Where D.symbol=T.symbol And 
              D.date_=Date(T.date_, '-1 day') And 
              D.high < T.close)

See a demo db<>fiddle.