konteks_tren

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(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, c,
           lagInFrame(o, 1)   OVER w AS p1o,
           lagInFrame(c, 1)   OVER w AS p1c,
           lagInFrame(c, 11)  OVER w AS p11c,
           leadInFrame(c, 1)  OVER w AS f1c,
           leadInFrame(c, 5)  OVER w AS f5c,
           leadInFrame(c, 20) OVER w AS f20c
    FROM harian
    WINDOW w AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 25 PRECEDING AND 20 FOLLOWING)
)
SELECT multiIf(100 * (b.p1c / b.p11c - 1) < -5, '1. sepuluh sesi sebelumnya turun lebih dari 5%',
               100 * (b.p1c / b.p11c - 1) <  0, '2. sepuluh sesi sebelumnya turun sampai 5%',
               100 * (b.p1c / b.p11c - 1) <  5, '3. sepuluh sesi sebelumnya naik sampai 5%',
                                                '4. sepuluh sesi sebelumnya naik lebih dari 5%') AS tren_bucket,
       count()                                         AS kejadian_count,
       round(100 * countIf(b.f1c  < b.c) / count(), 1) AS turun_1_sesi_pct,
       round(100 * countIf(b.f5c  < b.c) / count(), 1) AS turun_5_sesi_pct,
       round(100 * countIf(b.f20c < b.c) / count(), 1) AS turun_20_sesi_pct
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.p1c > b.p1o AND b.c < b.o AND b.o > b.p1c AND b.c < b.p1o
  AND b.p11c > 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)
GROUP BY tren_bucket
ORDER BY tren_bucket