WITH users_v2 as (

SELECT BLOCK_TIMESTAMP as time, TX_HASH from ethereum.compound.ez_borrows

UNION

SELECT BLOCK_TIMESTAMP as time, TX_HASH from ethereum.compound.ez_deposits

UNION

SELECT BLOCK_TIMESTAMP as time, TX_HASH from ethereum.compound.ez_liquidations

UNION

SELECT BLOCK_TIMESTAMP as time, TX_HASH from ethereum.compound.ez_redemptions

UNION

SELECT BLOCK_TIMESTAMP as time, TX_HASH from ethereum.compound.ez_repayments

),

gas as (

select block_timestamp,TX_HASH, tx_fee as gas_fee

from ethereum.core.fact_transactions

where tx_hash in (

select TX_HASH from users_v2

)

),

prices as

(

select date_trunc('hour' , block_timestamp) as hr,

avg(LATEST_ANSWER_UNADJ/power(10,8)) as price

from ethereum.chainlink.ez_oracle_feeds

where feed_name = 'ETH / USD'

group by 1

)

select date(date_trunc('day',g.block_timestamp)) as date , sum(gas_fee*p.price) as total_fee_usd , sum(gas_fee) as total_fee_eth , count(DISTINCT tx_hash) as tx_count, 'compound' as coin

from gas g

left join prices as p on p.hr = date_trunc('hour' , g.block_timestamp)

-- left join osmosis.core.dim_prices p on date_trunc('hour',block_timestamp) = p.RECORDED_AT and p.symbol = 'weth' and p.provider = 'coin gecko'

group by 1

order by 1 asc