( with tiertbl as (with alltbl AS (WITH general AS ( SELECT DATE_TRUNC('hour', a.BLOCK_TIMESTAMP) AS "Date", CASE WHEN b.TO_ADDRESS IN ( '0x54945180db7943c0ed0fee7edab2bd24620256bc', '0x93c4b944d05dfe6df7645a86cd2206016c51564d', '0x1bee69b7dfffa4e2d53c2a2df135c388ad25dcd2', lower('0x57ba429517c3473B6d34CA9aCd56c0e735b94c02'), lower('0x0Fe4F44beE93503346A3Ac9EE5A26b130a5796d6'), lower('0x13760F50a9d7377e4F20CB8CF9e4c26586c658ff'), lower('0x7CA911E83dabf90C90D3De5411a10F1A6112184'), lower('0x9d7eD45EE2E8FC5482fa2428f15C971e6369011d'), lower('0xa4C637e0F704745D182e4D38cAb7E7485321d059') ) THEN 'Deposit' ELSE 'Withdraw' END AS "Action", DATEDIFF('HOUR', a.BLOCK_TIMESTAMP, CURRENT_TIMESTAMP()) AS "Hours Held", b.SYMBOL AS "Pool", ROUND(b.AMOUNT, 4) AS "Volume", ROUND(b.AMOUNT_USD, 4) AS "USD Volume", a.FROM_ADDRESS AS "User Wallet Address", ROUND(a.TX_FEE, 5) AS "Transaction Fee", ROUND(a.GAS_PRICE, 1) AS "Gas (Gwei)", a.TX_HASH AS "Transaction Hash" FROM ethereum.core.fact_transactions a LEFT JOIN ethereum.core.ez_token_transfers b ON a.tx_hash = b.tx_hash AND a.BLOCK_TIMESTAMP = b.BLOCK_TIMESTAMP WHERE (b.TO_ADDRESS IN ( '0x54945180db7943c0ed0fee7edab2bd24620256bc', '0x93c4b944d05dfe6df7645a86cd2206016c51564d', '0x1bee69b7dfffa4e2d53c2a2df135c388ad25dcd2', lower('0x57ba429517c3473B6d34CA9aCd56c0e735b94c02'), lower('0x0Fe4F44beE93503346A3Ac9EE5A26b130a5796d6'), lower('0x13760F50a9d7377e4F20CB8CF9e4c26586c658ff'), lower('0x7CA911E83dabf90C90D3De5411a10F1A6112184'), lower('0x9d7eD45EE2E8FC5482fa2428f15C971e6369011d'), lower('0xa4C637e0F704745D182e4D38cAb7E7485321d059') ) OR b.FROM_ADDRESS IN ( '0x54945180db7943c0ed0fee7edab2bd24620256bc', '0x93c4b944d05dfe6df7645a86cd2206016c51564d', '0x1bee69b7dfffa4e2d53c2a2df135c388ad25dcd2', lower('0x57ba429517c3473B6d34CA9aCd56c0e735b94c02'), lower('0x0Fe4F44beE93503346A3Ac9EE5A26b130a5796d6'), lower('0x13760F50a9d7377e4F20CB8CF9e4c26586c658ff'), lower('0x7CA911E83dabf90C90D3De5411a10F1A6112184'), lower('0x9d7eD45EE2E8FC5482fa2428f15C971e6369011d'), lower('0xa4C637e0F704745D182e4D38cAb7E7485321d059') )) AND a.TX_SUCCEEDED = 'TRUE' AND b.symbol IN ('cbETH', 'stETH', 'rETH', 'OETH', 'EthX', 'wBETH', 'AnkrETH', 'swETH', 'osETH') ) SELECT *, CASE WHEN "Action" = 'Deposit' THEN "Hours Held" * "Volume" ELSE "Hours Held" * "Volume" * -1 END AS "points" FROM general where "Date" < '2023-08-22 00:00:00.000 ' and "Date" >= '2023-07-12 00:00:00.000 ' ) SELECT "User Wallet Address", -- count (DISTINCT "Transaction Hash") as "Total tx", sum ("points") as "Total Points" from alltbl GROUP by 1 order by 2 desc ) SELECT 'Aug 22th-Jul 12th' time, CASE WHEN "Total Points" >= 100000 THEN 'Tier.a: more than 100k points' WHEN "Total Points" >= 90000 and "Total Points" < 100000 THEN 'Tier.b: 90-100k points' WHEN "Total Points" >= 80000 and "Total Points" < 90000 THEN 'Tier.c: 80-90k points' WHEN "Total Points" >= 70000 and "Total Points" < 80000 THEN 'Tier.d: 70-80k points' WHEN "Total Points" >= 60000 and "Total Points" < 70000 THEN 'Tier.e: 60-70k points' WHEN "Total Points" >= 50000 and "Total Points" < 60000 THEN 'Tier.f: 50-60k points' WHEN "Total Points" >= 40000 and "Total Points" < 50000 THEN 'Tier.g: 40-50k points' WHEN "Total Points" >= 30000 and "Total Points" < 40000 THEN 'Tier.h: 30-40k points' WHEN "Total Points" >= 20000 and "Total Points" < 30000 THEN 'Tier.i: 20-30k points' WHEN "Total Points" >= 10000 and "Total Points" < 20000 THEN 'Tier.j: 10-20k points' WHEN "Total Points" >= 9000 and "Total Points" < 10000 THEN 'Tier.k: 9-10k points' WHEN "Total Points" >= 8000 and "Total Points" < 9000 THEN 'Tier.l: 8-9k points' WHEN "Total Points" >= 7000 and "Total Points" < 8000 THEN 'Tier.m: 7-8k points' WHEN "Total Points" >= 6000 and "Total Points" < 7000 THEN 'Tier.n: 6-7k points' WHEN "Total Points" >= 5000 and "Total Points" < 6000 THEN 'Tier.o: 5-6k points' WHEN "Total Points" >= 4000 and "Total Points" < 5000 THEN 'Tier.p: 4-5k points' WHEN "Total Points" >= 3000 and "Total Points" < 4000 THEN 'Tier.q: 3-4k points' WHEN "Total Points" >= 2000 and "Total Points" < 3000 THEN 'Tier.r: 2-3k points' WHEN "Total Points" >= 1000 and "Total Points" < 2000 THEN 'Tier.s: 1-2k points' WHEN "Total Points" >= 500 and "Total Points" < 1000 THEN 'Tier.t: 500-1000 points' WHEN "Total Points" >= 100 and "Total Points" < 500 THEN 'Tier.u: 100-500 points' WHEN "Total Points" >= 10 and "Total Points" < 100 THEN 'Tier.v: 10-100 points' WHEN "Total Points" < 10 THEN 'Tier.w: under 10 points' END AS "Tier" , COUNT(1) AS "Total wallets" FROM tiertbl GROUP BY 2 order by 2 asc ) union all ( with tiertbl as (with alltbl AS (WITH general AS ( SELECT DATE_TRUNC('hour', a.BLOCK_TIMESTAMP) AS "Date", CASE WHEN b.TO_ADDRESS IN ( '0x54945180db7943c0ed0fee7edab2bd24620256bc', '0x93c4b944d05dfe6df7645a86cd2206016c51564d', '0x1bee69b7dfffa4e2d53c2a2df135c388ad25dcd2', lower('0x57ba429517c3473B6d34CA9aCd56c0e735b94c02'), lower('0x0Fe4F44beE93503346A3Ac9EE5A26b130a5796d6'), lower('0x13760F50a9d7377e4F20CB8CF9e4c26586c658ff'), lower('0x7CA911E83dabf90C90D3De5411a10F1A6112184'), lower('0x9d7eD45EE2E8FC5482fa2428f15C971e6369011d'), lower('0xa4C637e0F704745D182e4D38cAb7E7485321d059') ) THEN 'Deposit' ELSE 'Withdraw' END AS "Action", DATEDIFF('HOUR', a.BLOCK_TIMESTAMP, CURRENT_TIMESTAMP()) AS "Hours Held", b.SYMBOL AS "Pool", ROUND(b.AMOUNT, 4) AS "Volume", ROUND(b.AMOUNT_USD, 4) AS "USD Volume", a.FROM_ADDRESS AS "User Wallet Address", ROUND(a.TX_FEE, 5) AS "Transaction Fee", ROUND(a.GAS_PRICE, 1) AS "Gas (Gwei)", a.TX_HASH AS "Transaction Hash" FROM ethereum.core.fact_transactions a LEFT JOIN ethereum.core.ez_token_transfers b ON a.tx_hash = b.tx_hash AND a.BLOCK_TIMESTAMP = b.BLOCK_TIMESTAMP WHERE (b.TO_ADDRESS IN ( '0x54945180db7943c0ed0fee7edab2bd24620256bc', '0x93c4b944d05dfe6df7645a86cd2206016c51564d', '0x1bee69b7dfffa4e2d53c2a2df135c388ad25dcd2', lower('0x57ba429517c3473B6d34CA9aCd56c0e735b94c02'), lower('0x0Fe4F44beE93503346A3Ac9EE5A26b130a5796d6'), lower('0x13760F50a9d7377e4F20CB8CF9e4c26586c658ff'), lower('0x7CA911E83dabf90C90D3De5411a10F1A6112184'), lower('0x9d7eD45EE2E8FC5482fa2428f15C971e6369011d'), lower('0xa4C637e0F704745D182e4D38cAb7E7485321d059') ) OR b.FROM_ADDRESS IN ( '0x54945180db7943c0ed0fee7edab2bd24620256bc', '0x93c4b944d05dfe6df7645a86cd2206016c51564d', '0x1bee69b7dfffa4e2d53c2a2df135c388ad25dcd2', lower('0x57ba429517c3473B6d34CA9aCd56c0e735b94c02'), lower('0x0Fe4F44beE93503346A3Ac9EE5A26b130a5796d6'), lower('0x13760F50a9d7377e4F20CB8CF9e4c26586c658ff'), lower('0x7CA911E83dabf90C90D3De5411a10F1A6112184'), lower('0x9d7eD45EE2E8FC5482fa2428f15C971e6369011d'), lower('0xa4C637e0F704745D182e4D38cAb7E7485321d059') )) AND a.status = 'SUCCESS' AND b.symbol IN ('cbETH', 'stETH', 'rETH', 'OETH', 'EthX', 'wBETH', 'AnkrETH', 'swETH', 'osETH') ) SELECT *, CASE WHEN "Action" = 'Deposit' THEN "Hours Held" * "Volume" ELSE "Hours Held" * "Volume" * -1 END AS "points" FROM general where "Date" < '2023-07-12 00:00:00.000 ' ) SELECT "User Wallet Address", -- count (DISTINCT "Transaction Hash") as "Total tx", sum ("points") as "Total Points" from alltbl GROUP by 1 order by 2 desc ) SELECT 'Before Jul 12th' time, CASE WHEN "Total Points" >= 100000 THEN 'Tier.a: more than 100k points' WHEN "Total Points" >= 90000 and "Total Points" < 100000 THEN 'Tier.b: 90-100k points' WHEN "Total Points" >= 80000 and "Total Points" < 90000 THEN 'Tier.c: 80-90k points' WHEN "Total Points" >= 70000 and "Total Points" < 80000 THEN 'Tier.d: 70-80k points' WHEN "Total Points" >= 60000 and "Total Points" < 70000 THEN 'Tier.e: 60-70k points' WHEN "Total Points" >= 50000 and "Total Points" < 60000 THEN 'Tier.f: 50-60k points' WHEN "Total Points" >= 40000 and "Total Points" < 50000 THEN 'Tier.g: 40-50k points' WHEN "Total Points" >= 30000 and "Total Points" < 40000 THEN 'Tier.h: 30-40k points' WHEN "Total Points" >= 20000 and "Total Points" < 30000 THEN 'Tier.i: 20-30k points' WHEN "Total Points" >= 10000 and "Total Points" < 20000 THEN 'Tier.j: 10-20k points' WHEN "Total Points" >= 9000 and "Total Points" < 10000 THEN 'Tier.k: 9-10k points' WHEN "Total Points" >= 8000 and "Total Points" < 9000 THEN 'Tier.l: 8-9k points' WHEN "Total Points" >= 7000 and "Total Points" < 8000 THEN 'Tier.m: 7-8k points' WHEN "Total Points" >= 6000 and "Total Points" < 7000 THEN 'Tier.n: 6-7k points' WHEN "Total Points" >= 5000 and "Total Points" < 6000 THEN 'Tier.o: 5-6k points' WHEN "Total Points" >= 4000 and "Total Points" < 5000 THEN 'Tier.p: 4-5k points' WHEN "Total Points" >= 3000 and "Total Points" < 4000 THEN 'Tier.q: 3-4k points' WHEN "Total Points" >= 2000 and "Total Points" < 3000 THEN 'Tier.r: 2-3k points' WHEN "Total Points" >= 1000 and "Total Points" < 2000 THEN 'Tier.s: 1-2k points' WHEN "Total Points" >= 500 and "Total Points" < 1000 THEN 'Tier.t: 500-1000 points' WHEN "Total Points" >= 100 and "Total Points" < 500 THEN 'Tier.u: 100-500 points' WHEN "Total Points" >= 10 and "Total Points" < 100 THEN 'Tier.v: 10-100 points' WHEN "Total Points" < 10 THEN 'Tier.w: under 10 points' END AS "Tier" , COUNT(1) AS "Total wallets" FROM tiertbl GROUP BY 2 order by 2 asc ) union all ( with tiertbl as (with alltbl AS (WITH general AS ( SELECT DATE_TRUNC('hour', a.BLOCK_TIMESTAMP) AS "Date", CASE WHEN b.TO_ADDRESS IN ( '0x54945180db7943c0ed0fee7edab2bd24620256bc', '0x93c4b944d05dfe6df7645a86cd2206016c51564d', '0x1bee69b7dfffa4e2d53c2a2df135c388ad25dcd2', lower('0x57ba429517c3473B6d34CA9aCd56c0e735b94c02'), lower('0x0Fe4F44beE93503346A3Ac9EE5A26b130a5796d6'), lower('0x13760F50a9d7377e4F20CB8CF9e4c26586c658ff'), lower('0x7CA911E83dabf90C90D3De5411a10F1A6112184'), lower('0x9d7eD45EE2E8FC5482fa2428f15C971e6369011d'), lower('0xa4C637e0F704745D182e4D38cAb7E7485321d059') ) THEN 'Deposit' ELSE 'Withdraw' END AS "Action", DATEDIFF('HOUR', a.BLOCK_TIMESTAMP, CURRENT_TIMESTAMP()) AS "Hours Held", b.SYMBOL AS "Pool", ROUND(b.AMOUNT, 4) AS "Volume", ROUND(b.AMOUNT_USD, 4) AS "USD Volume", a.FROM_ADDRESS AS "User Wallet Address", ROUND(a.TX_FEE, 5) AS "Transaction Fee", ROUND(a.GAS_PRICE, 1) AS "Gas (Gwei)", a.TX_HASH AS "Transaction Hash" FROM ethereum.core.fact_transactions a LEFT JOIN ethereum.core.ez_token_transfers b ON a.tx_hash = b.tx_hash AND a.BLOCK_TIMESTAMP = b.BLOCK_TIMESTAMP WHERE (b.TO_ADDRESS IN ( '0x54945180db7943c0ed0fee7edab2bd24620256bc', '0x93c4b944d05dfe6df7645a86cd2206016c51564d', '0x1bee69b7dfffa4e2d53c2a2df135c388ad25dcd2', lower('0x57ba429517c3473B6d34CA9aCd56c0e735b94c02'), lower('0x0Fe4F44beE93503346A3Ac9EE5A26b130a5796d6'), lower('0x13760F50a9d7377e4F20CB8CF9e4c26586c658ff'), lower('0x7CA911E83dabf90C90D3De5411a10F1A6112184'), lower('0x9d7eD45EE2E8FC5482fa2428f15C971e6369011d'), lower('0xa4C637e0F704745D182e4D38cAb7E7485321d059') ) OR b.FROM_ADDRESS IN ( '0x54945180db7943c0ed0fee7edab2bd24620256bc', '0x93c4b944d05dfe6df7645a86cd2206016c51564d', '0x1bee69b7dfffa4e2d53c2a2df135c388ad25dcd2', lower('0x57ba429517c3473B6d34CA9aCd56c0e735b94c02'), lower('0x0Fe4F44beE93503346A3Ac9EE5A26b130a5796d6'), lower('0x13760F50a9d7377e4F20CB8CF9e4c26586c658ff'), lower('0x7CA911E83dabf90C90D3De5411a10F1A6112184'), lower('0x9d7eD45EE2E8FC5482fa2428f15C971e6369011d'), lower('0xa4C637e0F704745D182e4D38cAb7E7485321d059') )) AND a.status = 'SUCCESS' AND b.symbol IN ('cbETH', 'stETH', 'rETH', 'OETH', 'EthX', 'wBETH', 'AnkrETH', 'swETH', 'osETH') ) SELECT *, CASE WHEN "Action" = 'Deposit' THEN "Hours Held" * "Volume" ELSE "Hours Held" * "Volume" * -1 END AS "points" FROM general where "Date" < '2023-12-18 00:00:00.000 ' and "Date" >= '2023-08-22 00:00:00.000 ' ) SELECT "User Wallet Address", -- count (DISTINCT "Transaction Hash") as "Total tx", sum ("points") as "Total Points" from alltbl GROUP by 1 order by 2 desc ) SELECT 'Aug 22th-Dec 18th' time, CASE WHEN "Total Points" >= 100000 THEN 'Tier.a: more than 100k points' WHEN "Total Points" >= 90000 and "Total Points" < 100000 THEN 'Tier.b: 90-100k points' WHEN "Total Points" >= 80000 and "Total Points" < 90000 THEN 'Tier.c: 80-90k points' WHEN "Total Points" >= 70000 and "Total Points" < 80000 THEN 'Tier.d: 70-80k points' WHEN "Total Points" >= 60000 and "Total Points" < 70000 THEN 'Tier.e: 60-70k points' WHEN "Total Points" >= 50000 and "Total Points" < 60000 THEN 'Tier.f: 50-60k points' WHEN "Total Points" >= 40000 and "Total Points" < 50000 THEN 'Tier.g: 40-50k points' WHEN "Total Points" >= 30000 and "Total Points" < 40000 THEN 'Tier.h: 30-40k points' WHEN "Total Points" >= 20000 and "Total Points" < 30000 THEN 'Tier.i: 20-30k points' WHEN "Total Points" >= 10000 and "Total Points" < 20000 THEN 'Tier.j: 10-20k points' WHEN "Total Points" >= 9000 and "Total Points" < 10000 THEN 'Tier.k: 9-10k points' WHEN "Total Points" >= 8000 and "Total Points" < 9000 THEN 'Tier.l: 8-9k points' WHEN "Total Points" >= 7000 and "Total Points" < 8000 THEN 'Tier.m: 7-8k points' WHEN "Total Points" >= 6000 and "Total Points" < 7000 THEN 'Tier.n: 6-7k points' WHEN "Total Points" >= 5000 and "Total Points" < 6000 THEN 'Tier.o: 5-6k points' WHEN "Total Points" >= 4000 and "Total Points" < 5000 THEN 'Tier.p: 4-5k points' WHEN "Total Points" >= 3000 and "Total Points" < 4000 THEN 'Tier.q: 3-4k points' WHEN "Total Points" >= 2000 and "Total Points" < 3000 THEN 'Tier.r: 2-3k points' WHEN "Total Points" >= 1000 and "Total Points" < 2000 THEN 'Tier.s: 1-2k points' WHEN "Total Points" >= 500 and "Total Points" < 1000 THEN 'Tier.t: 500-1000 points' WHEN "Total Points" >= 100 and "Total Points" < 500 THEN 'Tier.u: 100-500 points' WHEN "Total Points" >= 10 and "Total Points" < 100 THEN 'Tier.v: 10-100 points' WHEN "Total Points" < 10 THEN 'Tier.w: under 10 points' END AS "Tier" , COUNT(1) AS "Total wallets" FROM tiertbl GROUP BY 2 order by 2 asc ) union all ( with tiertbl as (with alltbl AS (WITH general AS ( SELECT DATE_TRUNC('hour', a.BLOCK_TIMESTAMP) AS "Date", CASE WHEN b.TO_ADDRESS IN ( '0x54945180db7943c0ed0fee7edab2bd24620256bc', '0x93c4b944d05dfe6df7645a86cd2206016c51564d', '0x1bee69b7dfffa4e2d53c2a2df135c388ad25dcd2', lower('0x57ba429517c3473B6d34CA9aCd56c0e735b94c02'), lower('0x0Fe4F44beE93503346A3Ac9EE5A26b130a5796d6'), lower('0x13760F50a9d7377e4F20CB8CF9e4c26586c658ff'), lower('0x7CA911E83dabf90C90D3De5411a10F1A6112184'), lower('0x9d7eD45EE2E8FC5482fa2428f15C971e6369011d'), lower('0xa4C637e0F704745D182e4D38cAb7E7485321d059') ) THEN 'Deposit' ELSE 'Withdraw' END AS "Action", DATEDIFF('HOUR', a.BLOCK_TIMESTAMP, CURRENT_TIMESTAMP()) AS "Hours Held", b.SYMBOL AS "Pool", ROUND(b.AMOUNT, 4) AS "Volume", ROUND(b.AMOUNT_USD, 4) AS "USD Volume", a.FROM_ADDRESS AS "User Wallet Address", ROUND(a.TX_FEE, 5) AS "Transaction Fee", ROUND(a.GAS_PRICE, 1) AS "Gas (Gwei)", a.TX_HASH AS "Transaction Hash" FROM ethereum.core.fact_transactions a LEFT JOIN ethereum.core.ez_token_transfers b ON a.tx_hash = b.tx_hash AND a.BLOCK_TIMESTAMP = b.BLOCK_TIMESTAMP WHERE (b.TO_ADDRESS IN ( '0x54945180db7943c0ed0fee7edab2bd24620256bc', '0x93c4b944d05dfe6df7645a86cd2206016c51564d', '0x1bee69b7dfffa4e2d53c2a2df135c388ad25dcd2', lower('0x57ba429517c3473B6d34CA9aCd56c0e735b94c02'), lower('0x0Fe4F44beE93503346A3Ac9EE5A26b130a5796d6'), lower('0x13760F50a9d7377e4F20CB8CF9e4c26586c658ff'), lower('0x7CA911E83dabf90C90D3De5411a10F1A6112184'), lower('0x9d7eD45EE2E8FC5482fa2428f15C971e6369011d'), lower('0xa4C637e0F704745D182e4D38cAb7E7485321d059'), lower('0x8CA7A5d6f3acd3A7A8bC468a8CD0FB14B6BD28b6'), --sfrxETH lower('0xAe60d8180437b5C34bB956822ac2710972584473'), --lsETH lower('0x298aFB19A105D59E74658C4C334Ff360BadE6dd2') --mETH ) OR b.FROM_ADDRESS IN ( '0x54945180db7943c0ed0fee7edab2bd24620256bc', '0x93c4b944d05dfe6df7645a86cd2206016c51564d', '0x1bee69b7dfffa4e2d53c2a2df135c388ad25dcd2', lower('0x57ba429517c3473B6d34CA9aCd56c0e735b94c02'), lower('0x0Fe4F44beE93503346A3Ac9EE5A26b130a5796d6'), lower('0x13760F50a9d7377e4F20CB8CF9e4c26586c658ff'), lower('0x7CA911E83dabf90C90D3De5411a10F1A6112184'), lower('0x9d7eD45EE2E8FC5482fa2428f15C971e6369011d'), lower('0xa4C637e0F704745D182e4D38cAb7E7485321d059'), lower('0x8CA7A5d6f3acd3A7A8bC468a8CD0FB14B6BD28b6'), --sfrxETH lower('0xAe60d8180437b5C34bB956822ac2710972584473'), --lsETH lower('0x298aFB19A105D59E74658C4C334Ff360BadE6dd2') --mETH )) AND a.status = 'SUCCESS' AND b.symbol IN ('cbETH', 'stETH', 'rETH', 'OETH', 'EthX', 'wBETH', 'AnkrETH', 'swETH', 'osETH') ) SELECT *, CASE WHEN "Action" = 'Deposit' THEN "Hours Held" * "Volume" ELSE "Hours Held" * "Volume" * -1 END AS "points" FROM general where "Date" < '2024-02-05 00:00:00.000 ' and "Date" >= '2023-12-18 00:00:00.000 ' ) SELECT "User Wallet Address", -- count (DISTINCT "Transaction Hash") as "Total tx", sum ("points") as "Total Points" from alltbl GROUP by 1 order by 2 desc ) SELECT 'Dec 18th-Feb 5' time, CASE WHEN "Total Points" >= 100000 THEN 'Tier.a: more than 100k points' WHEN "Total Points" >= 90000 and "Total Points" < 100000 THEN 'Tier.b: 90-100k points' WHEN "Total Points" >= 80000 and "Total Points" < 90000 THEN 'Tier.c: 80-90k points' WHEN "Total Points" >= 70000 and "Total Points" < 80000 THEN 'Tier.d: 70-80k points' WHEN "Total Points" >= 60000 and "Total Points" < 70000 THEN 'Tier.e: 60-70k points' WHEN "Total Points" >= 50000 and "Total Points" < 60000 THEN 'Tier.f: 50-60k points' WHEN "Total Points" >= 40000 and "Total Points" < 50000 THEN 'Tier.g: 40-50k points' WHEN "Total Points" >= 30000 and "Total Points" < 40000 THEN 'Tier.h: 30-40k points' WHEN "Total Points" >= 20000 and "Total Points" < 30000 THEN 'Tier.i: 20-30k points' WHEN "Total Points" >= 10000 and "Total Points" < 20000 THEN 'Tier.j: 10-20k points' WHEN "Total Points" >= 9000 and "Total Points" < 10000 THEN 'Tier.k: 9-10k points' WHEN "Total Points" >= 8000 and "Total Points" < 9000 THEN 'Tier.l: 8-9k points' WHEN "Total Points" >= 7000 and "Total Points" < 8000 THEN 'Tier.m: 7-8k points' WHEN "Total Points" >= 6000 and "Total Points" < 7000 THEN 'Tier.n: 6-7k points' WHEN "Total Points" >= 5000 and "Total Points" < 6000 THEN 'Tier.o: 5-6k points' WHEN "Total Points" >= 4000 and "Total Points" < 5000 THEN 'Tier.p: 4-5k points' WHEN "Total Points" >= 3000 and "Total Points" < 4000 THEN 'Tier.q: 3-4k points' WHEN "Total Points" >= 2000 and "Total Points" < 3000 THEN 'Tier.r: 2-3k points' WHEN "Total Points" >= 1000 and "Total Points" < 2000 THEN 'Tier.s: 1-2k points' WHEN "Total Points" >= 500 and "Total Points" < 1000 THEN 'Tier.t: 500-1000 points' WHEN "Total Points" >= 100 and "Total Points" < 500 THEN 'Tier.u: 100-500 points' WHEN "Total Points" >= 10 and "Total Points" < 100 THEN 'Tier.v: 10-100 points' WHEN "Total Points" < 10 THEN 'Tier.w: under 10 points' END AS "Tier" , COUNT(1) AS "Total wallets" FROM tiertbl GROUP BY 2 order by 2 asc ) union all ( with tiertbl as (with alltbl AS (WITH general AS ( SELECT DATE_TRUNC('hour', a.BLOCK_TIMESTAMP) AS "Date", CASE WHEN b.TO_ADDRESS IN ( '0x54945180db7943c0ed0fee7edab2bd24620256bc', '0x93c4b944d05dfe6df7645a86cd2206016c51564d', '0x1bee69b7dfffa4e2d53c2a2df135c388ad25dcd2', lower('0x57ba429517c3473B6d34CA9aCd56c0e735b94c02'), lower('0x0Fe4F44beE93503346A3Ac9EE5A26b130a5796d6'), lower('0x13760F50a9d7377e4F20CB8CF9e4c26586c658ff'), lower('0x7CA911E83dabf90C90D3De5411a10F1A6112184'), lower('0x9d7eD45EE2E8FC5482fa2428f15C971e6369011d'), lower('0xa4C637e0F704745D182e4D38cAb7E7485321d059') ) THEN 'Deposit' ELSE 'Withdraw' END AS "Action", DATEDIFF('HOUR', a.BLOCK_TIMESTAMP, CURRENT_TIMESTAMP()) AS "Hours Held", b.SYMBOL AS "Pool", ROUND(b.AMOUNT, 4) AS "Volume", ROUND(b.AMOUNT_USD, 4) AS "USD Volume", a.FROM_ADDRESS AS "User Wallet Address", ROUND(a.TX_FEE, 5) AS "Transaction Fee", ROUND(a.GAS_PRICE, 1) AS "Gas (Gwei)", a.TX_HASH AS "Transaction Hash" FROM ethereum.core.fact_transactions a LEFT JOIN ethereum.core.ez_token_transfers b ON a.tx_hash = b.tx_hash AND a.BLOCK_TIMESTAMP = b.BLOCK_TIMESTAMP WHERE (b.TO_ADDRESS IN ( '0x54945180db7943c0ed0fee7edab2bd24620256bc', '0x93c4b944d05dfe6df7645a86cd2206016c51564d', '0x1bee69b7dfffa4e2d53c2a2df135c388ad25dcd2', lower('0x57ba429517c3473B6d34CA9aCd56c0e735b94c02'), lower('0x0Fe4F44beE93503346A3Ac9EE5A26b130a5796d6'), lower('0x13760F50a9d7377e4F20CB8CF9e4c26586c658ff'), lower('0x7CA911E83dabf90C90D3De5411a10F1A6112184'), lower('0x9d7eD45EE2E8FC5482fa2428f15C971e6369011d'), lower('0xa4C637e0F704745D182e4D38cAb7E7485321d059'), lower('0x8CA7A5d6f3acd3A7A8bC468a8CD0FB14B6BD28b6'), --sfrxETH lower('0xAe60d8180437b5C34bB956822ac2710972584473'), --lsETH lower('0x298aFB19A105D59E74658C4C334Ff360BadE6dd2') --mETH ) OR b.FROM_ADDRESS IN ( '0x54945180db7943c0ed0fee7edab2bd24620256bc', '0x93c4b944d05dfe6df7645a86cd2206016c51564d', '0x1bee69b7dfffa4e2d53c2a2df135c388ad25dcd2', lower('0x57ba429517c3473B6d34CA9aCd56c0e735b94c02'), lower('0x0Fe4F44beE93503346A3Ac9EE5A26b130a5796d6'), lower('0x13760F50a9d7377e4F20CB8CF9e4c26586c658ff'), lower('0x7CA911E83dabf90C90D3De5411a10F1A6112184'), lower('0x9d7eD45EE2E8FC5482fa2428f15C971e6369011d'), lower('0xa4C637e0F704745D182e4D38cAb7E7485321d059'), lower('0x8CA7A5d6f3acd3A7A8bC468a8CD0FB14B6BD28b6'), --sfrxETH lower('0xAe60d8180437b5C34bB956822ac2710972584473'), --lsETH lower('0x298aFB19A105D59E74658C4C334Ff360BadE6dd2') --mETH )) AND a.status = 'SUCCESS' AND b.symbol IN ('cbETH', 'stETH', 'rETH', 'OETH', 'EthX', 'wBETH', 'AnkrETH', 'swETH', 'osETH') ) SELECT *, CASE WHEN "Action" = 'Deposit' THEN "Hours Held" * "Volume" ELSE "Hours Held" * "Volume" * -1 END AS "points" FROM general where "Date" < '2024-04-16 00:00:00.000 ' and "Date" >= '2024-02-05 00:00:00.000 ' ) SELECT "User Wallet Address", -- count (DISTINCT "Transaction Hash") as "Total tx", sum ("points") as "Total Points" from alltbl GROUP by 1 order by 2 desc ) SELECT 'Feb 5-Apr 16' time, CASE WHEN "Total Points" >= 100000 THEN 'Tier.a: more than 100k points' WHEN "Total Points" >= 90000 and "Total Points" < 100000 THEN 'Tier.b: 90-100k points' WHEN "Total Points" >= 80000 and "Total Points" < 90000 THEN 'Tier.c: 80-90k points' WHEN "Total Points" >= 70000 and "Total Points" < 80000 THEN 'Tier.d: 70-80k points' WHEN "Total Points" >= 60000 and "Total Points" < 70000 THEN 'Tier.e: 60-70k points' WHEN "Total Points" >= 50000 and "Total Points" < 60000 THEN 'Tier.f: 50-60k points' WHEN "Total Points" >= 40000 and "Total Points" < 50000 THEN 'Tier.g: 40-50k points' WHEN "Total Points" >= 30000 and "Total Points" < 40000 THEN 'Tier.h: 30-40k points' WHEN "Total Points" >= 20000 and "Total Points" < 30000 THEN 'Tier.i: 20-30k points' WHEN "Total Points" >= 10000 and "Total Points" < 20000 THEN 'Tier.j: 10-20k points' WHEN "Total Points" >= 9000 and "Total Points" < 10000 THEN 'Tier.k: 9-10k points' WHEN "Total Points" >= 8000 and "Total Points" < 9000 THEN 'Tier.l: 8-9k points' WHEN "Total Points" >= 7000 and "Total Points" < 8000 THEN 'Tier.m: 7-8k points' WHEN "Total Points" >= 6000 and "Total Points" < 7000 THEN 'Tier.n: 6-7k points' WHEN "Total Points" >= 5000 and "Total Points" < 6000 THEN 'Tier.o: 5-6k points' WHEN "Total Points" >= 4000 and "Total Points" < 5000 THEN 'Tier.p: 4-5k points' WHEN "Total Points" >= 3000 and "Total Points" < 4000 THEN 'Tier.q: 3-4k points' WHEN "Total Points" >= 2000 and "Total Points" < 3000 THEN 'Tier.r: 2-3k points' WHEN "Total Points" >= 1000 and "Total Points" < 2000 THEN 'Tier.s: 1-2k points' WHEN "Total Points" >= 500 and "Total Points" < 1000 THEN 'Tier.t: 500-1000 points' WHEN "Total Points" >= 100 and "Total Points" < 500 THEN 'Tier.u: 100-500 points' WHEN "Total Points" >= 10 and "Total Points" < 100 THEN 'Tier.v: 10-100 points' WHEN "Total Points" < 10 THEN 'Tier.w: under 10 points' END AS "Tier" , COUNT(1) AS "Total wallets" FROM tiertbl GROUP BY 2 order by 2 asc ) union all ( with tiertbl as (with alltbl AS (WITH general AS ( SELECT DATE_TRUNC('hour', a.BLOCK_TIMESTAMP) AS "Date", CASE WHEN b.TO_ADDRESS IN ( '0x54945180db7943c0ed0fee7edab2bd24620256bc', '0x93c4b944d05dfe6df7645a86cd2206016c51564d', '0x1bee69b7dfffa4e2d53c2a2df135c388ad25dcd2', lower('0x57ba429517c3473B6d34CA9aCd56c0e735b94c02'), lower('0x0Fe4F44beE93503346A3Ac9EE5A26b130a5796d6'), lower('0x13760F50a9d7377e4F20CB8CF9e4c26586c658ff'), lower('0x7CA911E83dabf90C90D3De5411a10F1A6112184'), lower('0x9d7eD45EE2E8FC5482fa2428f15C971e6369011d'), lower('0xa4C637e0F704745D182e4D38cAb7E7485321d059') ) THEN 'Deposit' ELSE 'Withdraw' END AS "Action", DATEDIFF('HOUR', a.BLOCK_TIMESTAMP, CURRENT_TIMESTAMP()) AS "Hours Held", b.SYMBOL AS "Pool", ROUND(b.AMOUNT, 4) AS "Volume", ROUND(b.AMOUNT_USD, 4) AS "USD Volume", a.FROM_ADDRESS AS "User Wallet Address", ROUND(a.TX_FEE, 5) AS "Transaction Fee", ROUND(a.GAS_PRICE, 1) AS "Gas (Gwei)", a.TX_HASH AS "Transaction Hash" FROM ethereum.core.fact_transactions a LEFT JOIN ethereum.core.ez_token_transfers b ON a.tx_hash = b.tx_hash AND a.BLOCK_TIMESTAMP = b.BLOCK_TIMESTAMP WHERE (b.TO_ADDRESS IN ( '0x54945180db7943c0ed0fee7edab2bd24620256bc', '0x93c4b944d05dfe6df7645a86cd2206016c51564d', '0x1bee69b7dfffa4e2d53c2a2df135c388ad25dcd2', lower('0x57ba429517c3473B6d34CA9aCd56c0e735b94c02'), lower('0x0Fe4F44beE93503346A3Ac9EE5A26b130a5796d6'), lower('0x13760F50a9d7377e4F20CB8CF9e4c26586c658ff'), lower('0x7CA911E83dabf90C90D3De5411a10F1A6112184'), lower('0x9d7eD45EE2E8FC5482fa2428f15C971e6369011d'), lower('0xa4C637e0F704745D182e4D38cAb7E7485321d059'), lower('0x8CA7A5d6f3acd3A7A8bC468a8CD0FB14B6BD28b6'), --sfrxETH lower('0xAe60d8180437b5C34bB956822ac2710972584473'), --lsETH lower('0x298aFB19A105D59E74658C4C334Ff360BadE6dd2') --mETH ) OR b.FROM_ADDRESS IN ( '0x54945180db7943c0ed0fee7edab2bd24620256bc', '0x93c4b944d05dfe6df7645a86cd2206016c51564d', '0x1bee69b7dfffa4e2d53c2a2df135c388ad25dcd2', lower('0x57ba429517c3473B6d34CA9aCd56c0e735b94c02'), lower('0x0Fe4F44beE93503346A3Ac9EE5A26b130a5796d6'), lower('0x13760F50a9d7377e4F20CB8CF9e4c26586c658ff'), lower('0x7CA911E83dabf90C90D3De5411a10F1A6112184'), lower('0x9d7eD45EE2E8FC5482fa2428f15C971e6369011d'), lower('0xa4C637e0F704745D182e4D38cAb7E7485321d059'), lower('0x8CA7A5d6f3acd3A7A8bC468a8CD0FB14B6BD28b6'), --sfrxETH lower('0xAe60d8180437b5C34bB956822ac2710972584473'), --lsETH lower('0x298aFB19A105D59E74658C4C334Ff360BadE6dd2') --mETH )) AND a.status = 'SUCCESS' AND b.symbol IN ('cbETH', 'stETH', 'rETH', 'OETH', 'EthX', 'wBETH', 'AnkrETH', 'swETH', 'osETH') ) SELECT *, CASE WHEN "Action" = 'Deposit' THEN "Hours Held" * "Volume" ELSE "Hours Held" * "Volume" * -1 END AS "points" FROM general where "Date" >= '2024-04-16 00:00:00.000 ' ) SELECT "User Wallet Address", -- count (DISTINCT "Transaction Hash") as "Total tx", sum ("points") as "Total Points" from alltbl GROUP by 1 order by 2 desc ) SELECT 'March 16-now' time, CASE WHEN "Total Points" >= 100000 THEN 'Tier.a: more than 100k points' WHEN "Total Points" >= 90000 and "Total Points" < 100000 THEN 'Tier.b: 90-100k points' WHEN "Total Points" >= 80000 and "Total Points" < 90000 THEN 'Tier.c: 80-90k points' WHEN "Total Points" >= 70000 and "Total Points" < 80000 THEN 'Tier.d: 70-80k points' WHEN "Total Points" >= 60000 and "Total Points" < 70000 THEN 'Tier.e: 60-70k points' WHEN "Total Points" >= 50000 and "Total Points" < 60000 THEN 'Tier.f: 50-60k points' WHEN "Total Points" >= 40000 and "Total Points" < 50000 THEN 'Tier.g: 40-50k points' WHEN "Total Points" >= 30000 and "Total Points" < 40000 THEN 'Tier.h: 30-40k points' WHEN "Total Points" >= 20000 and "Total Points" < 30000 THEN 'Tier.i: 20-30k points' WHEN "Total Points" >= 10000 and "Total Points" < 20000 THEN 'Tier.j: 10-20k points' WHEN "Total Points" >= 9000 and "Total Points" < 10000 THEN 'Tier.k: 9-10k points' WHEN "Total Points" >= 8000 and "Total Points" < 9000 THEN 'Tier.l: 8-9k points' WHEN "Total Points" >= 7000 and "Total Points" < 8000 THEN 'Tier.m: 7-8k points' WHEN "Total Points" >= 6000 and "Total Points" < 7000 THEN 'Tier.n: 6-7k points' WHEN "Total Points" >= 5000 and "Total Points" < 6000 THEN 'Tier.o: 5-6k points' WHEN "Total Points" >= 4000 and "Total Points" < 5000 THEN 'Tier.p: 4-5k points' WHEN "Total Points" >= 3000 and "Total Points" < 4000 THEN 'Tier.q: 3-4k points' WHEN "Total Points" >= 2000 and "Total Points" < 3000 THEN 'Tier.r: 2-3k points' WHEN "Total Points" >= 1000 and "Total Points" < 2000 THEN 'Tier.s: 1-2k points' WHEN "Total Points" >= 500 and "Total Points" < 1000 THEN 'Tier.t: 500-1000 points' WHEN "Total Points" >= 100 and "Total Points" < 500 THEN 'Tier.u: 100-500 points' WHEN "Total Points" >= 10 and "Total Points" < 100 THEN 'Tier.v: 10-100 points' WHEN "Total Points" < 10 THEN 'Tier.w: under 10 points' END AS "Tier" , COUNT(1) AS "Total wallets" FROM tiertbl GROUP BY 2 order by 2 asc )

EigenLayer