/* 1, Select every addresses first two outgoing transactions on Ethereum, Arbitrum, Avalanche, Optimism, Polygon and BSC 2, Select the date of the first tranction for every address, count the total number of new addresses each day 3, Count the number of new addresses each day that have an outgoing transaction to SushiSwap among their first two. Sushicontracts for users on ETH> SushiSwapRouter: '0xd9e1cE17f2641f24aE83637ab66a2cca9C378B9F' BentoBoxV1: '0xF5BCE5077908a1b7370B9ae04AdC565EBd643966' SushiToken: '0x6B3595068778DD592e39A122f4f5a5cF09C90fE2' SushiXSwap: '0x011E52E4E40CF9498c79273329E8827b21E2e581' WethMaker: '0x5ad6211CD3fdE39A9cECB5df6f380b8263d1e277' */ /* -- This version never finishes, too large db WITH ETH_First2TxOutByAddress AS ( SELECT * FROM ( SELECT *, rank() OVER (PARTITION BY from_address ORDER BY block_timestamp) as tx_order FROM ( SELECT * FROM arbitrum.core.fact_transactions WHERE block_timestamp > CURRENT_DATE-180 ) ) WHERE tx_order < 3 ORDER BY block_timestamp, from_address ) */ WITH ETH_Old_addresses AS ( SELECT DISTINCT user FROM (SELECT DISTINCT from_address as user FROM ethereum.core.fact_transactions WHERE block_timestamp < CURRENT_DATE-180 AND block_timestamp > CURRENT_DATE-240 AND user IS NOT NULL -- This is the secret sauce, if you don't include it hashing crashes AND status = 'SUCCESS' ) UNION (SELECT DISTINCT to_address as user FROM ethereum.core.fact_transactions WHERE block_timestamp < CURRENT_DATE-180 AND block_timestamp > CURRENT_DATE-240 AND user IS NOT NULL AND status = 'SUCCESS' ) -- WHERE user IS NOT NULL ), ETH_FirstTxOutByAddress AS ( SELECT from_address, block_timestamp, tx_hash, to_address, eth_value FROM ( SELECT from_address, min(block_timestamp) as block_timestamp FROM ethereum.core.fact_transactions WHERE block_timestamp > CURRENT_DATE-180 AND from_address NOT IN (SELECT user FROM ETH_Old_addresses) AND status = 'SUCCESS' GROUP BY from_address ) NATURAL LEFT JOIN ethereum.core.fact_transactions ), ETH_SecondTxOutByAddress AS ( SELECT from_address, block_timestamp, tx_hash, to_address, eth_value FROM ( SELECT from_address, min(block_timestamp) as block_timestamp FROM ethereum.core.fact_transactions WHERE tx_hash NOT IN (SELECT tx_hash FROM ETH_FirstTxOutByAddress) AND from_address NOT IN (SELECT user FROM ETH_Old_addresses) AND block_timestamp > CURRENT_DATE-180 AND status = 'SUCCESS' GROUP BY from_address ) NATURAL LEFT JOIN ethereum.core.fact_transactions ), ETH_FirstTxInByAddress AS ( SELECT to_address, block_timestamp, tx_hash, from_address, eth_value FROM ( SELECT to_address, min(block_timestamp) as block_timestamp FROM ethereum.core.fact_transactions WHERE block_timestamp > CURRENT_DATE-180 AND to_address NOT IN (SELECT user FROM ETH_Old_addresses) AND status = 'SUCCESS' GROUP BY to_address ) NATURAL LEFT JOIN ethereum.core.fact_transactions ), ETH_First2TxOutByAddress AS ( SELECT * FROM ETH_FirstTxOutByAddress UNION SELECT * FROM ETH_SecondTxOutByAddress ), ETH_FirstInOutTx AS ( SELECT * FROM ETH_FirstTxOutByAddress UNION SELECT * FROM ETH_FirstTxInByAddress ), ETH_NewUserTx AS ( SELECT from_address as user, block_timestamp, tx_hash, to_address as counterparty, eth_value FROM ( SELECT from_address, min(block_timestamp) as block_timestamp FROM ETH_FirstInOutTx GROUP BY 1 ) NATURAL LEFT JOIN ETH_FirstInOutTx ), ETH_NewAddresses_daily AS ( SELECT date_trunc('day',block_timestamp) as date, count(user) as new_user_count_ETH FROM ETH_NewUserTx GROUP BY 1 ), ETH_New_SushiAddresses AS ( SELECT distinct from_address as user FROM ETH_First2TxOutByAddress WHERE to_address IN (lower('0xd9e1cE17f2641f24aE83637ab66a2cca9C378B9F'), lower('0xF5BCE5077908a1b7370B9ae04AdC565EBd643966'), lower('0x6B3595068778DD592e39A122f4f5a5cF09C90fE2'), lower('0x011E52E4E40CF9498c79273329E8827b21E2e581'), lower('0x5ad6211CD3fdE39A9cECB5df6f380b8263d1e277')) ), ETH_NewSushiAddresses_daily AS ( SELECT date_trunc('day',block_timestamp) as date, count(user) as new_sushi_user_count_ETH FROM ETH_NewUserTx WHERE user IN (SELECT user FROM ETH_New_SushiAddresses) GROUP BY 1 ), ETH_NewAddressSushiPerc AS ( SELECT date, new_sushi_user_count_ETH as NUC_sushi, new_user_count_ETH-new_sushi_user_count_ETH as NUC_other, new_sushi_user_count_ETH/new_user_count_ETH*100 as NU_prc_sushi, new_user_count_ETH as NUC, 'ETH' as network FROM ETH_NewAddresses_daily NATURAL LEFT JOIN ETH_NewSushiAddresses_daily ), /* -- Arbitrum starts here: MiniChefV2: '0xF4d73326C13a4Fc5FD7A064217e12780e9Bd62c3' SushiSwapRouter: '0x1b02dA8Cb0d097eB8D57A175b88c7D8b47997506' BentoBoxV1: '0x74c764D41B77DBbb4fe771daB1939B00b146894A' SushiToken: '0xd4d42F0b6DEF4CE0383636770eF773390d85c61A' SushiXSwap: '0x53b08DbD70327b7Ba3B7886Fc9987BC985d27262' WethMaker: '0xa19b3b22f29E23e4c04678C94CFC3e8f202137d8' */ ARB_Old_addresses AS ( SELECT DISTINCT user FROM (SELECT DISTINCT from_address as user FROM arbitrum.core.fact_transactions WHERE block_timestamp < CURRENT_DATE-180 AND block_timestamp > CURRENT_DATE-240 AND user IS NOT NULL -- This is the secret sauce, if you don't include it hashing crashes AND status = 'SUCCESS' ) UNION (SELECT DISTINCT to_address as user FROM arbitrum.core.fact_transactions WHERE block_timestamp < CURRENT_DATE-180 AND block_timestamp > CURRENT_DATE-240 AND user IS NOT NULL AND status = 'SUCCESS' ) ), ARB_FirstTxOutByAddress AS ( SELECT from_address, block_timestamp, tx_hash, to_address, eth_value FROM ( SELECT from_address, min(block_timestamp) as block_timestamp FROM arbitrum.core.fact_transactions WHERE block_timestamp > CURRENT_DATE-180 AND from_address NOT IN (SELECT user FROM ARB_Old_addresses) AND status = 'SUCCESS' GROUP BY from_address ) NATURAL LEFT JOIN arbitrum.core.fact_transactions ), ARB_SecondTxOutByAddress AS ( SELECT from_address, block_timestamp, tx_hash, to_address, eth_value FROM ( SELECT from_address, min(block_timestamp) as block_timestamp FROM arbitrum.core.fact_transactions WHERE tx_hash NOT IN (SELECT tx_hash FROM ARB_FirstTxOutByAddress) AND block_timestamp > CURRENT_DATE-180 AND from_address NOT IN (SELECT user FROM ARB_Old_addresses) AND status = 'SUCCESS' GROUP BY from_address ) NATURAL LEFT JOIN arbitrum.core.fact_transactions ), ARB_FirstTxInByAddress AS ( SELECT to_address, block_timestamp, tx_hash, from_address, eth_value FROM ( SELECT to_address, min(block_timestamp) as block_timestamp FROM arbitrum.core.fact_transactions WHERE block_timestamp > CURRENT_DATE-180 AND to_address NOT IN (SELECT user FROM ARB_Old_addresses) AND status = 'SUCCESS' GROUP BY to_address ) NATURAL LEFT JOIN arbitrum.core.fact_transactions ), ARB_First2TxOutByAddress AS ( SELECT * FROM ARB_FirstTxOutByAddress UNION SELECT * FROM ARB_SecondTxOutByAddress ), ARB_FirstInOutTx AS ( SELECT * FROM ARB_FirstTxOutByAddress UNION SELECT * FROM ARB_FirstTxInByAddress ), ARB_NewUserTx AS ( SELECT from_address as user, block_timestamp, tx_hash, to_address as counterparty, eth_value FROM ( SELECT from_address, min(block_timestamp) as block_timestamp FROM ARB_FirstInOutTx GROUP BY 1 ) NATURAL LEFT JOIN ARB_FirstInOutTx ), ARB_NewAddresses_daily AS ( SELECT date_trunc('day',block_timestamp) as date, count(user) as new_user_count_ARB FROM ARB_NewUserTx GROUP BY 1 ), ARB_New_SushiAddresses AS ( SELECT distinct from_address as user FROM ARB_First2TxOutByAddress WHERE to_address IN (lower('0xF4d73326C13a4Fc5FD7A064217e12780e9Bd62c3'), lower('0x1b02dA8Cb0d097eB8D57A175b88c7D8b47997506'), lower('0x74c764D41B77DBbb4fe771daB1939B00b146894A'), lower('0xd4d42F0b6DEF4CE0383636770eF773390d85c61A'), lower('0x53b08DbD70327b7Ba3B7886Fc9987BC985d27262'), lower('0xa19b3b22f29E23e4c04678C94CFC3e8f202137d8')) ), ARB_NewSushiAddresses_daily AS ( SELECT date_trunc('day',block_timestamp) as date, count(user) as new_sushi_user_count_ARB FROM ARB_NewUserTx WHERE user IN (SELECT user FROM ARB_New_SushiAddresses) GROUP BY 1 ), ARB_NewAddressSushiPerc AS ( SELECT date, new_sushi_user_count_ARB as NUC_sushi, new_user_count_ARB-new_sushi_user_count_ARB as NUC_other, new_sushi_user_count_ARB/new_user_count_ARB*100 as NU_prc_sushi, new_user_count_ARB as NUC, 'ARB' as network FROM ARB_NewAddresses_daily NATURAL LEFT JOIN ARB_NewSushiAddresses_daily ), /* -- Polygon starts here: MiniChefV2: '0x0769fd68dFb93167989C6f7254cd0D766Fb2841F' SushiSwapRouter: '0x1b02dA8Cb0d097eB8D57A175b88c7D8b47997506' BentoBoxV1: '0x0319000133d3AdA02600f0875d2cf03D442C3367' SushiToken: '0x0b3F868E0BE5597D5DB7fEB59E1CADBb0fdDa50a' SushiXSwap: '0xd08b5f3e89F1e2d6b067e0A0cbdb094e6e41E77c' WethMaker: '0xf1c9881Be22EBF108B8927c4d197d126346b5036' TridentRouter: '0xc5017BE80b4446988e8686168396289a9A62668E' */ POLY_Old_addresses AS ( SELECT DISTINCT user FROM (SELECT DISTINCT from_address as user FROM polygon.core.fact_transactions WHERE block_timestamp < CURRENT_DATE-180 AND block_timestamp > CURRENT_DATE-240 AND user IS NOT NULL -- This is the secret sauce, if you don't include it hashing crashes AND status = 'SUCCESS' ) UNION (SELECT DISTINCT to_address as user FROM polygon.core.fact_transactions WHERE block_timestamp < CURRENT_DATE-180 AND block_timestamp > CURRENT_DATE-240 AND user IS NOT NULL AND status = 'SUCCESS' ) ), POLY_FirstTxOutByAddress AS ( SELECT from_address, block_timestamp, tx_hash, to_address, matic_value FROM ( SELECT from_address, min(block_timestamp) as block_timestamp FROM polygon.core.fact_transactions WHERE block_timestamp > CURRENT_DATE-180 AND from_address NOT IN (SELECT user FROM POLY_Old_addresses) AND status = 'SUCCESS' GROUP BY from_address ) NATURAL LEFT JOIN polygon.core.fact_transactions ), POLY_SecondTxOutByAddress AS ( SELECT from_address, block_timestamp, tx_hash, to_address, matic_value FROM ( SELECT from_address, min(block_timestamp) as block_timestamp FROM polygon.core.fact_transactions WHERE tx_hash NOT IN (SELECT tx_hash FROM POLY_FirstTxOutByAddress) AND block_timestamp > CURRENT_DATE-180 AND from_address NOT IN (SELECT user FROM POLY_Old_addresses) AND status = 'SUCCESS' GROUP BY from_address ) NATURAL LEFT JOIN polygon.core.fact_transactions ), POLY_FirstTxInByAddress AS ( SELECT to_address, block_timestamp, tx_hash, from_address, matic_value FROM ( SELECT to_address, min(block_timestamp) as block_timestamp FROM polygon.core.fact_transactions WHERE block_timestamp > CURRENT_DATE-180 AND to_address NOT IN (SELECT user FROM POLY_Old_addresses) AND status = 'SUCCESS' GROUP BY to_address ) NATURAL LEFT JOIN polygon.core.fact_transactions ), POLY_First2TxOutByAddress AS ( SELECT * FROM POLY_FirstTxOutByAddress UNION SELECT * FROM POLY_SecondTxOutByAddress ), POLY_FirstInOutTx AS ( SELECT * FROM POLY_FirstTxOutByAddress UNION SELECT * FROM POLY_FirstTxInByAddress ), POLY_NewUserTx AS ( SELECT from_address as user, block_timestamp, tx_hash, to_address as counterparty, matic_value FROM ( SELECT from_address, min(block_timestamp) as block_timestamp FROM POLY_FirstInOutTx GROUP BY 1 ) NATURAL LEFT JOIN POLY_FirstInOutTx ), POLY_NewAddresses_daily AS ( SELECT date_trunc('day',block_timestamp) as date, count(user) as new_user_count_POLY FROM POLY_NewUserTx GROUP BY 1 ), POLY_New_SushiAddresses AS ( SELECT distinct from_address as user FROM POLY_First2TxOutByAddress WHERE to_address IN (lower('0x0769fd68dFb93167989C6f7254cd0D766Fb2841F'), lower('0x1b02dA8Cb0d097eB8D57A175b88c7D8b47997506'), lower('0x0319000133d3AdA02600f0875d2cf03D442C3367'), lower('0x0b3F868E0BE5597D5DB7fEB59E1CADBb0fdDa50a'), lower('0xd08b5f3e89F1e2d6b067e0A0cbdb094e6e41E77c'), lower('0xf1c9881Be22EBF108B8927c4d197d126346b5036'), lower('0xc5017BE80b4446988e8686168396289a9A62668E')) ), POLY_NewSushiAddresses_daily AS ( SELECT date_trunc('day',block_timestamp) as date, count(user) as new_sushi_user_count_POLY FROM POLY_NewUserTx WHERE user IN (SELECT user FROM POLY_New_SushiAddresses) GROUP BY 1 ), POLY_NewAddressSushiPerc AS ( SELECT date, new_sushi_user_count_POLY as NUC_sushi, new_user_count_POLY-new_sushi_user_count_POLY as NUC_other, new_sushi_user_count_POLY/new_user_count_POLY*100 as NU_prc_sushi, new_user_count_POLY as NUC, 'POLY' as network FROM POLY_NewAddresses_daily NATURAL LEFT JOIN POLY_NewSushiAddresses_daily ), /* -- Avalanche starts here: MiniChefV2: '' SushiSwapRouter: '0x1b02dA8Cb0d097eB8D57A175b88c7D8b47997506' BentoBoxV1: '0x0711b6026068f736bae6b213031fce978d48e026' SushiToken: '0x37B608519F91f70F2EeB0e5Ed9AF4061722e4F76' SushiXSwap: '0x2c8C987C4777AB740d20Cb581f5d381BE95A4A4a' WethMaker: '0x560C759A11cd026405F6f2e19c65Da1181995fA2' */ AVAX_Old_addresses AS ( SELECT DISTINCT user FROM (SELECT DISTINCT from_address as user FROM avalanche.core.fact_transactions WHERE block_timestamp < CURRENT_DATE-180 AND block_timestamp > CURRENT_DATE-240 AND user IS NOT NULL -- This is the secret sauce, if you don't include it hashing crashes AND status = 'SUCCESS' ) UNION (SELECT DISTINCT to_address as user FROM avalanche.core.fact_transactions WHERE block_timestamp < CURRENT_DATE-180 AND block_timestamp > CURRENT_DATE-240 AND user IS NOT NULL AND status = 'SUCCESS' ) ), AVAX_FirstTxOutByAddress AS ( SELECT from_address, block_timestamp, tx_hash, to_address, avax_value FROM ( SELECT from_address, min(block_timestamp) as block_timestamp FROM avalanche.core.fact_transactions WHERE block_timestamp > CURRENT_DATE-180 AND from_address NOT IN (SELECT user FROM AVAX_Old_addresses) AND status = 'SUCCESS' GROUP BY from_address ) NATURAL LEFT JOIN avalanche.core.fact_transactions ), AVAX_SecondTxOutByAddress AS ( SELECT from_address, block_timestamp, tx_hash, to_address, avax_value FROM ( SELECT from_address, min(block_timestamp) as block_timestamp FROM avalanche.core.fact_transactions WHERE tx_hash NOT IN (SELECT tx_hash FROM AVAX_FirstTxOutByAddress) AND block_timestamp > CURRENT_DATE-180 AND from_address NOT IN (SELECT user FROM AVAX_Old_addresses) AND status = 'SUCCESS' GROUP BY from_address ) NATURAL LEFT JOIN avalanche.core.fact_transactions ), AVAX_FirstTxInByAddress AS ( SELECT to_address, block_timestamp, tx_hash, from_address, avax_value FROM ( SELECT to_address, min(block_timestamp) as block_timestamp FROM avalanche.core.fact_transactions WHERE block_timestamp > CURRENT_DATE-180 AND to_address NOT IN (SELECT user FROM AVAX_Old_addresses) AND status = 'SUCCESS' GROUP BY to_address ) NATURAL LEFT JOIN avalanche.core.fact_transactions ), AVAX_First2TxOutByAddress AS ( SELECT * FROM AVAX_FirstTxOutByAddress UNION SELECT * FROM AVAX_SecondTxOutByAddress ), AVAX_FirstInOutTx AS ( SELECT * FROM AVAX_FirstTxOutByAddress UNION SELECT * FROM AVAX_FirstTxInByAddress ), AVAX_NewUserTx AS ( SELECT from_address as user, block_timestamp, tx_hash, to_address as counterparty, avax_value FROM ( SELECT from_address, min(block_timestamp) as block_timestamp FROM AVAX_FirstInOutTx GROUP BY 1 ) NATURAL LEFT JOIN AVAX_FirstInOutTx ), AVAX_NewAddresses_daily AS ( SELECT date_trunc('day',block_timestamp) as date, count(user) as new_user_count_AVAX FROM AVAX_NewUserTx GROUP BY 1 ), AVAX_New_SushiAddresses AS ( SELECT distinct from_address as user FROM AVAX_First2TxOutByAddress WHERE to_address IN (lower('0x1b02dA8Cb0d097eB8D57A175b88c7D8b47997506'), lower('0x0711b6026068f736bae6b213031fce978d48e026'), lower('0x37B608519F91f70F2EeB0e5Ed9AF4061722e4F76'), lower('0x2c8C987C4777AB740d20Cb581f5d381BE95A4A4a'), lower('0x560C759A11cd026405F6f2e19c65Da1181995fA2')) ), AVAX_NewSushiAddresses_daily AS ( SELECT date_trunc('day',block_timestamp) as date, count(user) as new_sushi_user_count_AVAX FROM AVAX_NewUserTx WHERE user IN (SELECT user FROM AVAX_New_SushiAddresses) GROUP BY 1 ), AVAX_NewAddressSushiPerc AS ( SELECT date, new_sushi_user_count_AVAX as NUC_sushi, new_user_count_AVAX-new_sushi_user_count_AVAX as NUC_other, new_sushi_user_count_AVAX/new_user_count_AVAX*100 as NU_prc_sushi, new_user_count_AVAX as NUC, 'AVAX' as network FROM AVAX_NewAddresses_daily NATURAL LEFT JOIN AVAX_NewSushiAddresses_daily ), /* -- Optimism starts here: MiniChefV2: '' SushiSwapRouter: '' BentoBoxV1: '' SushiToken: '' SushiXSwap: '0x8B396ddF906D552b2F98a8E7d743DD58Cd0d920f' WethMaker: '' TridentRouter: '0xbE811A0D44E2553d25d11CB8DC0d3F0D0E6430E6' */ OPT_Old_addresses AS ( SELECT DISTINCT user FROM (SELECT DISTINCT from_address as user FROM optimism.core.fact_transactions WHERE block_timestamp < CURRENT_DATE-180 AND block_timestamp > CURRENT_DATE-240 AND user IS NOT NULL -- This is the secret sauce, if you don't include it hashing crashes AND status = 'SUCCESS' ) UNION (SELECT DISTINCT to_address as user FROM optimism.core.fact_transactions WHERE block_timestamp < CURRENT_DATE-180 AND block_timestamp > CURRENT_DATE-240 AND user IS NOT NULL AND status = 'SUCCESS' ) ), OPT_FirstTxOutByAddress AS ( SELECT from_address, block_timestamp, tx_hash, to_address, eth_value FROM ( SELECT from_address, min(block_timestamp) as block_timestamp FROM optimism.core.fact_transactions WHERE block_timestamp > CURRENT_DATE-180 AND from_address NOT IN (SELECT user FROM OPT_Old_addresses) AND status = 'SUCCESS' GROUP BY from_address ) NATURAL LEFT JOIN optimism.core.fact_transactions ), OPT_SecondTxOutByAddress AS ( SELECT from_address, block_timestamp, tx_hash, to_address, eth_value FROM ( SELECT from_address, min(block_timestamp) as block_timestamp FROM optimism.core.fact_transactions WHERE tx_hash NOT IN (SELECT tx_hash FROM OPT_FirstTxOutByAddress) AND block_timestamp > CURRENT_DATE-180 AND from_address NOT IN (SELECT user FROM OPT_Old_addresses) AND status = 'SUCCESS' GROUP BY from_address ) NATURAL LEFT JOIN optimism.core.fact_transactions ), OPT_FirstTxInByAddress AS ( SELECT to_address, block_timestamp, tx_hash, from_address, eth_value FROM ( SELECT to_address, min(block_timestamp) as block_timestamp FROM optimism.core.fact_transactions WHERE block_timestamp > CURRENT_DATE-180 AND to_address NOT IN (SELECT user FROM OPT_Old_addresses) AND status = 'SUCCESS' GROUP BY to_address ) NATURAL LEFT JOIN optimism.core.fact_transactions ), OPT_First2TxOutByAddress AS ( SELECT * FROM OPT_FirstTxOutByAddress UNION SELECT * FROM OPT_SecondTxOutByAddress ), OPT_FirstInOutTx AS ( SELECT * FROM OPT_FirstTxOutByAddress UNION SELECT * FROM OPT_FirstTxInByAddress ), OPT_NewUserTx AS ( SELECT from_address as user, block_timestamp, tx_hash, to_address as counterparty, eth_value FROM ( SELECT from_address, min(block_timestamp) as block_timestamp FROM OPT_FirstInOutTx GROUP BY 1 ) NATURAL LEFT JOIN OPT_FirstInOutTx ), OPT_NewAddresses_daily AS ( SELECT date_trunc('day',block_timestamp) as date, count(user) as new_user_count_OPT FROM OPT_NewUserTx GROUP BY 1 ), OPT_New_SushiAddresses AS ( SELECT distinct from_address as user FROM OPT_First2TxOutByAddress WHERE to_address IN (lower('0x8B396ddF906D552b2F98a8E7d743DD58Cd0d920f'), lower('0xbE811A0D44E2553d25d11CB8DC0d3F0D0E6430E6')) ), OPT_NewSushiAddresses_daily AS ( SELECT date_trunc('day',block_timestamp) as date, count(user) as new_sushi_user_count_OPT FROM OPT_NewUserTx WHERE user IN (SELECT user FROM OPT_New_SushiAddresses) GROUP BY 1 ), OPT_NewAddressSushiPerc AS ( SELECT date, new_sushi_user_count_OPT as NUC_sushi, new_user_count_OPT-new_sushi_user_count_OPT as NUC_other, new_sushi_user_count_OPT/new_user_count_OPT*100 as NU_prc_sushi, new_user_count_OPT as NUC, 'OPT' as network FROM OPT_NewAddresses_daily NATURAL LEFT JOIN OPT_NewSushiAddresses_daily ), /* -- BSC starts here: MiniChefV2: '' SushiSwapRouter: '0x1b02dA8Cb0d097eB8D57A175b88c7D8b47997506' BentoBoxV1: '0xF5BCE5077908a1b7370B9ae04AdC565EBd643966' SushiToken: '0x947950BcC74888a40Ffa2593C5798F11Fc9124C4' SushiXSwap: '0x7A4af156379f512DE147ed3b96393047226d923F' WethMaker: '0xe2d7460457f55e4786C69D2d3fa81978Bf8DD11C' TridentRouter: '' */ BSC_Old_addresses AS ( SELECT DISTINCT user FROM (SELECT DISTINCT from_address as user FROM bsc.core.fact_transactions WHERE block_timestamp < CURRENT_DATE-180 AND block_timestamp > CURRENT_DATE-240 AND user IS NOT NULL -- This is the secret sauce, if you don't include it hashing crashes AND status = 'SUCCESS' ) UNION (SELECT DISTINCT to_address as user FROM bsc.core.fact_transactions WHERE block_timestamp < CURRENT_DATE-180 AND block_timestamp > CURRENT_DATE-240 AND user IS NOT NULL AND status = 'SUCCESS' ) ), BSC_FirstTxOutByAddress AS ( SELECT from_address, block_timestamp, tx_hash, to_address, bnb_value FROM ( SELECT from_address, min(block_timestamp) as block_timestamp FROM bsc.core.fact_transactions WHERE block_timestamp > CURRENT_DATE-180 AND from_address NOT IN (SELECT user FROM BSC_Old_addresses) AND status = 'SUCCESS' GROUP BY from_address ) NATURAL LEFT JOIN bsc.core.fact_transactions ), BSC_SecondTxOutByAddress AS ( SELECT from_address, block_timestamp, tx_hash, to_address, bnb_value FROM ( SELECT from_address, min(block_timestamp) as block_timestamp FROM bsc.core.fact_transactions WHERE tx_hash NOT IN (SELECT tx_hash FROM BSC_FirstTxOutByAddress) AND block_timestamp > CURRENT_DATE-180 AND from_address NOT IN (SELECT user FROM BSC_Old_addresses) AND status = 'SUCCESS' GROUP BY from_address ) NATURAL LEFT JOIN bsc.core.fact_transactions ), BSC_FirstTxInByAddress AS ( SELECT to_address, block_timestamp, tx_hash, from_address, bnb_value FROM ( SELECT to_address, min(block_timestamp) as block_timestamp FROM bsc.core.fact_transactions WHERE block_timestamp > CURRENT_DATE-180 AND to_address NOT IN (SELECT user FROM BSC_Old_addresses) AND status = 'SUCCESS' GROUP BY to_address ) NATURAL LEFT JOIN bsc.core.fact_transactions ), BSC_First2TxOutByAddress AS ( SELECT * FROM BSC_FirstTxOutByAddress UNION SELECT * FROM BSC_SecondTxOutByAddress ), BSC_FirstInOutTx AS ( SELECT * FROM BSC_FirstTxOutByAddress UNION SELECT * FROM BSC_FirstTxInByAddress ), BSC_NewUserTx AS ( SELECT from_address as user, block_timestamp, tx_hash, to_address as counterparty, bnb_value FROM ( SELECT from_address, min(block_timestamp) as block_timestamp FROM BSC_FirstInOutTx GROUP BY 1 ) NATURAL LEFT JOIN BSC_FirstInOutTx ), BSC_NewAddresses_daily AS ( SELECT date_trunc('day',block_timestamp) as date, count(user) as new_user_count_BSC FROM BSC_NewUserTx GROUP BY 1 ), BSC_New_SushiAddresses AS ( SELECT distinct from_address as user FROM BSC_First2TxOutByAddress WHERE to_address IN (lower('0x1b02dA8Cb0d097eB8D57A175b88c7D8b47997506'), lower('0xF5BCE5077908a1b7370B9ae04AdC565EBd643966'), lower('0x947950BcC74888a40Ffa2593C5798F11Fc9124C4'), lower('0x7A4af156379f512DE147ed3b96393047226d923F'), lower('0xe2d7460457f55e4786C69D2d3fa81978Bf8DD11C')) ), BSC_NewSushiAddresses_daily AS ( SELECT date_trunc('day',block_timestamp) as date, count(user) as new_sushi_user_count_BSC FROM BSC_NewUserTx WHERE user IN (SELECT user FROM BSC_New_SushiAddresses) GROUP BY 1 ), BSC_NewAddressSushiPerc AS ( SELECT date, new_sushi_user_count_BSC as NUC_sushi, new_user_count_BSC-new_sushi_user_count_BSC as NUC_other, new_sushi_user_count_BSC/new_user_count_BSC*100 as NU_prc_sushi, new_user_count_BSC as NUC, 'BSC' as network FROM BSC_NewAddresses_daily NATURAL LEFT JOIN BSC_NewSushiAddresses_daily ), -- Join it all together Presentation_order AS ( SELECT key AS symbol, value[0] AS pres_order FROM table(flatten(input => parse_json('{"ETH":[1], "BSC":[2], "POLY":[3], "AVAX":[4], "ARB":[5], "OPT":[6], }') , outer => true)) )/*, AllChain_NewAddressSushiPerc AS ( SELECT * FROM ETH_NewAddressSushiPerc NATURAL FULL OUTER JOIN ARB_NewAddressSushiPerc NATURAL FULL OUTER JOIN POLY_NewAddressSushiPerc NATURAL FULL OUTER JOIN AVAX_NewAddressSushiPerc NATURAL FULL OUTER JOIN OPT_NewAddressSushiPerc NATURAL FULL OUTER JOIN BSC_NewAddressSushiPerc )*/, AllChain_NewAddressSushiPerc AS ( SELECT U.*, P.pres_order FROM ( SELECT * FROM ETH_NewAddressSushiPerc UNION SELECT * FROM ARB_NewAddressSushiPerc UNION SELECT * FROM POLY_NewAddressSushiPerc UNION SELECT * FROM AVAX_NewAddressSushiPerc UNION SELECT * FROM OPT_NewAddressSushiPerc UNION SELECT * FROM BSC_NewAddressSushiPerc ) U LEFT JOIN Presentation_order P ON U.network = P.symbol ORDER BY pres_order ) SELECT * FROM AllChain_NewAddressSushiPerc ORDER BY pres_order

Contribution of SushiSwap to network growth on ETH, BSC and L2 networks (Optimism, Polygon, Avalance, Arbitrum)