WITH xsushi_dim_func_ AS ( SELECT * FROM (VALUES ('0xa59f3e0c', 'enter', 'staking $SUSHI for xSUSHI') , ('0x67dfd4c9', 'leave', 'unstaking xSUSHI for $SUSHI') , ('0x095ea7b3', 'approve', 'verifying amount') , ('0xa9059cbb', 'transfer', 'transfer') , ('0x23b872dd', 'transferFrom', 'transfer') , ('0xa457c2d7', 'decreaseAllowance', 'Don''t know for now') , ('0x39509351', 'increaseAllowance', 'Don''t know for now') ) as v (origin_function_signature, function_action, action_description) ) , deposit_ AS ( -- sushi stake SELECT t1.block_timestamp -- , CASE WHEN t1.block_number < 15537393 THEN 'pre-merge' ELSE 'post-merge' END AS period , t1.tx_hash , t1.origin_from_address AS depositor , 'deposit' AS action -- , t1.token_price AS sushi_price -- , t1.amount AS sushi_amount -- , t1.amount_usd AS sushi_amount_usd -- , t2.token_price AS xsushi_price , t2.amount AS xsushi_amount -- , t2.amount_usd AS xsushi_usd FROM ethereum.core.ez_token_transfers AS t1 JOIN ethereum.core.ez_token_transfers AS t2 ON t1.tx_hash = t2.tx_hash AND t1.origin_function_signature = '0xa59f3e0c' AND t1.origin_to_address = '0x8798249c2e607446efb7ad49ec89dd1865ff4272' -- xSUSHI token address AND t1.contract_address = '0x6b3595068778dd592e39a122f4f5a5cf09c90fe2' -- SUSHI token address AND t1.origin_from_address = t1.from_address AND t1.to_address = '0x8798249c2e607446efb7ad49ec89dd1865ff4272' AND t2.origin_function_signature = '0xa59f3e0c' AND t2.origin_to_address = '0x8798249c2e607446efb7ad49ec89dd1865ff4272' AND t2.contract_address = '0x8798249c2e607446efb7ad49ec89dd1865ff4272' AND t2.origin_from_address = t2.to_address AND t2.from_address = '0x0000000000000000000000000000000000000000' WHERE xsushi_amount > 0 -- AND sushi_amount > 0 ) , withdraw_ AS ( SELECT t1.block_timestamp -- , CASE WHEN t1.block_number < 15537393 THEN 'pre-merge' ELSE 'post-merge' END AS period , t1.tx_hash , t1.origin_from_address AS depositor , 'withdraw' AS action -- , t1.token_price AS sushi_price -- , t1.amount AS sushi_amount -- , t1.amount_usd AS sushi_amount_usd -- , t2.token_price AS xsushi_price , t2.amount AS xsushi_amount -- , t2.amount_usd AS xsushi_usd FROM ethereum.core.ez_token_transfers AS t1 JOIN ethereum.core.ez_token_transfers AS t2 ON t1.tx_hash = t2.tx_hash AND t1.origin_function_signature = '0x67dfd4c9' AND t1.origin_to_address = '0x8798249c2e607446efb7ad49ec89dd1865ff4272' AND t1.contract_address = '0x6b3595068778dd592e39a122f4f5a5cf09c90fe2' -- SUSHI token address AND t1.origin_from_address = t1.to_address AND t1.from_address = '0x8798249c2e607446efb7ad49ec89dd1865ff4272' AND t2.origin_function_signature = '0x67dfd4c9' AND t2.origin_to_address = '0x8798249c2e607446efb7ad49ec89dd1865ff4272' AND t2.contract_address = '0x8798249c2e607446efb7ad49ec89dd1865ff4272' AND t2.origin_from_address = t2.from_address AND t2.to_address = '0x0000000000000000000000000000000000000000' WHERE xsushi_amount > 0 -- AND sushi_amount > 0 ) , send_ AS ( SELECT block_timestamp , tx_hash , from_address AS sender , 'send' AS action , amount FROM ethereum.core.ez_token_transfers WHERE origin_function_signature not in ('0xa59f3e0c', '0x67dfd4c9', '0x095ea7b3') -- enter, leave, approve AND contract_address = '0x8798249c2e607446efb7ad49ec89dd1865ff4272' ) , receive_ AS ( SELECT block_timestamp , tx_hash , to_address AS receiver , 'receive' AS action , amount FROM ethereum.core.ez_token_transfers WHERE origin_function_signature not in ('0xa59f3e0c', '0x67dfd4c9', '0x095ea7b3') -- enter, leave, approve AND contract_address = '0x8798249c2e607446efb7ad49ec89dd1865ff4272' ) , union_tx_ AS ( SELECT * FROM deposit_ UNION SELECT * FROM withdraw_ UNION SELECT * FROM send_ UNION SELECT * FROM receive_ ) , hold_ AS ( SELECT depositor , SUM(CASE WHEN action = 'deposit' THEN xsushi_amount END) AS deposit , SUM(CASE WHEN action = 'withdraw' THEN xsushi_amount END) AS withdraw , SUM(CASE WHEN action = 'send' THEN xsushi_amount END) AS send , SUM(CASE WHEN action = 'receive' THEN xsushi_amount END) AS receive , deposit-withdraw-send+receive AS net_hold_amount , CASE WHEN net_hold_amount <= 10 THEN 'size ≤ 10 xSUSHI' WHEN net_hold_amount <= 100 THEN '10 < size ≤ 100 xSUSHI' WHEN net_hold_amount <= 1000 THEN '100 < size ≤ 1k xSUSHI' WHEN net_hold_amount <= 10000 THEN '1k < size ≤ 10k xSUSHI' WHEN net_hold_amount <= 100000 THEN '10k < size ≤ 100k xSUSHI' ELSE '100k xSUSHI < size' END as type , CASE WHEN type = 'size ≤ 10 xSUSHI' THEN '1. size ≤ 10 xSUSHI' WHEN type = '10 < size ≤ 100 xSUSHI' THEN '2. 10 < size ≤ 100 xSUSHI' WHEN type = '100 < size ≤ 1k xSUSHI' THEN '3. 100 < size ≤ 1k xSUSHI' WHEN type = '1k < size ≤ 10k xSUSHI' THEN '4. 1k < size ≤ 10k xSUSHI' WHEN type = '10k < size ≤ 100k xSUSHI' THEN '5. 10k < size ≤ 100k xSUSHI' WHEN type = '100k xSUSHI < size' THEN '6. 100k xSUSHI < size' END as numbering , ROW_NUMBER() OVER (ORDER BY net_hold_amount DESC) AS rank , concat(lpad(rank::string, 3, ' '), '. ', left(depositor, 6), '...', right(depositor, 4)) AS xlabel , 100 * ratio_to_report(net_hold_amount) over () as percent_net_hold FROM union_tx_ GROUP BY 1 HAVING net_hold_amount > 0 ORDER BY rank ) SELECT type , numbering , COUNT(depositor) FROM hold_ GROUP BY 1, 2 ORDER BY numbering

xSUSHI Megadash