SQL WITH statements for date filtering

Question

I have a few years of price data from which I need to extract data for specific dates: a year ago, a quarter ago, a month ago, two weeks ago. I use Apache Impala SQL

I tried to use WITH statements to create a subset of filter days. Since a year ago date could be a weekend or a holiday, I choose the max date from the range of (-370.. -365 days ago)

WITH yearpast AS (
    SELECT max(quote_date_time) as yearago
    FROM quotes_raw
    WHERE quote_date_time >= adddate(now(), -370) and quote_date_time <= adddate(now(), -365)),

monthpast AS (
    SELECT max(quote_date_time) as monthago
    FROM quotes_raw
    WHERE quote_date_time >= adddate(now(), -33) and quote_date_time <= adddate(now(), -30)
    )

SELECT close_px FROM quotes_raw
    WHERE quote_date_time IN (yearpast.yeargo, monthpast.monthago)

I'd expect the SELECT statement to use yearpast's quote_date_time as a filter but instead I am getting an error.

Answer 1

You need to refer cte:

WITH yearpast AS (
    SELECT max(quote_date_time)
    FROM quotes_raw
    WHERE quote_date_time >= adddate(now(), -370) and quote_date_time <= adddate(now(), -365)
)
SELECT close_px 
FROM quotes_raw
WHERE quote_date_time IN (SELECT yearpast.quote_date_time FROM yearpast);

EDIT:

WITH yearpast AS (
    SELECT max(quote_date_time) as yearago
    FROM quotes_raw
    WHERE quote_date_time>=adddate(now(),-370) and quote_date_time <= adddate(now(), -365)),

monthpast AS (
    SELECT max(quote_date_time) as monthago
    FROM quotes_raw
    WHERE quote_date_time >= adddate(now(), -33) and quote_date_time <= adddate(now(), -30)
    )

SELECT close_px 
FROM quotes_raw
WHERE quote_date_time IN (SELECT yearpast.yeargo FROM yearpast
                          UNION ALL SELECT monthpast.monthago FROM monthpast)

Answer 2

How about just using order by and limit?

select qr.*from quotes_raw qr
where quote_date_time >= adddate(now(), -370)
order by quote_date_time
limit 1;