confirmation_lag

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

WITH
px AS
(
    SELECT
        ticker,
        date,
        toFloat64(max(close)) AS close_px
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('DIA', 'IYT')
    GROUP BY ticker, date
),
joint AS
(
    SELECT
        date,
        anyIf(close_px, ticker = 'DIA') AS dia,
        anyIf(close_px, ticker = 'IYT') AS iyt
    FROM px
    GROUP BY date
    HAVING countIf(ticker = 'DIA') = 1 AND countIf(ticker = 'IYT') = 1
),
rolled AS
(
    SELECT
        date,
        dia,
        iyt,
        row_number() OVER (ORDER BY date) AS i,
        count() OVER () AS n,
        max(dia) OVER (ORDER BY date ROWS BETWEEN 62 PRECEDING AND 1 PRECEDING) AS dia_prior_high,
        max(iyt) OVER (ORDER BY date ROWS BETWEEN 62 PRECEDING AND 1 PRECEDING) AS iyt_prior_high
    FROM joint
),
marked AS
(
    SELECT
        date,
        i,
        n,
        if(i > 62 AND dia > dia_prior_high, 1, 0) AS dia_signal,
        if(i > 62 AND iyt > iyt_prior_high, 1, 0) AS iyt_signal
    FROM rolled
),
scanned AS
(
    SELECT
        i,
        n,
        dia_signal,
        indexOf(groupArray(iyt_signal) OVER (ORDER BY date ROWS BETWEEN CURRENT ROW AND 20 FOLLOWING), 1) - 1 AS confirm_lag
    FROM marked
),
confirmed_signals AS
(
    SELECT
        confirm_lag,
        count() OVER () AS n_confirmed
    FROM scanned
    WHERE dia_signal = 1 AND i + 20 <= n AND confirm_lag >= 0
)
SELECT
    multiIf(confirm_lag <= 2, '0 ถึง 2 วัน',
            confirm_lag <= 5, '3 ถึง 5 วัน',
            confirm_lag <= 10, '6 ถึง 10 วัน',
            confirm_lag <= 15, '11 ถึง 15 วัน',
            '16 ถึง 20 วัน')                        AS lag_bucket,
    count()                                         AS signal_count,
    round(100 * count() / any(n_confirmed), 1)      AS share_pct
FROM confirmed_signals
GROUP BY lag_bucket
ORDER BY min(confirm_lag)