SELECT TX_ID, BLOCK_TIMESTAMP,SENDER, CURRENCY ,AMOUNT/POW (10, DECIMAL) AS AMOUNT, RECEIVER,

CASE

WHEN CURRENCY = 'uaxl' THEN 'AXL'

WHEN CURRENCY IN ('weth-wei', 'gravity0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2') THEN 'ETH'

WHEN CURRENCY = 'uatom' THEN 'ATOM'

WHEN CURRENCY = 'uaxl' THEN 'AXL'

WHEN CURRENCY IN ('uusdc', 'gravity0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48') THEN 'USDC'

WHEN CURRENCY = 'mkr-wei' THEN 'Maker'

WHEN CURRENCY = 'uusdt' THEN 'USDT'

WHEN CURRENCY = 'wbtc-satoshi' THEN 'BTC'

WHEN CURRENCY IN ('dai-wei', 'gravity0x6B175474E89094C44Da98b954EedeAC495271d0F') THEN 'DAI'

WHEN CURRENCY = 'dot-planck' THEN 'Polkadot'

WHEN CURRENCY = 'uluna' THEN 'Luna'

WHEN CURRENCY = 'uosmo' THEN 'OSMO'

WHEN CURRENCY = 'uusd' THEN 'uusd'

END AS Asset_Name

FROM axelar.core.fact_transfers

WHERE TRANSFER_TYPE = 'IBC_TRANSFER_OUT' AND RECEIVER ilike 'osmo%' AND TX_SUCCEEDED = 'TRUE' AND BLOCK_TIMESTAMP::DATE < CURRENT_DATE-1

),

DAILY_TRANSFERS AS (

SELECT BLOCK_TIMESTAMP::DATE AS DAYS, Asset_Name, SUM(AMOUNT) AS AMOUNT

FROM Transfers_To_OSMOSIS

GROUP BY DAYS, Asset_Name

),

WBTC_PRICE AS (

SELECT RECORDED_AT::DATE AS DAYS, AVG(PRICE) AS "Price($)", 'BTC' AS SYMBOL

FROM osmosis.core.dim_prices

WHERE SYMBOL = 'WBTC' AND DAYS BETWEEN '2022-03-25' AND CURRENT_DATE-1

GROUP BY DAYS, SYMBOL

),

WETH_PRICE AS (

SELECT RECORDED_AT::DATE AS DAYS, AVG(PRICE) AS "Price($)", 'ETH' AS SYMBOL