select

date_trunc(day, hour) as day,

token_address,

avg(price) as avg_price

from

solana.price.ez_prices_hourly

group by

1,2

),

fee as (

select

date_trunc(week, block_timestamp) as week,

sum(amount * avg_price) as Fee

from

solana.core.fact_transfers

join price on fact_transfers.mint = price.token_address

and date_trunc(day, fact_transfers.block_timestamp) = price.day

where

tx_to = 'ZG98FUCjb8mJ824Gbs6RsgVmr1FhXb2oNiJHa2dwmPd'

and block_timestamp :: date >= '2023-08-17'

and mint not in ( --Bug Price

'2TLDx5M7Z9pfUPbHAboYeTEq6ShzaGhnCwWkfvVyPFyD',

'Fcc9VgmUHZoGewBqj7PfwagjRkLXUjUniD3wRErMm9XV',

'3FrSmUQQPjjdcWFWdHdoY974k2ZBwRT673eYFkhgkkWK'

)

group by

1

week,

Fee as Revenue,

sum(Revenue) over(order by week) as Total_Revenue

fee