from solana.core.ez_token_prices_hourly

where recorded_hour between '{{From}}' and '{{To}}'

group by 1,2,3)

,

buy_token as ( select date(block_timestamp) as date, swapper,SWAP_TO_MINT, count(DISTINCT(tx_id)) as total_swap,

sum(swap_to_amount) as total_amounts

from solana.core.fact_swaps

where block_timestamp between '{{From}}' and '{{To}}'

and swap_to_mint in (select token_address from token_price)

group by 1,2,3)

,

buy_volume as ( select a.date, swapper, symbol as to_label, total_swap ,total_amounts, total_amounts*avg_price as volume

from buy_token a left outer join token_price b on a.date = b.date and swap_to_mint = token_address)

,

final_buy as ( select swapper, to_label , sum(total_swap) as total_buy ,sum(total_amounts) as buy_amount, sum(volume) as buy_volume

from buy_volume

group by 1,2)

,

sell_token as ( select date(block_timestamp) as date, swapper,SWAP_from_MINT, count(DISTINCT(tx_id)) as total_swap,

sum(swap_from_amount) as sell_amount

from solana.core.fact_swaps

where block_timestamp between '{{From}}' and '{{To}}'

and swap_from_mint in (select token_address from token_price)

group by 1,2,3)

,

sell_volume as ( select a.date, swapper, symbol as from_label, total_swap ,sell_amount, sell_amount*avg_price as volume

from sell_token a left outer join token_price b on a.date = b.date and swap_from_mint = token_address)

,

final_sell as ( select a.swapper, from_label, sum(a.total_swap) as total_sell ,sum(sell_amount)as sell_amounts, sum(a.volume) as sell_volume

from sell_volume a join buy_volume b on a.swapper = b.swapper and from_label = b.to_label

where a.date >= b.date

group by 1,2

,

final_2 as ( select a.swapper,from_label,to_label, total_buy, total_sell, buy_amount, sell_amounts,