SELECT

token as symbol,

id as token_contract,

date_trunc('day', recorded_hour) AS day,

AVG(close) AS price

FROM flow.core.fact_hourly_prices

WHERE recorded_hour > current_date - 91

GROUP BY symbol, token_contract, day

),

sells AS (

SELECT

trader AS user_address,

symbol,

swap_contract,

token_out_contract AS token_contract,

count(tx_id) AS n_sells,

sum(token_out_amount) AS token_sell_volume,

sum(token_out_amount * price) AS usd_sell_volume

FROM

flow.core.ez_swaps ds

LEFT JOIN daily_prices dp ON date_trunc('day', ds.block_timestamp) = dp.day

AND ds.token_out_contract = dp.token_contract

WHERE

block_timestamp > current_date - 91

GROUP BY

trader, swap_contract, symbol, token_out_contract

),

buys AS (

SELECT

trader AS user_address,

symbol,

swap_contract,

token_in_contract AS token_contract,

count(tx_id) AS n_buys,

sum(token_in_amount) AS token_buy_volume,