3,256 answered market questions

WITH daily AS
(
    SELECT
        date,
        toFloat64(any(close)) AS close_px
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'AAPL'
      AND date >= '2022-01-03'
      AND date <  '2022-07-01'
    GROUP BY date
),
marked AS
(
    SELECT
        date,
        close_px,
        max(close_px) OVER (ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS peak_close
    FROM daily
),
widths AS
(
    SELECT arrayJoin([5, 8, 10, 15, 20]) AS trail_pct
)
SELECT
    concat(toString(w.trail_pct), '% trail')           AS label,
    formatDateTime(min(m.date), '%b %e, %Y')           AS first_trigger_day,
    dateDiff('day', toDate('2022-01-03'), min(m.date)) AS days_into_window,
    count()                                            AS closes_at_or_below_trail
FROM marked AS m
CROSS JOIN widths AS w
WHERE m.close_px <= m.peak_close * (1 - w.trail_pct / 100)
GROUP BY w.trail_pct
ORDER BY w.trail_pct ASC