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, '2024-01-17 12:00:11.000') 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 ) 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 CASE WHEN "Total Points" > 50000 THEN 'Tier1: more than 50k points' WHEN "Total Points" > 25000 and "Total Points" <= 50000 THEN 'Tier2: 25k-50k points' WHEN "Total Points" > 15000 and "Total Points" <= 25000 THEN 'Tier3: 15k-25k points' WHEN "Total Points" > 10000 and "Total Points" <= 15000 THEN 'Tier4: 10k-15k points' WHEN "Total Points" >= 5000 and "Total Points" <= 10000 THEN 'Tier5: 5k-10k points' WHEN "Total Points" >= 2500 and "Total Points" <= 5000 THEN 'Tier6: 5k-2500 points' WHEN "Total Points" >= 1000 and "Total Points" <= 2500 THEN 'Tier7: 2500-1000 points' WHEN "Total Points" >= 500 and "Total Points" <= 1000 THEN 'Tier8: 500-1000 points' WHEN "Total Points" >= 300 and "Total Points" <= 500 THEN 'Tier9: 300-500 points' WHEN "Total Points" >= 100 and "Total Points" <= 300 THEN 'Tier10: 100-300 points' WHEN "Total Points" < 100 THEN 'Tier11: not eligible' END AS "Tier" , COUNT(1) AS "Total wallets" FROM tiertbl GROUP BY 1 order by 1 asc

0xhashx