SELECT

s.block_timestamp as timestamp,

s.symbol_out as symbol_token,

s.token_out as address_token,

s.amount_in as volume_base,

s.amount_out as volume_token,

iff(s.platform = 'uniswap-v3', (try_cast(split_part(s.pool_name, ' ', 2) as DECIMAL) / 1000000), 0.003) as fee_pct,

div0null(s.amount_in, s.amount_out) as price_with_fee

FROM ethereum.core.ez_dex_swaps AS s

WHERE s.token_in = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' -- WETH

AND s.platform IN ('uniswap-v3', 'uniswap-v2', 'sushiswap')

AND s.symbol_out NOT IN ('WETH', 'WBTC', 'USDT', 'USDC', 'ETH', 'DAI')

ORDER BY timestamp DESC

SELECT

DATE_TRUNC('minute', timestamp) AS ts_minute,

symbol_token,

address_token,

SUM(volume_token) as total_volume_token,

SUM(volume_base) as total_volume_base,

div0(sum(price_with_fee * volume_base), sum(volume_base)) as avg_price_with_fee,

div0(sum(fee_pct * volume_base), sum(volume_base)) as avg_fee_pct

FROM trades

GROUP BY address_token, symbol_token, ts_minute

ORDER BY ts_minute DESC

LIMIT 100000;