pola_vs_kontrol

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
harian AS (
    SELECT ticker, date,
           argMax(toFloat64(open),  _ingest_time) AS o,
           argMax(toFloat64(high),  _ingest_time) AS h,
           argMax(toFloat64(low),   _ingest_time) AS l,
           argMax(toFloat64(close), _ingest_time) AS c
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('AAPL', 'MSFT', 'JPM', 'JNJ', 'KO', 'PG', 'WMT', 'XOM')
      AND date BETWEEN '2010-01-01' AND '2026-03-31'
    GROUP BY ticker, date
),
pecahan AS (
    SELECT ticker, groupArray(execution_date) AS tanggal
    FROM global_markets.stocks_splits
    WHERE ticker IN ('AAPL', 'MSFT', 'JPM', 'JNJ', 'KO', 'PG', 'WMT', 'XOM')
      AND execution_date BETWEEN '2009-11-01' AND '2026-05-31'
    GROUP BY ticker
),
bars AS (
    SELECT ticker, date, o, h, l, c,
           lagInFrame(o, 1)   OVER w  AS p1o,
           lagInFrame(c, 1)   OVER w  AS p1c,
           lagInFrame(o, 2)   OVER w  AS p2o,
           lagInFrame(c, 2)   OVER w  AS p2c,
           leadInFrame(c, 1)  OVER w  AS f1c,
           leadInFrame(c, 5)  OVER w  AS f5c,
           leadInFrame(c, 20) OVER w  AS f20c,
           avg(c)             OVER w2 AS ma10
    FROM harian
    WINDOW w  AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 20 PRECEDING AND 20 FOLLOWING),
           w2 AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 10 PRECEDING AND 1 PRECEDING)
),
sesi AS (
    SELECT b.c AS c, b.f1c AS f1c, b.f5c AS f5c, b.f20c AS f20c,
           arrayConcat(
               ['Semua sesi (kontrol)'],
               if(b.p1c > b.p1o AND b.c < b.o AND b.o > b.p1c AND b.c < b.p1o, ['Bearish engulfing'], []),
               if(b.p1c > b.p1o AND b.c < b.o AND b.o > b.p1c AND b.c < (b.p1o + b.p1c) / 2 AND b.c > b.p1o, ['Dark cloud cover'], []),
               if(b.h > b.l AND (b.h - greatest(b.o, b.c)) >= 0.55 * (b.h - b.l) AND abs(b.c - b.o) <= 0.35 * (b.h - b.l) AND (least(b.o, b.c) - b.l) <= 0.15 * (b.h - b.l) AND b.c > b.ma10, ['Shooting star'], []),
               if(b.h > b.l AND (least(b.o, b.c) - b.l) >= 0.55 * (b.h - b.l) AND abs(b.c - b.o) <= 0.35 * (b.h - b.l) AND (b.h - greatest(b.o, b.c)) <= 0.15 * (b.h - b.l) AND b.c > b.ma10, ['Hanging man'], []),
               if(b.p2c > b.p2o AND abs(b.p1c - b.p1o) <= 0.4 * (b.p2c - b.p2o) AND least(b.p1o, b.p1c) >= b.p2c AND b.c < b.o AND b.c < (b.p2o + b.p2c) / 2, ['Evening star'], []),
               if(b.c < b.o AND b.p1c < b.p1o AND b.p2c < b.p2o AND b.c < b.p1c AND b.p1c < b.p2c AND b.o <= b.p1o AND b.o >= b.p1c AND b.p1o <= b.p2o AND b.p1o >= b.p2c, ['Three black crows'], [])
           ) AS pola
    FROM bars AS b
    LEFT JOIN pecahan AS s ON s.ticker = b.ticker
    WHERE b.date BETWEEN '2010-03-01' AND '2025-12-31'
      AND b.p2o > 0 AND b.p2c > 0 AND b.ma10 > 0
      AND b.f1c > 0 AND b.f5c > 0 AND b.f20c > 0
      AND NOT arrayExists(d -> d > b.date - 30 AND d <= b.date + 45, s.tanggal)
),
terbuka AS (
    SELECT arrayJoin(pola) AS nama_pola, c, f1c, f5c, f20c FROM sesi
),
ringkas AS (
    SELECT nama_pola,
           count()                                     AS kejadian_count,
           round(100 * countIf(f1c  < c) / count(), 1) AS turun_1_sesi_pct,
           round(100 * countIf(f5c  < c) / count(), 1) AS turun_5_sesi_pct,
           round(100 * countIf(f20c < c) / count(), 1) AS turun_20_sesi_pct
    FROM terbuka
    GROUP BY nama_pola
)
SELECT nama_pola, kejadian_count, turun_1_sesi_pct, turun_5_sesi_pct, turun_20_sesi_pct,
       round(turun_20_sesi_pct - (SELECT turun_20_sesi_pct FROM ringkas WHERE nama_pola = 'Semua sesi (kontrol)'), 1) AS selisih_20_sesi_pct
FROM ringkas
ORDER BY nama_pola