Mô hình cốc tay cầm: breakout có thật sự đi tiếp?

WITH
    universe AS
    (
        SELECT ticker
        FROM global_markets.stocks_daily_aggs
        WHERE date >= '2014-06-01'
          AND ifNull(otc, 0) = 0
          AND ticker NOT IN ('SPCX')
        GROUP BY ticker
        HAVING count() >= 250
           AND avg(toFloat64(close) * volume) >= 2e7
    ),
    daily AS
    (
        SELECT
            ticker,
            date,
            toFloat64(close)  AS close,
            toFloat64(high)   AS high,
            toFloat64(low)    AS low,
            toFloat64(volume) AS volume
        FROM global_markets.stocks_daily_aggs
        WHERE date >= '2014-06-01'
          AND ticker IN (SELECT ticker FROM universe)
    ),
    featured AS
    (
        SELECT
            ticker,
            date,
            close,
            volume,
            max(high)   OVER cup    AS rim,
            min(low)    OVER cup    AS cup_low,
            max(high)   OVER handle AS handle_high,
            min(low)    OVER handle AS handle_low,
            avg(volume) OVER vol50  AS adv50,
            lagInFrame(close, 120)  OVER trend AS base_start_close,
            lagInFrame(close, 240)  OVER trend AS trend_start_close,
            count()     OVER trend  AS history_bars
        FROM daily
        WINDOW
            cup    AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 120 PRECEDING AND 11 PRECEDING),
            handle AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 10 PRECEDING AND 1 PRECEDING),
            vol50  AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 50 PRECEDING AND 1 PRECEDING),
            trend  AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 240 PRECEDING AND CURRENT ROW)
    ),
    signals AS
    (
        SELECT
            ticker,
            date       AS breakout_date,
            close      AS breakout_close,
            handle_low AS handle_low
        FROM featured
        WHERE history_bars = 241
          AND date >= '2015-01-01'
          AND date <= today() - 65
          AND rim > 0
          AND adv50 > 0
          AND close >= 10
          AND adv50 * close >= 1e8
          AND close > rim
          AND handle_high <= rim
          AND handle_low > cup_low
          AND (1 - cup_low / rim) >= 0.12
          AND (1 - cup_low / rim) <= 0.50
          AND (rim - handle_low) / rim <= 0.5 * (1 - cup_low / rim)
          AND base_start_close >= 1.10 * trend_start_close
    ),
    paths AS
    (
        SELECT
            min(if(b.low  <= s.handle_low,            b.date, toDate('2099-12-31'))) AS stop_date,
            min(if(b.high >= s.breakout_close * 1.05, b.date, toDate('2099-12-31'))) AS hit_05,
            min(if(b.high >= s.breakout_close * 1.10, b.date, toDate('2099-12-31'))) AS hit_10,
            min(if(b.high >= s.breakout_close * 1.15, b.date, toDate('2099-12-31'))) AS hit_15,
            min(if(b.high >= s.breakout_close * 1.20, b.date, toDate('2099-12-31'))) AS hit_20,
            min(if(b.high >= s.breakout_close * 1.25, b.date, toDate('2099-12-31'))) AS hit_25
        FROM daily AS b
        INNER JOIN signals AS s ON s.ticker = b.ticker
        WHERE b.date >  s.breakout_date
          AND b.date <= s.breakout_date + 60
        GROUP BY
            s.ticker,
            s.breakout_date,
            s.breakout_close,
            s.handle_low
    )
SELECT
    concat('+', toString(goal.1), '%')                    AS target_label,
    count()                                               AS signal_count,
    round(100 * countIf(goal.2 < stop_date) / count(), 1) AS hit_rate_pct,
    round(100 * countIf(goal.2 >= stop_date
                        AND stop_date < toDate('2099-12-31')) / count(), 1) AS stopped_first_pct
FROM
(
    SELECT
        stop_date,
        arrayJoin([(5, hit_05), (10, hit_10), (15, hit_15), (20, hit_20), (25, hit_25)]) AS goal
    FROM paths
)
GROUP BY target_label
ORDER BY min(goal.1)

