AAPL trades by venue, latest session: off-exchange first

WITH (
  SELECT max(toDate(sip_timestamp))
  FROM global_markets.stocks_trades
  WHERE ticker = 'AAPL' AND sip_timestamp >= now() - INTERVAL 7 DAY
) AS last_session
SELECT
  if(x.name = '', concat('Unmapped venue ', toString(t.exchange)), x.name) AS venue,
  multiIf(x.type = 'TRF', 'TRF', x.type = '', 'unmapped', x.type)          AS venue_type,
  round(count() / 1000, 1)                                                 AS trades_thousands,
  round(100 * count() / sum(count()) OVER (), 1)                           AS pct_of_trades
FROM global_markets.stocks_trades AS t
LEFT JOIN (
  SELECT id, name, type FROM global_markets.stocks_exchanges
  WHERE asset_class = 'stocks' AND locale = 'us'
) AS x ON t.exchange = x.id
WHERE t.ticker = 'AAPL'
  AND t.sip_timestamp >= now() - INTERVAL 7 DAY
  AND toDate(t.sip_timestamp) = last_session
GROUP BY venue, venue_type
ORDER BY venue_type = 'TRF' DESC, trades_thousands DESC