SELECT

date_trunc('day', RECORDED_HOUR) AS hour,

avg(close) AS usd_price

FROM

flow.core.fact_hourly_prices

WHERE

TOKEN = 'Flow'

GROUP BY

1

),

swap_data AS (

SELECT

ez.trader,

count(DISTINCT ez.tx_id) AS num_swaps,

sum(ez.TOKEN_IN_AMOUNT * p.usd_price) AS total_volume

FROM

flow.core.ez_swaps AS ez

JOIN flow.core.fact_transactions AS ft ON ez.tx_id = ft.tx_id

JOIN price AS p ON date_trunc('day', ez.block_timestamp) = p.hour

WHERE

ft.PROPOSER = '0x55ad22f01ef568a1'

AND ft.PAYER = '0x55ad22f01ef568a1'

AND ez.block_timestamp >= '2023-01-01'::TIMESTAMP

GROUP BY

ez.trader

trader,

num_swaps,

total_volume

swap_data

ORDER BY

num_swaps DESC --total_volume DESC

LIMIT 5;