Today's top-3 crowding leaders, traced back eight settlements

WITH dates AS (
    SELECT DISTINCT settlement_date AS d
    FROM global_markets.stocks_short_interest
    ORDER BY d DESC
    LIMIT 8
),
top3 AS (
    SELECT ticker, row_number() OVER (ORDER BY days_to_cover DESC, ticker) AS rank
    FROM global_markets.stocks_short_interest
    WHERE settlement_date = (SELECT max(d) FROM dates)
      AND avg_daily_volume >= 5000000
      AND days_to_cover IS NOT NULL
      AND ticker NOT IN ('SPCX')
    ORDER BY days_to_cover DESC, ticker
    LIMIT 3
)
SELECT toString(settlement_date) AS settlement_date,
       maxIf(round(days_to_cover, 1), ticker = (SELECT ticker FROM top3 WHERE rank = 1)) AS current_leader_dtc,
       maxIf(round(days_to_cover, 1), ticker = (SELECT ticker FROM top3 WHERE rank = 2)) AS second_place_dtc,
       maxIf(round(days_to_cover, 1), ticker = (SELECT ticker FROM top3 WHERE rank = 3)) AS third_place_dtc
FROM global_markets.stocks_short_interest
WHERE ticker IN (SELECT ticker FROM top3)
  AND settlement_date IN (SELECT d FROM dates)
GROUP BY settlement_date
ORDER BY settlement_date