SELECT TOKEN0_ADDRESS as contract_address, TOKEN0_SYMBOL as symbol , TOKEN0_DECIMALS as decimals

from polygon.sushi.dim_dex_pools

UNION ALL

SELECT TOKEN1_ADDRESS as contract_address, TOKEN1_SYMBOL as symbol , TOKEN1_DECIMALS as decimals

from polygon.sushi.dim_dex_pools

)

, priceTb as (

SELECT

HOUR::date as p_date,

TOKEN_ADDRESS,

b.symbol as token,

a.decimals,

avg(price) as price_usd

FROM ethereum.core.fact_hourly_token_prices a left join polyDecimal b USING(symbol)

GROUP by 1,2,3,4

UNION

SELECT

HOUR::date as p_date,

TOKEN_ADDRESS,

CASE

WHEN symbol = 'MATIC' THEN 'WMATIC'

ELSE symbol end as token,

decimals,

avg(price) as price_usd

FROM ethereum.core.fact_hourly_token_prices

GROUP by 1,2,3,4

)

select

FROM_ADDRESS,

LABEL,

COUNT(DISTINCT TX_HASH) as tx_cnt,

sum(((RAW_AMOUNT / pow(10, decimals)) * price_usd)) as amount

FROM ethereum.core.fact_token_transfers bebop JOIN ethereum.core.dim_labels l on bebop.FROM_ADDRESS = l.ADDRESS

join priceTb price on (bebop.contract_address = price.TOKEN_ADDRESS and bebop.BLOCK_TIMESTAMP::date = price.p_date)