move_stats

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
reports AS
(
    SELECT DISTINCT filing_date AS report_date
    FROM global_markets.stocks_8k_text
    WHERE ticker = 'AAPL'
      AND startsWith(form_type, '8-K')
      AND filing_date >= '2023-09-01'
      AND (positionCaseInsensitive(items_text, 'Results of Operations') > 0
           OR positionCaseInsensitive(items_text, 'Item 2.02') > 0)
),
bars AS
(
    SELECT
        date,
        toFloat64(any(open))  AS open_px,
        toFloat64(any(close)) AS close_px
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'AAPL'
      AND date >= '2023-08-01'
    GROUP BY date
),
seq AS
(
    SELECT date, open_px, close_px, row_number() OVER (ORDER BY date) AS n
    FROM bars
),
per_report AS
(
    SELECT
        d0.date                                  AS report_date,
        formatDateTime(d0.date, '%b %e, %Y')     AS report_label,
        abs(d1.open_px / d0.close_px - 1) * 100  AS abs_gap_pct,
        abs(d1.close_px / d0.close_px - 1) * 100 AS abs_full_day_pct
    FROM seq AS d0
    INNER JOIN seq AS d1 ON d1.n = d0.n + 1
    INNER JOIN reports AS r ON r.report_date = d0.date
),
stats AS
(
    SELECT
        round(quantileDeterministic(0.5)(abs_gap_pct, toUInt64(toUnixTimestamp(report_date))), 2)      AS median_gap,
        round(quantileDeterministic(0.5)(abs_full_day_pct, toUInt64(toUnixTimestamp(report_date))), 2) AS median_day,
        round(max(abs_gap_pct), 2)                                                                     AS max_gap,
        round(max(abs_full_day_pct), 2)                                                                AS max_day,
        argMax(report_label, abs_gap_pct)                                                              AS max_gap_report,
        argMax(report_label, abs_full_day_pct)                                                         AS max_day_report
    FROM per_report
)
SELECT
    ['اوورنائٹ گیپ', 'پورے دن کی حرکت'][idx] AS label,
    [median_gap, median_day][idx]            AS median_abs_pct,
    [max_gap, max_day][idx]                  AS largest_abs_pct,
    [max_gap_report, max_day_report][idx]    AS largest_on
FROM stats
ARRAY JOIN [1, 2] AS idx