SELECT currency,

count(DISTINCT address) as user_no,

date_trunc(day, date) as time

from osmosis.core.fact_daily_balances join osmosis.core.dim_labels using (address)

where balance_type in ('liquid')

group by 1,3

order by 3

),

total as

SELECT sum(balance/pow(10,decimal)) as total_token,

date_trunc(day, date) as time1

from osmosis.core.fact_daily_balances join osmosis.core.dim_labels using (address)

where balance_type in ('liquid')

group by 2

order by 2

SELECT currency,

total_token/user_no as avg_token,

time

from wallets_no join total on time=time1

where currency in ('uosmo',

'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2', --ATOM

'ibc/BE1BB42D4BE3C30D50B68D7C41DB4DFCE9678E8EF8C539F6E6A9345048894FCC', --USTC

'ibc/49C2B2C444B7C5F0066657A4DBF19D676E0D185FF721CFD3E14FA253BCB9BC04',--ECH

'ibc/6AE98883D4D5D5FF9E50D7130F1305DA2FFA0C652D1DD9C123657C6B4EB2DF8A',--EVMOS

'ibc/41999DF04D9441DAC0DF5D8291DF4333FBCBA810FFD63FDCE34FDF41EF37B6F7',--CRBRUS

'ibc/46B44899322F3CD854D2D46DEEF881958467CDD4B3B10086DA49296BBED94BED',--JUNO

'ibc/AA1C80225BCA7B32ED1FC6ABF8B8E899BEB48ECDB4B417FD69873C6D715F97E7',--ASVT

'ibc/0EF15DF2F02480ADE0BB6E85D9EBB5DAEA2836D3860E9F97F9AADE4F57A31AA0' --LUNC

)

group by 1,2,3

order by 3