Query editor
/*Trading stats (after 13 Jun 2025) uses orders table and not trades.*/withorders AS(SELECT * FROM query_5255724),daily_volume AS(SELECTdate_trunc('day', executed_at) as day,-- Volumesum(notional) as daily_volume,sum(if(pair_group = 'crypto', notional, 0)) as crypto_volume,sum(if(pair_group = 'forex', notional, 0)) as forex_volume,sum(if(pair_group = 'commodities', notional, 0)) as commodities_volume,sum(if(pair_group = 'indices', notional, 0)) as indices_volume,sum(if(pair_group = 'stocks', notional, 0)) as stocks_volume,sum(if(pair_group = 'etf', notional, 0)) as etf_volume,-- Feessum(dev_opening_fee) as dev_fee,sum(vault_opening_fee) as vault_opening_fee,sum(vault_liquidation_fee) as vault_liq_fee,sum(rollover_fee) as rollover_fee,sum(oracle_fee) as oracle_fee,sum(price_impact_fee) as price_impact_fee,sum(dev_opening_fee + vault_opening_fee + vault_liquidation_fee + rollover_fee + oracle_fee + price_impact_fee) as all_fees,count(distinct trade_id) as n_tradesFROM ordersgroup by 1),days as (select * from UNNEST(sequence(date('2024-08-25'), date(cast(now() as timestamp)), interval '1' day)) as day(day)),open_interest AS(SELECT * FROM query_5273105 ORDER BY 1),daily_users AS (SELECT * FROM query_4065690),daily_stats AS (SELECTa.*,b.crypto_oi, b.forex_oi, b.commodities_oi, b.indices_oi, b.stocks_oi, b.etf_oi,avg(a.daily_volume) OVER (ORDER BY a.day ROWS BETWEEN 6 PRECEDING AND 0 FOLLOWING) as "7_days_moving_average",avg(a.daily_volume) OVER (ORDER BY a.day ROWS BETWEEN 29 PRECEDING AND 0 FOLLOWING) as "30_days_moving_average",sum(a.daily_volume) OVER (ORDER BY a.day) AS cumulative_volume,sum(dev_fee) OVER (order by a.day) as cumulative_dev_fee,sum(oracle_fee) OVER (order by a.day) as cumulative_oracle_fee,sum(vault_liq_fee) OVER (order by a.day) as cumulative_vault_liq_fee,sum(vault_opening_fee) OVER (order by a.day) as cumulative_vault_opening_fee,sum(rollover_fee) OVER (order by a.day) as cumulative_rollover_fee,sum(price_impact_fee) OVER (order by a.day) as cumulative_price_impact_fee,sum(all_fees) OVER (order by a.day) as cumulative_all_fees,avg(all_fees) OVER (ORDER BY a.day ROWS BETWEEN 6 PRECEDING AND 0 FOLLOWING) AS "7D MA Fees",avg(all_fees) OVER (ORDER BY a.day ROWS BETWEEN 29 PRECEDING AND 0 FOLLOWING) AS "30D MA Fees",sum(n_trades) over (order by a.day) as cumulative_trades,avg(c.users) OVER (ORDER BY a.day ROWS BETWEEN 29 PRECEDING AND 0 FOLLOWING) AS "30d-ma Traders",c.recurring_users, c.new_users, c.cumulative_new_users,-- Last 24h & 7d metrics:-- fees(select sum(dev_opening_fee + vault_opening_fee + vault_liquidation_fee + rollover_fee + oracle_fee + price_impact_fee) from orders where executed_at >= CURRENT_TIMESTAMP - INTERVAL '24' hour ) as fees_last_24h,(select sum(dev_opening_fee + vault_opening_fee + vault_liquidation_fee + rollover_fee + oracle_fee + price_impact_fee) from orders where executed_at >= CURRENT_TIMESTAMP - INTERVAL '7' day ) as fees_last_7d,-- volume