date(recorded_at) as day,

avg(price) as price

from

osmosis.core.dim_prices

where

symbol like '%ATOM%'

and day >= dateadd(day, -7, getdate())

group by day

order by day)

select

date(tran.block_timestamp) as date,

count (distinct tx_id) as no_of_atom_txns,

count (distinct SENDER) as no_of_senders,

count (distinct RECEIVER) as no_of_receivers,

round(sum(amount/pow(10,5)),2) as tot_atom_volume,

round(sum((amount/pow(10,5))*price),2) as tot_atom_volume_usd,

round(avg((amount/pow(10,5))*price),2) as avg_atom_volume_usd,

round(avg(amount/pow(10,5)),2) as avg_atom_volume

from osmosis.core.fact_transfers tran

join price_dtls prc on date(tran.block_timestamp) = prc.day

where

currency = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2'

and

block_timestamp >= dateadd(day, -7, getdate())

and

tx_status = 'SUCCEEDED'

group by date

order by date desc