AMD_Stock_Analysis/query2EMA.sql at main · Yunado/AMD_Stock_Analysis · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
SELECT *
FROM (
    SELECT
    event_time,
    ticker,
    close_price,
    sma as avg_close_price,
    ((close_price - emaprev) * (2.0/11.0) + emaprev) AS ema_close_price,
    `date`
    FROM (
        SELECT
            event_time,
            ticker,
            close_price,
            sma,
            (close_price - LAG(sma, 1) OVER (ORDER BY event_time)) * (2.0/11.0) + LAG(sma, 1) OVER (ORDER BY event_time) as emaprev,
            `date`
        FROM (
            SELECT
                event_time,
                ticker,
                close_price,
                AVG(close_price) OVER (
                  PARTITION BY ticker
                  ORDER BY event_time
                  ROWS BETWEEN 9 PRECEDING AND CURRENT ROW
                ) AS sma,
                `date`
            FROM stock_table
        ) t
    ) t1
) t2
WHERE close_price < ema_close_price;