1WITH raw as (
2select markets.title, count(*) as count_trades, round(sum(trades.price*trades.count)/1000000,1) as total_trades_usd
3from @kalshi.markets.market_trades trades
4INNER JOIN @kalshi.markets.markets markets ON (trades.market_ticker=markets.ticker)
5INNER JOIN @kalshi.markets.events events ON (markets.event_ticker=events.event_ticker)
6WHERE events.category='Crypto' AND trades.created_time > now() - interval '7 days'
7GROUP BY ALL
8ORDER BY total_trades_usd DESC
9LIMIT 10
10)
11select title as market, count_trades as "# Trades", total_trades_usd as "USD (millions)"
12from raw
13order by total_trades_usd