I'm trying to query a daily_price table with 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:

I have zero SQL experience and I have tried this query and a few others 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')"""

Any help would be greatly appreciated!

CodePudding user response：

I'm using lag to compare today's close with yesterday's high and row_number to give you only the last date for each symbol in the table.

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

where   close > pre 
  and   close > open 
  and   rn = 1

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.