WITH fresh_wallet_traders AS ( SELECT from_address, MIN(block_timestamp) AS first_trade FROM ethereum.core.fact_transactions WHERE from_address IN ( SELECT trader FROM crosschain.defi.ez_dex_swaps WHERE block_timestamp > CURRENT_TIMESTAMP() - INTERVAL '7 DAYS' ) GROUP BY from_address HAVING MIN(block_timestamp) > CURRENT_TIMESTAMP() - INTERVAL '7 DAYS' ), fresh_wallet_buys AS ( SELECT token_out, SUM( CASE WHEN block_timestamp > CURRENT_TIMESTAMP() - INTERVAL '1 DAY' THEN amount_out_usd ELSE 0 END ) AS inflow_1d, SUM( CASE WHEN block_timestamp > CURRENT_TIMESTAMP() - INTERVAL '2 DAYS' AND block_timestamp <= CURRENT_TIMESTAMP() - INTERVAL '1 DAY' THEN amount_out_usd ELSE 0 END ) AS inflow_2d, SUM( CASE WHEN block_timestamp > CURRENT_TIMESTAMP() - INTERVAL '3 DAYS' AND block_timestamp <= CURRENT_TIMESTAMP() - INTERVAL '2 DAYS' THEN amount_out_usd ELSE 0 END ) AS inflow_3d, SUM( CASE WHEN block_timestamp > CURRENT_TIMESTAMP() - INTERVAL '4 DAYS' AND block_timestamp <= CURRENT_TIMESTAMP() - INTERVAL '3 DAYS' THEN amount_out_usd ELSE 0 END ) AS inflow_4d, SUM( CASE WHEN block_timestamp > CURRENT_TIMESTAMP() - INTERVAL '5 DAYS' AND block_timestamp <= CURRENT_TIMESTAMP() - INTERVAL '4 DAYS' THEN amount_out_usd ELSE 0 END ) AS inflow_5d, SUM( CASE WHEN block_timestamp > CURRENT_TIMESTAMP() - INTERVAL '6 DAYS' AND block_timestamp <= CURRENT_TIMESTAMP() - INTERVAL '5 DAYS' THEN amount_out_usd ELSE 0 END ) AS inflow_6d, SUM( CASE WHEN block_timestamp > CURRENT_TIMESTAMP() - INTERVAL '7 DAYS' AND block_timestamp <= CURRENT_TIMESTAMP() - INTERVAL '6 DAYS' THEN amount_out_usd ELSE 0 END ) AS inflow_7d FROM crosschain.defi.ez_dex_swaps WHERE trader IN ( SELECT from_address FROM fresh_wallet_traders ) AND amount_out_usd IS NOT NULL AND blockchain = 'ethereum' GROUP BY token_out HAVING SUM(amount_out_usd) > 1000 ), daily_prices AS ( SELECT DATE(HOUR) AS date, token_address, PRICE, ROW_NUMBER() OVER (PARTITION BY DATE(HOUR), token_address ORDER BY PRICE) AS row_num, COUNT(*) OVER (PARTITION BY DATE(HOUR), token_address) AS cnt FROM crosschain.price.ez_prices_hourly ), median_prices AS ( SELECT date, token_address, PRICE, cnt, row_num, CASE WHEN cnt % 2 = 1 AND row_num = (cnt + 1) / 2 THEN PRICE WHEN cnt % 2 = 0 AND (row_num = cnt / 2 OR row_num = cnt / 2 + 1) THEN PRICE END AS median_price FROM daily_prices ), daily_median_prices AS ( SELECT date, token_address, AVG(median_price) AS median_price FROM median_prices WHERE median_price IS NOT NULL GROUP BY date, token_address ) SELECT DISTINCT dim_contracts.symbol AS "Symbol", fresh_wallet_buys.token_out AS "Token Address", CASE WHEN fresh_wallet_buys.inflow_1d < 0.004 THEN to_varchar(fresh_wallet_buys.inflow_1d * 100000, '$999,999,999') ELSE to_varchar(fresh_wallet_buys.inflow_1d, '$999,999,999') END AS "Flow_1d_ago", CASE WHEN m1.median_price < 0.004 THEN to_varchar(m1.median_price * 100000, '$999,999,999.999') ELSE to_varchar(m1.median_price, '$999,999,999.999') END AS "Price_1d_ago", CASE WHEN fresh_wallet_buys.inflow_2d < 0.004 THEN to_varchar(fresh_wallet_buys.inflow_2d * 100000, '$999,999,999') ELSE to_varchar(fresh_wallet_buys.inflow_2d, '$999,999,999') END AS "Flow_2d_ago", CASE WHEN m2.median_price < 0.004 THEN to_varchar(m2.median_price * 100000, '$999,999,999.999') ELSE to_varchar(m2.median_price, '$999,999,999.999') END AS "Price_2d_ago", CASE WHEN fresh_wallet_buys.inflow_3d < 0.004 THEN to_varchar(fresh_wallet_buys.inflow_3d * 100000, '$999,999,999') ELSE to_varchar(fresh_wallet_buys.inflow_3d, '$999,999,999') END AS "Flow_3d_ago", CASE WHEN m3.median_price < 0.004 THEN to_varchar(m3.median_price * 100000, '$999,999,999.999') ELSE to_varchar(m3.median_price, '$999,999,999.999') END AS "Price_3d_ago", CASE WHEN fresh_wallet_buys.inflow_4d < 0.004 THEN to_varchar(fresh_wallet_buys.inflow_4d * 100000, '$999,999,999') ELSE to_varchar(fresh_wallet_buys.inflow_4d, '$999,999,999') END AS "Flow_4d_ago", CASE WHEN m4.median_price < 0.004 THEN to_varchar(m4.median_price * 100000, '$999,999,999.999') ELSE to_varchar(m4.median_price, '$999,999,999.999') END AS "Price_4d_ago", CASE WHEN fresh_wallet_buys.inflow_5d < 0.004 THEN to_varchar(fresh_wallet_buys.inflow_5d * 100000, '$999,999,999') ELSE to_varchar(fresh_wallet_buys.inflow_5d, '$999,999,999') END AS "Flow_5d_ago", CASE WHEN m5.median_price < 0.004 THEN to_varchar(m5.median_price * 1000, '$999,999,999.999') ELSE to_varchar(m5.median_price, '$999,999,999.999') END AS "Price_5d_ago", CASE WHEN fresh_wallet_buys.inflow_6d < 0.004 THEN to_varchar(fresh_wallet_buys.inflow_6d * 1000, '$999,999,999') ELSE to_varchar(fresh_wallet_buys.inflow_6d, '$999,999,999') END AS "Flow_6d_ago", CASE WHEN m6.median_price < 0.004 THEN to_varchar(m6.median_price * 100000, '$999,999,999.999') ELSE to_varchar(m6.median_price, '$999,999,999.999') END AS "Price_6d_ago", CASE WHEN fresh_wallet_buys.inflow_7d < 0.004 THEN to_varchar(fresh_wallet_buys.inflow_7d * 100000, '$999,999,999') ELSE to_varchar(fresh_wallet_buys.inflow_7d, '$999,999,999') END AS "Flow_7d_ago", CASE WHEN m7.median_price < 0.004 THEN to_varchar(m7.median_price * 100000, '$999,999,999.999') ELSE to_varchar(m7.median_price, '$999,999,999.999') END AS "Price_7d_ago", MIN(fact_transactions.block_timestamp) AS "Liquidity Pool Created On" FROM fresh_wallet_buys INNER JOIN crosschain.core.dim_contracts dim_contracts ON dim_contracts.address = fresh_wallet_buys.token_out INNER JOIN ethereum.core.fact_transactions fact_transactions ON fact_transactions.to_address = fresh_wallet_buys.token_out LEFT JOIN daily_median_prices m1 ON m1.token_address = fresh_wallet_buys.token_out AND m1.date = CURRENT_DATE - INTERVAL '1 DAY' LEFT JOIN daily_median_prices m2 ON m2.token_address = fresh_wallet_buys.token_out AND m2.date = CURRENT_DATE - INTERVAL '2 DAYS' LEFT JOIN daily_median_prices m3 ON m3.token_address = fresh_wallet_buys.token_out AND m3.date = CURRENT_DATE - INTERVAL '3 DAYS' LEFT JOIN daily_median_prices m4 ON m4.token_address = fresh_wallet_buys.token_out AND m4.date = CURRENT_DATE - INTERVAL '4 DAYS' LEFT JOIN daily_median_prices m5 ON m5.token_address = fresh_wallet_buys.token_out AND m5.date = CURRENT_DATE - INTERVAL '5 DAYS' LEFT JOIN daily_median_prices m6 ON m6.token_address = fresh_wallet_buys.token_out AND m6.date = CURRENT_DATE - INTERVAL '6 DAYS' LEFT JOIN daily_median_prices m7 ON m7.token_address = fresh_wallet_buys.token_out AND m7.date = CURRENT_DATE - INTERVAL '7 DAYS' WHERE dim_contracts.symbol NOT LIKE '%USD%' AND dim_contracts.symbol NOT LIKE '%DAI%' AND dim_contracts.symbol NOT LIKE '%ETH%' AND dim_contracts.symbol NOT LIKE '%BTC%' AND dim_contracts.symbol NOT LIKE '%EUR%' GROUP BY fresh_wallet_buys.token_out, dim_contracts.symbol, fresh_wallet_buys.inflow_1d, fresh_wallet_buys.inflow_2d, fresh_wallet_buys.inflow_3d, fresh_wallet_buys.inflow_4d, fresh_wallet_buys.inflow_5d, fresh_wallet_buys.inflow_6d, fresh_wallet_buys.inflow_7d, m1.median_price, m2.median_price, m3.median_price, m4.median_price, m5.median_price, m6.median_price, m7.median_price ORDER BY "Flow_1d_ago" DESC, "Flow_7d_ago" DESC LIMIT 100000;

Stealth money