SELECT

TOKEN_ADDRESS,

DATE_TRUNC('day', hour) AS price_date,

SYMBOL,

NAME,

DECIMALS,

AVG(PRICE) AS avg_daily_price

FROM ronin.price.ez_prices_hourly

GROUP BY

TOKEN_ADDRESS,

DATE_TRUNC('day', hour),

SYMBOL,

NAME,

DECIMALS

),

transfer_from_ronin AS (

SELECT

t.tx_hash,

MAX(CASE WHEN t.event_name = 'Transfer' THEN COALESCE(t.decoded_log:_to, t.decoded_log:to) END) AS bridger,

MAX(CASE WHEN t.event_name = 'Transfer' THEN COALESCE(t.decoded_log:_value, t.decoded_log:value) END) AS transfer_value_raw,

MAX(CASE WHEN t.event_name = 'Transfer' THEN t.contract_address END) AS contract_address,

MAX(DATE_TRUNC('day', t.block_timestamp)) AS event_date

FROM ronin.core.ez_decoded_event_logs t

WHERE t.event_name IN ('Transfer', 'Deposited')

AND t.ORIGIN_FUNCTION_SIGNATURE = '0xb9afa177'

AND (

(t.event_name = 'Deposited'

AND t.decoded_log:anonymous_1.mainchain.chainId = 1

AND t.decoded_log:anonymous_1.ronin.chainId = 2020)

OR (t.event_name = 'Transfer'

AND COALESCE(t.decoded_log:_from, t.decoded_log:from) = '0x0cf8ff40a508bdbc39fbe1bb679dcba64e65c7df')

)

AND t.contract_address NOT IN (

'0xf083289535052e8449d69e6dc41c0ae064d8e3f6',

'0x241a81fc0d6692707dad2b5025a3a7cf2cf25acf',