SELECT

'join_key' as join_key

,LATEST_ANSWER_UNADJ /1E8 as eth_price

from ethereum.chainlink.ez_oracle_feeds

-- where BLOCK_TIMESTAMP>'2023-01-01'

where FEED_NAME ='ETH / USD'

order by 1 desc

limit 1

),

pool_info as

SELECT

*

from

(

SELECT

pair_type

,POOL_ADDRESS

,POOL_NAME

,TOKEN0

,TOKEN0_SYMBOL

,TOKEN1

,TOKEN1_SYMBOL

,b_USD_VALUE_NOW

,c_USD_VALUE_NOW

,b_CURRENT_BAL

,c_CURRENT_BAL

,case

when b_CONTRACT_ADDRESS is null and c_CONTRACT_ADDRESS is null then 'Unused Pool'

when USD_AMOUNT<2000 then 'Tiny Pool'

when USD_AMOUNT is null then 'Null Pool'

else 'Other'

end as pool_type