GameStop, Nov 2020 – Mar 2021: short interest, days to cover and the closing price at each settlement

WITH px AS
(
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS day,
           argMax(toFloat64(close), window_start) AS close_px
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'GME'
      AND window_start >= '2020-11-01 00:00:00'
      AND window_start < '2021-04-02 00:00:00'
      AND toHour(toTimeZone(window_start, 'America/New_York')) BETWEEN 9 AND 15
    GROUP BY day
),
si AS
(
    SELECT settlement_date,
           round(max(short_interest) / 1e6, 1) AS shares_short_m,
           round(max(days_to_cover), 1) AS days_to_cover
    FROM global_markets.stocks_short_interest
    WHERE ticker = 'GME'
      AND settlement_date >= '2020-11-01'
      AND settlement_date <= '2021-03-31'
    GROUP BY settlement_date
)
SELECT concat(monthName(si.settlement_date), ' ', toString(toDayOfMonth(si.settlement_date)), ', ', toString(toYear(si.settlement_date))) AS settlement_date,
       si.shares_short_m AS shares_short_m,
       si.days_to_cover AS days_to_cover,
       round(px.close_px, 2) AS gme_close
FROM si
INNER JOIN px ON si.settlement_date = px.day
ORDER BY si.settlement_date