SELECT

NVL(event_inputs:pair, event_inputs:pool)::string as pool_address,

IFF(event_inputs:fee IS NOT NULL, 'v3', 'v2') as version,

NVL(event_inputs:fee / 10000 || '%', '0.3%') as fee,

event_inputs:token0::string as token0,

event_inputs:token1::string as token1

FROM ethereum.events_emitted

WHERE

contract_address IN ('0x5c69bee701ef814a2b6a3edd4b1652cb9cc5aa6f', '0x1f98431c8ad98523631ae4a59f267346ea31f984') --factory addresses

AND tx_succeeded = TRUE

AND event_name IN ('PairCreated','PoolCreated')

SELECT

user_address,

address_name,

version,

fee,

SUM(amount_usd) as tvl

FROM ethereum.erc20_balances a

JOIN uniswap_pools b

ON a.user_address = b.pool_address

WHERE balance_date = CURRENT_DATE

GROUP BY 1,2,3,4

ORDER BY tvl DESC NULLS LAST