SELECT DISTINCT tx_hash

FROM base.core.fact_transactions

WHERE to_address LIKE '0x16613524e02ad97edfef371bc883f2f5d6c480a5'

), tab2 as (

SELECT

date(hour) as day,

token_address,

symbol,

decimals,

avg(price) as token_price

FROM base.price.ez_hourly_token_prices

GROUP BY 1,2,3,4

), tab3 as (

SELECT --*

block_timestamp,

DECODED_LOG:amount as raw_amount,

DECODED_LOG:reward as token1,

token_address,

symbol,

decimals,

token_price

FROM base.core.fact_decoded_event_logs

left outer JOIN tab2

on day = date(block_timestamp)

AND token_address = DECODED_LOG:reward

WHERE tx_hash in (SELECT * FROM tab1)

AND event_name LIKE 'ClaimRewards'

), tab4 as (

tab3.*,

((RAW_AMOUNT / power(10, CASE WHEN decimals IS null THEN 0 ELSE decimals END)) * CASE

WHEN token1 in ('0x65a2508c429a6078a7bc2f7df81ab575bd9d9275', '0x4621b7a9c75199271f773ebd9a499dbd165c3191', '0xbf1aea8670d2528e08334083616dd9c5f3b087ae', '0xc142171b138db17a1b7cb999c44526094a4dae05', '0x4a3a6dd60a34bb2aba60d73b4c88315e9ceb6a3d') THEN 1/power(10, 18)

WHEN token1 LIKE '0xb79dd08ea68a908a97220c76d19a6aa9cbde4376' THEN 1/power(10,6)

WHEN token1 LIKE '0x9483ab65847a447e36d21af1cab8c87e9712ff93' THEN 1/power(10,9)

WHEN token1 LIKE '0x940181a94a35a4569e4529a3cdfb74e38fd98631' THEN areo_price/power(10,18)