SELECT

DISTINCT

date_trunc('hour',block_timestamp) AS blockhour,

contract_address AS factory,

LOWER(inputs:_pool::STRING) AS pool_add,

(SPLIT(LOWER(VALUE_STR),'^')) AS coins

FROM SILVER.ETHEREUM_READS

WHERE contract_name='Vyper_contract'

AND contract_address IN ('0x0959158b6040d32d04c301a72cbfd6b39e21c9ae', LOWER('0xfD6f33A0509ec67dEFc500755322aBd9Df1bD5B8'))

AND function_name = 'get_underlying_coins'

--QUALIFY (row_number() OVER (partition by pool_add order by pool_add desc)) = 1

AND block_timestamp >= CURRENT_DATE - 60

, coins AS (

SELECT

blockhour,

pool_add,

factory,

value::STRING AS coins,

(row_number() OVER (partition by pool_add,blockhour order by blockhour desc) - 1 ) AS index

FROM pool_tokens,

Table(Flatten(pool_tokens.coins))

WHERE value::STRING <> '0x0000000000000000000000000000000000000000'

)

SELECT

blockhour,pool_add,factory,coins,index,

l.address_name

FROM coins c LEFT JOIN silver.ethereum_address_labels l

ON c.coins = l.address