WITH tokenList AS ( SELECT tokenSymbol, l1Address, starknetAddressWith0s,starknetAddress, decimals FROM ( VALUES('ETH', '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2', '0x049d36570d4e46f48e99674bd3fcc84644ddd6b96f7c741b1562b82f9e004dc7','0x49d36570d4e46f48e99674bd3fcc84644ddd6b96f7c741b1562b82f9e004dc7', 18) , ('BTC', '0x2260fac5e5542a773aa44fbcfedf7c193bc2c599', '0x03fe2b97c1fd336e750087d68b9b867997fd64a2661ff3ca5a7c771641e8e7ac', '0x3fe2b97c1fd336e750087d68b9b867997fd64a2661ff3ca5a7c771641e8e7ac', 8) , ('DAI', '0x6b175474e89094c44da98b954eedeac495271d0f', '0x00da114221cb83fa859dbdb4c44beeaa0bb37c7537ad5ae66fe5e0efd20e6eb3', '0xda114221cb83fa859dbdb4c44beeaa0bb37c7537ad5ae66fe5e0efd20e6eb3', 18) , ('USDT', '0xdac17f958d2ee523a2206206994597c13d831ec7', '0x068f5c6a61780768455de69077e07e89787839bf8166decfbf92b645209c0fb8', '0x68f5c6a61780768455de69077e07e89787839bf8166decfbf92b645209c0fb8', 6) , ('USDC', '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48', '0x053c91253bc9682c04929ca02ed00b3e423f6710d2ee7e0d5ebb06f3ecf368a8', '0x53c91253bc9682c04929ca02ed00b3e423f6710d2ee7e0d5ebb06f3ecf368a8', 6) , ('wstETH', '0x7f39c581f595b53c5cb19bd0b3f8da6c935e2ca0', '0x042b8f0484674ca266ac5d08e4ac6a3fe65bd3129795def2dca5c34ecc5f96d2', '0x42b8f0484674ca266ac5d08e4ac6a3fe65bd3129795def2dca5c34ecc5f96d2', 18) , ('LORDS', '0x686f2404e77ab0d9070a46cdfb0b7fecdd2318b0', '0x0124aeb495b947201f5fac96fd1138e326ad86195b98df6dec9009158a533b49', '0x124aeb495b947201f5fac96fd1138e326ad86195b98df6dec9009158a533b49', 18) , ('rETH', '0xae78736cd615f374d3085123a210448e74fc6393', '0x0319111a5037cbec2b3e638cc34a3474e2d2608299f3e62866e9cc683208c610', '0x319111a5037cbec2b3e638cc34a3474e2d2608299f3e62866e9cc683208c610', 18 ) , ('LUSD', '0x5f98805a4e8be255a32880fdec7f6728c6568ba0', '0x070a76fd48ca0ef910631754d77dd822147fe98a569b826ec85e3c33fde586ac', '0x70a76fd48ca0ef910631754d77dd822147fe98a569b826ec85e3c33fde586ac', 18) ) AS t(tokenSymbol, l1Address, starknetAddressWith0s, starknetAddress, decimals) ) , prices AS ( SELECT TOKEN_ADDRESS , AVG(price) AS price FROM crosschain.price.fact_hourly_token_prices WHERE BLOCKCHAIN = 'ethereum' AND HOUR > CURRENT_TIMESTAMP - INTERVAL '30 HOUR' AND HOUR < CURRENT_TIMESTAMP - INTERVAL '6 HOUR' AND TOKEN_ADDRESS IN ( SELECT l1Address FROM tokenList ) GROUP BY 1 ) , deposits_1 AS ( SELECT ev.TIMESTAMP AS date , tr.CONTRACT AS userAddress , ev.TX_HASH AS hash , CONCAT(tl0.tokenSymbol, '/', tl1.tokenSymbol, '-' ,ev.PARAMETERS[1]:value[2]:value/POW(2,128) * 100, '%') AS poolName , CASE ev.PARAMETERS[4]:value[0]:value[1]:value WHEN '0x0' THEN ev.PARAMETERS[4]:value[0]:value[0]:value/POW(10,tl0.decimals) WHEN '0x1' THEN ev.PARAMETERS[4]:value[0]:value[0]:value/POW(10,tl0.decimals) * -1 ELSE '0' END AS token0realAmount , CASE ev.PARAMETERS[4]:value[1]:value[1]:value WHEN '0x0' THEN ev.PARAMETERS[4]:value[1]:value[0]:value/POW(10,tl1.decimals) WHEN '0x1' THEN ev.PARAMETERS[4]:value[1]:value[0]:value/POW(10,tl1.decimals) * -1 ELSE '0' END AS token1realAmount , tl0.l1Address AS token0L1Address , tl1.l1Address AS token1L1Address FROM external.tokenflow_starknet.decoded_events ev INNER JOIN external.tokenflow_starknet.decoded_transactions tr ON ev.TX_HASH = tr.TX_HASH AND tr.CHAIN_ID = 'mainnet' LEFT JOIN tokenList tl0 ON ev.PARAMETERS[1]:value[0]:value = tl0.starknetAddress LEFT JOIN tokenList tl1 ON ev.PARAMETERS[1]:value[1]:value = tl1.starknetAddress WHERE ev.CHAIN_ID = 'mainnet' --AND ev.TX_HASH = '0x0795b952aafcc1a172165ca5e316f8ccf6872ab5c326c07876e877f2c23f6456' AND ev.NAME = 'Deposit' AND ev.CONTRACT = '0x02e0af29598b407c8716b17f6d2795eca1b471413fa03fb145a5e33722184067' --EkuboDeposits ) , deposits_2 AS ( SELECT d.userAddress AS userAddress , d.poolName AS pool , SUM(d.token0realAmount * p0.price + d.token1realAmount * p1.price) AS depositedAmount FROM deposits_1 d LEFT JOIN prices p0 on d.token0L1Address = p0.TOKEN_ADDRESS LEFT JOIN prices p1 on d.token1L1Address = p1.TOKEN_ADDRESS GROUP BY 1, 2 ) , withdrawals_1 AS ( SELECT ev.TIMESTAMP AS date , ev.TX_HASH AS hash , tr.CONTRACT AS userAddress , CONCAT(tl0.tokenSymbol, '/', tl1.tokenSymbol, '-' ,ev.PARAMETERS[1]:value[2]:value/POW(2,128) * 100, '%') AS poolName , CASE ev.PARAMETERS[4]:value[0]:value[1]:value WHEN '0x0' THEN ev.PARAMETERS[4]:value[0]:value[0]:value/POW(10,tl0.decimals) WHEN '0x1' THEN ev.PARAMETERS[4]:value[0]:value[0]:value/POW(10,tl0.decimals) * -1 ELSE '0' END AS token0realAmount , CASE ev.PARAMETERS[4]:value[1]:value[1]:value WHEN '0x0' THEN ev.PARAMETERS[4]:value[1]:value[0]:value/POW(10,tl1.decimals) WHEN '0x1' THEN ev.PARAMETERS[4]:value[1]:value[0]:value/POW(10,tl1.decimals) * -1 ELSE '0' END AS token1realAmount , tl0.l1Address AS token0L1Address , tl1.l1Address AS token1L1Address FROM external.tokenflow_starknet.decoded_events ev INNER JOIN external.tokenflow_starknet.decoded_transactions tr ON ev.TX_HASH = tr.TX_HASH AND tr.CHAIN_ID = 'mainnet' LEFT JOIN tokenList tl0 ON ev.PARAMETERS[1]:value[0]:value = tl0.starknetAddress LEFT JOIN tokenList tl1 ON ev.PARAMETERS[1]:value[1]:value = tl1.starknetAddress WHERE ev.CHAIN_ID = 'mainnet' --AND TX_HASH = '0x02c975201b7e2dc8948d4f2ed0bb7a450c23e07f0a5b82fe4964afc858c22253' AND ev.NAME = 'Withdraw' AND ev.CONTRACT = '0x02e0af29598b407c8716b17f6d2795eca1b471413fa03fb145a5e33722184067' --EKUBO POSITIONS ) , withdrawals_2 AS ( SELECT d.userAddress AS userAddress , d.poolName AS pool , SUM(d.token0realAmount * p0.price + d.token1realAmount * p1.price) AS withdrawnAmount FROM withdrawals_1 d LEFT JOIN prices p0 on d.token0L1Address = p0.TOKEN_ADDRESS LEFT JOIN prices p1 on d.token1L1Address = p1.TOKEN_ADDRESS GROUP BY 1, 2 ) , user_lp_balances AS ( SELECT d.userAddress , d.pool , d.depositedAmount + COALESCE(w.withdrawnAmount, 0) AS userLpBalance FROM deposits_2 d LEFT JOIN withdrawals_2 w ON d.userAddress = w.userAddress AND d.pool = w.pool ORDER BY 3 DESC ) , uneligible_pools AS ( SELECT pool AS noneligible_pools FROM (SELECT pool , SUM(userLpBalance) AS poolLPbalance FROM user_lp_balances GROUP BY 1 ) WHERE poolLPbalance < 500 ) SELECT CASE WHEN userLpBalance <= 10 AND userLpBalance > 0 THEN 'A(0-10]' WHEN userLpBalance <= 50 AND userLpBalance > 10 THEN 'B(10-50]' WHEN userLpBalance <= 250 AND userLpBalance > 50 THEN 'C(50-250]' WHEN userLpBalance <= 1000 AND userLpBalance > 250 THEN 'D(250-1K]' WHEN userLpBalance <= 5000 AND userLpBalance > 1000 THEN 'E(1K-5K]' WHEN userLpBalance <= 10000 AND userLpBalance > 5000 THEN 'F(5K-10K]' WHEN userLpBalance <= 50000 AND userLpBalance > 10000 THEN 'G(10K-50K]' WHEN userLpBalance <= 250000 AND userLpBalance > 50000 THEN 'H(50K-250K]' WHEN userLpBalance > 250000 THEN 'I250K+' ELSE 'A(0-10]' END AS "LP Balance Tier" , pool , COUNT(DISTINCT(userAddress)) AS "Number of Addresses" , SUM(userLpBalance) AS "Total LP Amount" FROM user_lp_balances WHERE pool NOT IN (SELECT noneligible_pools FROM uneligible_pools) GROUP BY 1, 2

Ekubo Protocol