select BLOCK_HOUR, ASSET_ID, ASSET_NAME, PRICE_USD

from algorand.core.ez_price_pool_balances

union all

select BLOCK_HOUR, ASSET_ID, ASSET_NAME, PRICE_USD

from algorand.core.ez_price_swap),

algo_transfers as (

select BLOCK_TIMESTAMP, TX_ID, TX_SENDER as user, case when DECIMALS>=0 then AMOUNT/pow(10,DECIMALS) else AMOUNT end as token_amount,

token_amount*b.PRICE_USD as usd_amount, FEE, FEE*c.PRICE_USD as FEE_USD

from algorand.core.ez_transfer a

left join algo_prices b

on a.ASSET_ID=b.ASSET_ID and date_trunc(hour, BLOCK_TIMESTAMP)=date_trunc(hour, b.BLOCK_HOUR)

left join algo_prices c

on date_trunc(hour, BLOCK_TIMESTAMP)=date_trunc(hour, c.BLOCK_HOUR) and c.ASSET_ID=0

where BLOCK_TIMESTAMP>=CURRENT_DATE-{{Days}} and USD_AMOUNT is not null),

algo_txs as (

select BLOCK_ID, BLOCK_TIMESTAMP, TX_ID, TX_SENDER as user, FEE, FEE*b.PRICE_USD as FEE_USD

from algorand.core.fact_transaction a

left join algo_prices b

on date_trunc(hour, BLOCK_TIMESTAMP)=date_trunc(hour, b.BLOCK_HOUR) and b.ASSET_ID=0

where BLOCK_TIMESTAMP>=CURRENT_DATE-{{Days}}),

eth_transfers as (

select BLOCK_TIMESTAMP, TX_HASH, user,

sum(usd_amount) as usd_amount, max(TX_FEE) as FEE, max(FEE_USD) as FEE_USD

from

(select a.BLOCK_TIMESTAMP, a.TX_HASH,a.ORIGIN_FROM_ADDRESS as user,

sum(AMOUNT*b.PRICE) as usd_amount, TX_FEE, TX_FEE*d.PRICE as FEE_USD

from ethereum.core.ez_token_transfers a

left join ethereum.core.fact_hourly_token_prices b

on a.CONTRACT_ADDRESS=b.TOKEN_ADDRESS and date_trunc(hour, a.BLOCK_TIMESTAMP)=date_trunc(hour, b.HOUR)

left join ethereum.core.fact_transactions c

on a.TX_HASH=c.TX_HASH