SELECT

DATE(recorded_hour) AS date1,

MEDIAN(price) AS token_price

FROM osmosis.price.ez_prices

WHERE symbol LIKE 'AXL'

GROUP BY 1

), tab1 AS (

SELECT

created_at,

id,

amount,

PARSE_JSON(data):call:receipt:from::STRING AS user,

PARSE_JSON(data):call:chain::STRING AS source_chain,

PARSE_JSON(data):call:returnValues:destinationChain::STRING AS destination_chain,

PARSE_JSON(fees):token:token_price:usd::FLOAT AS token_price1,

PARSE_JSON(fees):destination_native_token:token_price:usd::FLOAT AS destination_token_price_usd,

CASE

WHEN PARSE_JSON(fees):token:token_price:usd IS NOT NULL THEN PARSE_JSON(fees):token:token_price:usd::FLOAT

WHEN PARSE_JSON(data):approved:returnValues:symbol::STRING LIKE '%USDC%' THEN 1

WHEN PARSE_JSON(data):approved:returnValues:symbol::STRING LIKE '%USDT%' THEN 1

WHEN PARSE_JSON(data):approved:returnValues:symbol::STRING LIKE '%DAI%' THEN 1

WHEN PARSE_JSON(data):approved:returnValues:symbol::STRING LIKE '%MAI%' THEN 1

WHEN PARSE_JSON(data):call:returnValues:symbol::STRING LIKE '%USDC%' THEN 1

WHEN PARSE_JSON(data):call:returnValues:symbol::STRING LIKE '%USDT%' THEN 1

WHEN PARSE_JSON(data):call:returnValues:symbol::STRING LIKE '%DAI%' THEN 1

WHEN PARSE_JSON(data):call:returnValues:symbol::STRING LIKE '%MAI%' THEN 1

WHEN PARSE_JSON(data):call:returnValues:symbol::STRING LIKE 'AXL' THEN token_price

ELSE destination_token_price_usd

END AS token_price2

FROM axelar.axelscan.fact_gmp

LEFT OUTER JOIN tab0

ON date1 = DATE(created_at)

WHERE amount IS NOT NULL

AND simplified_status LIKE 'received'

AND token_price2 IS NOT NULL