aapl_trace

WITH
on_file AS
(
    SELECT
        execution_date,
        any(split_from) AS from_shares,
        any(split_to)   AS to_shares
    FROM global_markets.stocks_splits
    WHERE ticker = 'AAPL'
      AND execution_date <= today()
    GROUP BY execution_date
),
restated AS
(
    SELECT
        d.date AS date,
        any(toFloat64(d.close)) * arrayProduct(groupArray(if(s.execution_date > d.date, toFloat64(s.to_shares) / toFloat64(s.from_shares), 1.0))) AS raw_close
    FROM
    (
        SELECT date, close
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'AAPL'
          AND date >= '2014-01-01'
          AND date <  '2021-01-01'
    ) AS d
    CROSS JOIN on_file AS s
    GROUP BY d.date
)
SELECT
    toString(toStartOfMonth(date))                AS month,
    formatDateTime(toStartOfMonth(date), '%b %Y') AS period_label,
    round(max(raw_close), 2)                      AS highest_close,
    round(min(raw_close), 2)                      AS lowest_close
FROM restated
GROUP BY toStartOfMonth(date)
ORDER BY toStartOfMonth(date)