WITH
    universe AS
    (
        SELECT ticker
        FROM global_markets.stocks_daily_aggs
        WHERE date >= '2014-06-01'
          AND ifNull(otc, 0) = 0
          AND ticker NOT IN ('SPCX')
        GROUP BY ticker
        HAVING count() >= 250
           AND avg(toFloat64(close) * volume) >= 2e7
    ),
    daily AS
    (
        SELECT
            ticker,
            date,
            toFloat64(close)  AS close,
            toFloat64(high)   AS high,
            toFloat64(low)    AS low,
            toFloat64(volume) AS volume
        FROM global_markets.stocks_daily_aggs
        WHERE date >= '2014-06-01'
          AND ticker IN (SELECT ticker FROM universe)
    ),
    featured AS
    (
        SELECT
            ticker,
            date,
            close,
            volume,
            max(high)   OVER cup    AS rim,
            min(low)    OVER cup    AS cup_low,
            max(high)   OVER handle AS handle_high,
            min(low)    OVER handle AS handle_low,
            avg(volume) OVER vol50  AS adv50,
            lagInFrame(close, 120)  OVER trend AS base_start_close,
            lagInFrame(close, 240)  OVER trend AS trend_start_close,
            count()     OVER trend  AS history_bars
        FROM daily
        WINDOW
            cup    AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 120 PRECEDING AND 11 PRECEDING),
            handle AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 10 PRECEDING AND 1 PRECEDING),
            vol50  AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 50 PRECEDING AND 1 PRECEDING),
            trend  AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 240 PRECEDING AND CURRENT ROW)
    ),
    signals AS
    (
        SELECT
            ticker,
            date           AS breakout_date,
            close          AS breakout_close,
            handle_low     AS handle_low,
            volume / adv50 AS rel_volume
        FROM featured
        WHERE history_bars = 241
          AND date >= '2015-01-01'
          AND date <= today() - 65
          AND rim > 0
          AND adv50 > 0
          AND close >= 10
          AND adv50 * close >= 1e8
          AND close > rim
          AND handle_high <= rim
          AND handle_low > cup_low
          AND (1 - cup_low / rim) >= 0.12
          AND (1 - cup_low / rim) <= 0.50
          AND (rim - handle_low) / rim <= 0.5 * (1 - cup_low / rim)
          AND base_start_close >= 1.10 * trend_start_close
    ),
    paths AS
    (
        SELECT
            s.rel_volume                                                             AS rel_volume,
            min(if(b.low  <= s.handle_low,            b.date, toDate('2099-12-31'))) AS stop_date,
            min(if(b.high >= s.breakout_close * 1.10, b.date, toDate('2099-12-31'))) AS hit_date
        FROM daily AS b
        INNER JOIN signals AS s ON s.ticker = b.ticker
        WHERE b.date >  s.breakout_date
          AND b.date <= s.breakout_date + 60
        GROUP BY
            s.ticker,
            s.breakout_date,
            s.breakout_close,
            s.handle_low,
            s.rel_volume
    )
SELECT
    multiIf(rel_volume < 1,   'Dưới 1x',
            rel_volume < 1.5, '1x đến 1,5x',
            rel_volume < 2,   '1,5x đến 2x',
            rel_volume < 3,   '2x đến 3x',
                              'Trên 3x')                    AS volume_bucket,
    count()                                                 AS signal_count,
    round(100 * countIf(hit_date < stop_date) / count(), 1) AS hit_rate_pct,
    round(100 * countIf(stop_date <= hit_date
                        AND stop_date < toDate('2099-12-31')) / count(), 1) AS stopped_first_pct
FROM paths
GROUP BY volume_bucket
ORDER BY min(rel_volume)

WITH
    universe AS
    (
        SELECT ticker
        FROM global_markets.stocks_daily_aggs
        WHERE date >= '2014-06-01'
          AND ifNull(otc, 0) = 0
          AND ticker NOT IN ('SPCX')
        GROUP BY ticker
        HAVING count() >= 250
           AND avg(toFloat64(close) * volume) >= 2e7
    ),
    daily AS
    (
        SELECT
            ticker,
            date,
            toFloat64(close)  AS close,
            toFloat64(high)   AS high,
            toFloat64(low)    AS low,
            toFloat64(volume) AS volume
        FROM global_markets.stocks_daily_aggs
        WHERE date >= '2014-06-01'
          AND ticker IN (SELECT ticker FROM universe)
    ),
    featured AS
    (
        SELECT
            ticker,
            date,
            close,
            volume,
            max(high)   OVER cup    AS rim,
            min(low)    OVER cup    AS cup_low,
            max(high)   OVER handle AS handle_high,
            min(low)    OVER handle AS handle_low,
            avg(volume) OVER vol50  AS adv50,
            lagInFrame(close, 120)  OVER trend AS base_start_close,
            lagInFrame(close, 240)  OVER trend AS trend_start_close,
            count()     OVER trend  AS history_bars
        FROM daily
        WINDOW
            cup    AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 120 PRECEDING AND 11 PRECEDING),
            handle AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 10 PRECEDING AND 1 PRECEDING),
            vol50  AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 50 PRECEDING AND 1 PRECEDING),
            trend  AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 240 PRECEDING AND CURRENT ROW)
    ),
    signals AS
    (
        SELECT
            ticker,
            date       AS breakout_date,
            close      AS breakout_close,
            handle_low AS handle_low
        FROM featured
        WHERE history_bars = 241
          AND date >= '2015-01-01'
          AND date <= today() - 65
          AND rim > 0
          AND adv50 > 0
          AND close >= 10
          AND adv50 * close >= 1e8
          AND close > rim
          AND handle_high <= rim
          AND handle_low > cup_low
          AND (1 - cup_low / rim) >= 0.12
          AND (1 - cup_low / rim) <= 0.50
          AND (rim - handle_low) / rim <= 0.5 * (1 - cup_low / rim)
          AND base_start_close >= 1.10 * trend_start_close
    ),
    paths AS
    (
        SELECT
            s.breakout_date                                                          AS breakout_date,
            min(if(b.low  <= s.handle_low,            b.date, toDate('2099-12-31'))) AS stop_date,
            min(if(b.high >= s.breakout_close * 1.10, b.date, toDate('2099-12-31'))) AS hit_date
        FROM daily AS b
        INNER JOIN signals AS s ON s.ticker = b.ticker
        WHERE b.date >  s.breakout_date
          AND b.date <= s.breakout_date + 60
        GROUP BY
            s.ticker,
            s.breakout_date,
            s.breakout_close,
            s.handle_low
    )
