(SELECT

RAW_METADATA[0]['denom'] AS Curr,

DECIMAL

FROM

osmosis.core.dim_tokens

WHERE

RAW_METADATA[0]['denom'] IS NOT NULL

),

PRICE AS

(

SELECT

RECORDED_HOUR::dATE AS Date,

CURRENCY ,

SYMBOL ,

AVG (PRICE) AS Price ,

AVG(DECIMAL) AS DECIMAL

FROM

osmosis.price.ez_prices A LEFT JOIN TOKENS B ON A.CURRENCY=B.Curr

GROUP BY 1,2,3

)

SELECT

COUNT(*) AS Number,

A.CURRENCY ,

SYMBOL ,

SUM(CASE WHEN SYMBOL != 'USDY' THEN (Price * AMOUNT) / POW(10 ,B.DECIMAL ) ELSE (Price * AMOUNT) / POW(10 ,18 ) END ) AS Volume_usd ,

AVG(CASE WHEN SYMBOL != 'USDY' THEN (Price * AMOUNT) / POW(10 ,B.DECIMAL ) ELSE (Price * AMOUNT) / POW(10 ,18 ) END) AS AVG_Volume ,

Median(CASE WHEN SYMBOL != 'USDY' THEN (Price * AMOUNT) / POW(10 ,B.DECIMAL ) ELSE (Price * AMOUNT) / POW(10 ,18 ) END) AS Median_Volume

FROM

osmosis.core.fact_transfers A LEFT JOIN PRICE B ON A.CURRENCY=B.CURRENCY AND A.BLOCK_TIMESTAMP::DATE=B.DATE

WHERE

BLOCK_TIMESTAMP > CURRENT_DATE - 30

AND

TX_SUCCEEDED