select hour, symbol, case when symbol='PEPE' and price >0.0001 then 0.00001 else price end as price_usd

from crosschain.price.ez_prices_hourly

where symbol in ('NEAR','BRETT','AAVE','AURORA','BTC','DOGE','ETH','USDT','USDC','UNI','LINK','MOG','ARB','PEPE','SHIB','SOL')

),

info as (

SELECT

block_timestamp,

tx_hash,

TRY_PARSE_JSON(clean_log):"data"[0]:"amounts"[0] AS amount_in,

TRY_PARSE_JSON(clean_log):"data"[0]:"token_ids"[0] AS token_id_in,

TRY_PARSE_JSON(clean_log):"data"[1]:"amounts"[0] AS amount_out,

TRY_PARSE_JSON(clean_log):"data"[1]:"token_ids"[0] AS token_id_out,

CASE

WHEN TRY_PARSE_JSON(clean_log):"data"[0]:"token_ids"[0] = 'nep141:wrap.near' THEN 'NEAR'

WHEN TRY_PARSE_JSON(clean_log):"data"[0]:"token_ids"[0] = 'nep141:base-0x532f27101965dd16442e59d40670faf5ebb142e4.omft.near' THEN 'BRETT'

WHEN TRY_PARSE_JSON(clean_log):"data"[0]:"token_ids"[0] = 'nep141:base-0x833589fcd6edb6e08f4c7c32d4f71b54bda02913.omft.near' THEN 'USDC'

WHEN TRY_PARSE_JSON(clean_log):"data"[0]:"token_ids"[0] = 'nep141:base.omft.near' THEN 'ETH'

WHEN TRY_PARSE_JSON(clean_log):"data"[0]:"token_ids"[0] = 'nep141:17208628f84f5d6ad33f0da3bbbeb27ffcb398eac501a31bd6ad2011e36133a1' THEN 'USDC'

WHEN TRY_PARSE_JSON(clean_log):"data"[0]:"token_ids"[0] = 'nep141:aaaaaa20d9e0e2461697782ef11675f668207961.factory.bridge.near' THEN 'AURORA'

WHEN TRY_PARSE_JSON(clean_log):"data"[0]:"token_ids"[0] = 'nep141:arb-0xaf88d065e77c8cc2239327c5edb3a432268e5831.omft.near' THEN 'USDC'

WHEN TRY_PARSE_JSON(clean_log):"data"[0]:"token_ids"[0] = 'nep141:arb-0xfc5a1a6eb076a2c7ad06ed22c90d7e710e35ad0a.omft.near' THEN 'GMX'

WHEN TRY_PARSE_JSON(clean_log):"data"[0]:"token_ids"[0] = 'nep141:arb-0xfd086bc7cd5c481dcc9c85ebe478a1c0b69fcbb9.omft.near' THEN 'USDT'

WHEN TRY_PARSE_JSON(clean_log):"data"[0]:"token_ids"[0] = 'nep141:arb-0x912ce59144191c1204e64559fe8253a0e49e6548.omft.near' THEN 'ARB'

WHEN TRY_PARSE_JSON(clean_log):"data"[0]:"token_ids"[0] = 'nep141:arb.omft.near' THEN 'ETH'

WHEN TRY_PARSE_JSON(clean_log):"data"[0]:"token_ids"[0] = 'nep141:aurora' THEN 'AURORA'

WHEN TRY_PARSE_JSON(clean_log):"data"[0]:"token_ids"[0] = 'nep141:btc.omft.near' THEN 'BTC'

WHEN TRY_PARSE_JSON(clean_log):"data"[0]:"token_ids"[0] = 'nep141:doge.omft.near' THEN 'DOGE'

WHEN TRY_PARSE_JSON(clean_log):"data"[0]:"token_ids"[0] = 'nep141:eth-0x1f9840a85d5af5bf1d1762f925bdaddc4201f984.omft.near' THEN 'UNI'

WHEN TRY_PARSE_JSON(clean_log):"data"[0]:"token_ids"[0] = 'nep141:eth-0x7fc66500c84a76ad7e9c93437bfc5ac33e2ddae9.omft.near' THEN 'AAVE'

WHEN TRY_PARSE_JSON(clean_log):"data"[0]:"token_ids"[0] = 'nep141:eth-0x95ad61b0a150d79219dcf64e1e6cc01f0b64c4ce.omft.near' THEN 'SHIB'

WHEN TRY_PARSE_JSON(clean_log):"data"[0]:"token_ids"[0] = 'nep141:eth-0x514910771af9ca656af840dff83e8264ecf986ca.omft.near' THEN 'LINK'

WHEN TRY_PARSE_JSON(clean_log):"data"[0]:"token_ids"[0] = 'nep141:eth-0x6982508145454ce325ddbe47a25d4ec3d2311933.omft.near' THEN 'PEPE'

WHEN TRY_PARSE_JSON(clean_log):"data"[0]:"token_ids"[0] = 'nep141:eth-0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48.omft.near' THEN 'USDC'

WHEN TRY_PARSE_JSON(clean_log):"data"[0]:"token_ids"[0] = 'nep141:eth-0xaaee1a9723aadb7afa2810263653a34ba2c21c7a.omft.near' THEN 'MOG'

WHEN TRY_PARSE_JSON(clean_log):"data"[0]:"token_ids"[0] = 'nep141:eth-0xdac17f958d2ee523a2206206994597c13d831ec7.omft.near' THEN 'USDT'