with luna_price as(

select

date_trunc('day',RECORDED_HOUR) as date,

avg(CLOSE) as price

from crosschain.core.fact_hourly_prices

where ID ilike 'terra-luna-2'

group by 1

),

main as(select

date_trunc('week',BLOCK_TIMESTAMP) as date,

from_amount,

from_currency,

tx_id,

from terra.core.ez_swaps a

where FROM_currency in ('ibc/B3504E092456BA618CC28AC671A71FB08C6CA0FD0BE7C8A5B5A3E2DD933CC9E4','terra1uc3r74qg44csdrl8hrm5muzlue9gf7umgkyv569pgazh7tudpr4qdtgqh6' --usdc

,'ibc/CBF67A2BCF6CAE343FDF251E510C8E18C361FC02B23430C121116E0811835DEF','terra1rcmvfsn77pd6m04ctqj3wcu66pvrw9p265cdl72w4zarfup2rv7qjxhkzl') --usdt

and TX_SUCCEEDED='TRUE'

group by 1,2,3,4,5)

select a.date,

case

when FROM_currency in ('ibc/B3504E092456BA618CC28AC671A71FB08C6CA0FD0BE7C8A5B5A3E2DD933CC9E4','terra1uc3r74qg44csdrl8hrm5muzlue9gf7umgkyv569pgazh7tudpr4qdtgqh6') then 'USDC'

when FROM_currency in ('ibc/CBF67A2BCF6CAE343FDF251E510C8E18C361FC02B23430C121116E0811835DEF','terra1rcmvfsn77pd6m04ctqj3wcu66pvrw9p265cdl72w4zarfup2rv7qjxhkzl') then 'USDT'

end as stable_coins,

count(DISTINCT tx_id) as transactions,

count(DISTINCT trader) as users,

sum(from_amount/1e6) as volume_luna,

avg(from_amount/1e6) as avg_volume_luna,

sum(from_amount/1e6*price) as volume_usd,

avg(from_amount/1e6*price) as avg_volume_usd,

sum(volume_luna) over (order by a.date) as cum_volume_luna,

sum(volume_usd) over (order by a.date) as cum_volume_usd

from main a

join luna_price b on a.date=b.date