Query editor
withorders as (select * from dune.gains.result_gtrade_all_orders_daily_view -- use view for now, refreshed every 2hrs-- query_3388490 -- gtrade.all.orders (all collaterals)),daily_volume as(select date_trunc('day', block_time) as day, sum(pnl * collateralPriceUsd) as pnl, sum(pnl_raw * collateralPriceUsd) as pnl_before_fees,sum(if(group_name in ('crypto', 'altcoins', 'crypto-degen'), position_size_dai * collateralPriceUsd * leverage, 0)) as crypto_volume,sum(if(group_name in ('crypto-degen'), position_size_dai * collateralPriceUsd * leverage, 0)) as crypto_degen_only_volume,sum(if(group_name like 'forex%', position_size_dai * collateralPriceUsd * leverage, 0)) as forex_volume,sum(if(group_name = 'stocks', position_size_dai * collateralPriceUsd * leverage, 0)) as stocks_volume,sum(if(group_name = 'indices', position_size_dai * collateralPriceUsd * leverage, 0)) as indices_volume,sum(if(group_name = 'commodities', position_size_dai * collateralPriceUsd * leverage, 0)) as commodities_volume,sum(position_size_dai * collateralPriceUsd * leverage) as daily_volume,sum(if(collateral = 'DAI', position_size_dai * collateralPriceUsd * leverage, 0)) as dai_daily_volume,sum(if(collateral = 'USDC', position_size_dai * collateralPriceUsd * leverage, 0)) as usdc_daily_volume,sum(if(collateral = 'BTCUSD', position_size_dai * collateralPriceUsd * leverage, 0)) as btcusd_daily_volume,sum(if(collateral = 'wETH', position_size_dai * collateralPriceUsd * leverage, 0)) as weth_daily_volume,sum(if(collateral = 'GNS', position_size_dai * collateralPriceUsd * leverage, 0)) as gns_daily_volume,sum(if(collateral = 'USDM', position_size_dai * collateralPriceUsd * leverage, 0)) as usdm_daily_volume,sum(if(blockchain = 'polygon', position_size_dai * collateralPriceUsd * leverage, 0)) as polygon_daily_volume,sum(if(blockchain = 'arbitrum', position_size_dai * collateralPriceUsd * leverage, 0)) as arbitrum_daily_volume,sum(if(blockchain = 'base', position_size_dai * collateralPriceUsd * leverage, 0)) as base_daily_volume,sum(if(blockchain = 'megaeth', position_size_dai * collateralPriceUsd * leverage, 0)) as megaeth_daily_volume,sum(if("type" = 'LIQ', position_size_dai * collateralPriceUsd * leverage, 0)) as "liq_volume",sum(if("type" = 'LIQ', position_size_dai * collateralPriceUsd, 0)) as "liq_collateral",sum(if("type" = 'LIQ' and group_name in ('crypto', 'altcoins', 'crypto-degen'), position_size_dai * collateralPriceUsd * leverage, 0)) as "crypto_liq_volume",sum(if("type" = 'LIQ' and group_name like 'forex%', position_size_dai * collateralPriceUsd * leverage, 0)) as "forex_liq_volume",sum(if("type" = 'LIQ' and group_name = 'stocks', position_size_dai * collateralPriceUsd * leverage, 0)) as "stocks_liq_volume",sum(if("type" = 'LIQ' and group_name = 'indices', position_size_dai * collateralPriceUsd * leverage, 0)) as "indices_liq_volume",sum(if("type" = 'LIQ' and group_name = 'commodities', position_size_dai * collateralPriceUsd * leverage, 0)) as "commodities_liq_volume",sum("project_fund" * collateralPriceUsd) as "project_fund",sum("dev_fund" * collateralPriceUsd) as "dev_fund",sum("referral" * collateralPriceUsd) as "referral",sum("nft_bots" * collateralPriceUsd) as "nft_bots",sum(if(collateral = 'DAI', "dai_stakers" * collateralPriceUsd, 0 )) as "dai_stakers",sum(if(collateral = 'USDC', "dai_stakers" * collateralPriceUsd, 0 )) as "usdc_stakers",sum(if(collateral = 'wETH', "dai_stakers" * collateralPriceUsd, 0 )) as "weth_stakers",sum(if(collateral = 'BTCUSD', "dai_stakers" * collateralPriceUsd, 0 )) as "btcusd_stakers",sum(if(collateral = 'USDM', "dai_stakers" * collateralPriceUsd, 0 )) as "usdm_stakers",sum("lp_stakers" * collateralPriceUsd) as "lp_stakers",sum("gns_stakers" * collateralPriceUsd) as "gns_stakers",sum("oc_fees" * collateralPriceUsd) as "oc_fees",sum(if(collateral = 'DAI', "gns_stakers" * collateralPriceUsd, 0 )) as "gns_stakers_dai",sum(if(collateral = 'USDC', "gns_stakers" * collateralPriceUsd, 0 )) as "gns_stakers_usdc",sum(if(collateral = 'wETH', "gns_stakers" * collateralPriceUsd, 0 )) as "gns_stakers_weth",sum(if(collateral = 'BTCUSD', "gns_stakers" * collateralPriceUsd, 0 )) as "gns_stakers_btcusd",sum(if(collateral = 'USDM', "gns_stakers" * collateralPriceUsd, 0 )) as "gns_stakers_usdm",-1 * sum(coalesce("rollover_fee", 0) * collateralPriceUsd) as "rollover_fee_neg",-1 * sum(coalesce("borrowing_fee", 0) * collateralPriceUsd) as "borrowing_fee_neg",sum(coalesce("rollover_fee", 0) * collateralPriceUsd) as "rollover_fee",sum(coalesce("borrowing_fee", 0) * collateralPriceUsd) as "borrowing_fee",sum(("project_fund" + "dev_fund" + "referral" + "nft_bots" + "dai_stakers" + "lp_stakers" + "gns_stakers" + "oc_fees") * collateralPriceUsd) as "all_fees",count(*) as trades,avg(position_size_dai * collateralPriceUsd) as avg_margin_size,avg(leverage) as avg_leveragefrom ordersgroup by 1)