with

'0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' as quote_token_addr,

q1 as (

select

timestamp as ts,

block_number,

if(token_sold_address = quote_token_addr, token_bought_address, token_sold_address) as token_address,

if(token_sold_address = quote_token_addr, token_bought_symbol, token_sold_symbol) as token_symbol,

if(token_sold_address = quote_token_addr, 1 / price, price) as token_price

from defi_trades_ethereum_mainnet_v1

where timestamp > now() - interval 7 day

and (token_sold_address = quote_token_addr or token_bought_address = quote_token_addr)

and price > 0

),

q2 as (

select

any(ts) as ts,

block_number as block_number,

token_address as token_address,

any(token_symbol) as token_symbol,

min(token_price) as min_price,

max(token_price) as max_price,

avg(token_price) as avg_price,

count(*) as num_trades

from q1

group by token_address, block_number

),

q3 as (

select

date_trunc('minute', ts) as ts,

min(block_number) as min_block_number,

max(block_number) as max_block_number,

token_address,

any(token_symbol) as token_symbol,

min(min_price) as min_price,

max(max_price) as max_price,

avg(avg_price) as avg_price,

sum(num_trades) as num_trades

from q2

group by token_address, ts

)

select

*

from q3

where token_symbol = 'USDC'

order by ts desc

limit 100