direccion

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
reportes AS
(
    SELECT DISTINCT filing_date AS fecha_reporte
    FROM global_markets.stocks_8k_text
    WHERE ticker = 'NKE'
      AND form_type = '8-K'
      AND positionCaseInsensitive(items_text, 'Results of Operations') > 0
      AND filing_date >= '2021-08-01'
    ORDER BY fecha_reporte DESC
    LIMIT 12
),
barras AS
(
    SELECT
        date,
        toFloat64(close) AS cierre,
        any(toFloat64(close)) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS cierre_previo
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'NKE'
      AND date >= '2021-01-01'
),
sesiones AS
(
    SELECT
        r.fecha_reporte AS fecha_reporte,
        min(b.date)     AS fecha_sesion
    FROM reportes AS r
    CROSS JOIN barras AS b
    WHERE b.date > r.fecha_reporte
    GROUP BY r.fecha_reporte
),
movs AS
(
    SELECT
        (b.cierre / b.cierre_previo - 1) * 100 AS variacion,
        toUInt32(toUnixTimestamp(b.date))      AS semilla
    FROM sesiones AS s
    INNER JOIN barras AS b ON b.date = s.fecha_sesion
),
grupos AS
(
    SELECT arrayJoin(['Todos los trimestres', 'Sesiones al alza', 'Sesiones a la baja']) AS bucket
)
SELECT
    bucket,
    count()                                                       AS trimestres,
    round(quantileDeterministic(0.5)(abs(variacion), semilla), 2) AS mediana_abs_pct,
    round(max(abs(variacion)), 2)                                 AS maximo_abs_pct
FROM
(
    SELECT
        g.bucket AS bucket,
        indexOf(['Todos los trimestres', 'Sesiones al alza', 'Sesiones a la baja'], g.bucket) AS orden,
        m.variacion AS variacion,
        m.semilla   AS semilla
    FROM grupos AS g
    CROSS JOIN movs AS m
    WHERE g.bucket = 'Todos los trimestres'
       OR (g.bucket = 'Sesiones al alza'   AND m.variacion > 0)
       OR (g.bucket = 'Sesiones a la baja' AND m.variacion < 0)
)
GROUP BY bucket, orden
ORDER BY orden