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

)

, bebop as (

select

bebop.BLOCK_TIMESTAMP::date as date,

TX_HASH,

FROM_ADDRESS,

((RAW_AMOUNT / pow(10, decimals.decimals)) * price_usd) as amt

from polygon.core.fact_token_transfers bebop

JOIN polyDecimal decimals on bebop.contract_address = decimals.contract_address