SELECT
    toYear(breakout_date)                                   AS year,
    count()                                                 AS signal_count,
    round(100 * countIf(hit_date < stop_date) / count(), 1) AS hit_rate_pct
FROM paths
GROUP BY year
ORDER BY year

WITH
    universe AS
    (
        SELECT ticker
        FROM global_markets.stocks_daily_aggs
        WHERE date >= '2014-06-01'
          AND ifNull(otc, 0) = 0
          AND ticker NOT IN ('SPCX')
        GROUP BY ticker
        HAVING count() >= 250
           AND avg(toFloat64(close) * volume) >= 2e7
    ),
    daily AS
    (
        SELECT
            ticker,
            date,
            toFloat64(close)  AS close,
            toFloat64(high)   AS high,
            toFloat64(low)    AS low,
            toFloat64(volume) AS volume
        FROM global_markets.stocks_daily_aggs
        WHERE date >= '2014-06-01'
          AND ticker IN (SELECT ticker FROM universe)
    ),
    featured AS
    (
        SELECT
            ticker,
            date,
            close,
            volume,
            max(high)   OVER cup    AS rim,
            min(low)    OVER cup    AS cup_low,
            max(high)   OVER handle AS handle_high,
            min(low)    OVER handle AS handle_low,
            avg(volume) OVER vol50  AS adv50,
            lagInFrame(close, 120)  OVER trend AS base_start_close,
            lagInFrame(close, 240)  OVER trend AS trend_start_close,
            count()     OVER trend  AS history_bars
        FROM daily
        WINDOW
            cup    AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 120 PRECEDING AND 11 PRECEDING),
            handle AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 10 PRECEDING AND 1 PRECEDING),
            vol50  AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 50 PRECEDING AND 1 PRECEDING),
            trend  AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 240 PRECEDING AND CURRENT ROW)
    ),
    signals AS
    (
        SELECT
            ticker,
            date                AS breakout_date,
            close               AS breakout_close,
            handle_low          AS handle_low,
            adv50 * close / 1e6 AS adv_usd_mn
        FROM featured
        WHERE history_bars = 241
          AND date >= '2015-01-01'
          AND date <= today() - 65
          AND rim > 0
          AND adv50 > 0
          AND close >= 10
          AND adv50 * close >= 1e8
          AND close > rim
          AND handle_high <= rim
          AND handle_low > cup_low
          AND (1 - cup_low / rim) >= 0.12
          AND (1 - cup_low / rim) <= 0.50
          AND (rim - handle_low) / rim <= 0.5 * (1 - cup_low / rim)
          AND base_start_close >= 1.10 * trend_start_close
    ),
    paths AS
    (
        SELECT
            s.adv_usd_mn                                                             AS adv_usd_mn,
            min(if(b.low  <= s.handle_low,            b.date, toDate('2099-12-31'))) AS stop_date,
            min(if(b.high >= s.breakout_close * 1.10, b.date, toDate('2099-12-31'))) AS hit_date
        FROM daily AS b
        INNER JOIN signals AS s ON s.ticker = b.ticker
        WHERE b.date >  s.breakout_date
          AND b.date <= s.breakout_date + 60
        GROUP BY
            s.ticker,
            s.breakout_date,
            s.breakout_close,
            s.handle_low,
            s.adv_usd_mn
    )
SELECT
    multiIf(adv_usd_mn < 300,  'Dưới 300 triệu USD',
            adv_usd_mn < 1000, '300 triệu đến 1 tỷ USD',
            adv_usd_mn < 5000, '1 tỷ đến 5 tỷ USD',
                               'Trên 5 tỷ USD')             AS liquidity_bucket,
    count()                                                 AS signal_count,
    round(100 * countIf(hit_date < stop_date) / count(), 1) AS hit_rate_pct,
    round(100 * countIf(stop_date <= hit_date
                        AND stop_date < toDate('2099-12-31')) / count(), 1) AS stopped_first_pct
FROM paths
GROUP BY liquidity_bucket
ORDER BY min(adv_usd_mn)