P as(

select date_trunc('week',hour) as day, token_address, max(price) as price

from ethereum.price.ez_hourly_token_prices

where token_address = '0x418d75f65a02b3d53b2418fb8e1fe493759c7605'

group by 1,2),

A as (

select block_timestamp as time, decoded_log as data, tx_hash

from bsc.core.fact_decoded_event_logs

where CONTRACT_ADDRESS = '0x1e70972ec6c8a3fae3ac34c9f3818ec46eb3bd5d'

),

B as(

select time,

data:"trader" as trader,

data:"subject" as subject,

data:"isBuy" as action,

data:"ethAmount"/1e18 as volume,

data:"protocolEthAmount"/1e18 as revenue,

data:"supply" as supply,

data:"ticketAmount" as ticketAmount,

tx_hash

from A),

C as(

select date_trunc('week',time) as time,

sum(volume*price) as volume,

sum(revenue*price) as revenue,

count(DISTINCT trader) as trader,

count(DISTINCT tx_hash) as txns

from B as a

join P as b on date_trunc('week',a.time) = b.day

group by 1)