0

My Database include every weekdays for each stocks [daily_price]:

Stock Date Price
Apple 2018-06-01 100$
Apple 2018-06-02 130$
Apple 2018-06-03 143$
Apple 2018-06-04 286$
... ... ...
Google 2021-03-13 3000$
Google 2021-03-14 3900$
Google 2021-03-15 7800$
Google 2021-03-16 3900$
... ... ...

I want to make a query that looks like this:

(trying to see movements of the next 3 days of stocks that has gone up more than 30% on D+1)

Stock From To D+1 % D+2 % D+3 %
Apple 2018-06-02 2018-06-04 +30% +10% +100%
Apple 2021-03-14 2021-03-16 +30% +100% -50%

My query [tried to used lead()] is as follows:

WITH comparing_price AS (
    SELECT
        CODE, DATE, OPEN, high, low, close, volume,
        LEAD(daily_price, 1) OVER (PARTITION BY CODE ORDER BY DATE) AS x1,
        LEAD(daily_price, 2) OVER (PARTITION BY CODE ORDER BY DATE) AS x2,
        LEAD(daily_price, 3) OVER (PARTITION BY CODE ORDER BY DATE) AS x3
    FROM daily_price x0
)
SELECT
    x0.code as 'Stock',
    x1.date as 'From',
    x3.date as 'To',
    100*(x1.close - x0.close)/x0.close AS 'D+1 %',
    100*(x2.close - x1.close)/x1.close AS 'D+2 %',
    100*(x3.close - x2.close)/x2.close AS 'D+2 %'
FROM
    comparing_price
WHERE
    100*(x1.close - x0.close)/x0.close >= '30';

My Table's primary keys are stock name [code] and date [date] from [daily_price] DB.

It gives me SQL error 1054: Unknown column 'daily_price' in 'field list'

koguma
  • 5
  • 3

1 Answers1

0

Your aliases are off and all over the place. But, you don't even need to use table aliases here, as there is only one table/CTE involved. Try this version:

WITH comparing_price AS (
    SELECT
        CODE, DATE, OPEN, high, low, close, volume,
        LEAD(daily_price, 1) OVER (PARTITION BY CODE ORDER BY DATE) AS x1,
        LEAD(daily_price, 2) OVER (PARTITION BY CODE ORDER BY DATE) AS x2,
        LEAD(daily_price, 3) OVER (PARTITION BY CODE ORDER BY DATE) AS x3
    FROM daily_price
)

SELECT
    CODE AS Stock,
    DATE AS `From`,
    DATE + INTERVAL 2 DAY AS `To`,
    100*(x1 - x0) / x0 AS `D+1 %`,
    100*(x2 - x1) / x1 AS `D+2 %`,
    100*(x3 - x2) / x2 AS `D+2 %`
FROM comparing_price
WHERE
    100*(x1 - x0) / x0 >= 30;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • I think this version is much simpler, but I still get an error "unknown column daily_price". I think the assumption of using LEAD() to the entire table [daily_price] is not correct? Is there a way for me to LEAD() the entire table and name it as x1 x2 x3 so that I can use the variables for selection? – koguma Jun 18 '21 at 06:21
  • Well is the price column called `daily_price` or is it called something else? Check your table definition (and only you can really do that). – Tim Biegeleisen Jun 18 '21 at 06:23
  • daily_price is the name of my DB. I need to make every column of the DB to be given offset, so I thought putting DB into lead() would work. Otherwise I would have to use Lead() on column by column, which would make my query too heavy. I'm trying to find a way to offset the entire DB and keep it light at the same time. Is there any other way? – koguma Jun 18 '21 at 07:06
  • `daily_price` is the name of your _table_, _not_ your database. Re-read the above comment. You need to find the name of the price column. – Tim Biegeleisen Jun 18 '21 at 07:09