WITH price AS ( SELECT hour , symbol , avg(price) as avg_price FROM optimism.core.fact_hourly_token_prices WHERE hour::date >= CURRENT_DATE - {{date_range}} GROUP BY 1, 2 ) , nft_sales AS ( SELECT t1.block_timestamp , t1.tx_hash , t1.platform_name , t1.seller_address , t1.buyer_address , CASE WHEN project_name IS NOT NULL THEN project_name WHEN nft_address = '0x0110bb5739a6f82eafc748418e572fc67d854a0f' THEN 'Early Optimists' WHEN nft_address = '0xfa14e1157f35e1dad95dc3f822a9d18c40e360e2' THEN 'Optimism Quests' WHEN nft_address = '0xac3b9b3f5956b52c448158c0a07ddfa9d5c53a3b' THEN 'OP Delegatooors' END AS collection , t1.tokenid AS token_id , CASE WHEN t1.price_usd IS NULL THEN t1.price*t2.avg_price ELSE t1.price_usd END AS sale_price FROM optimism.core.ez_nft_sales AS t1 LEFT JOIN price AS t2 ON t1.currency_symbol = t2.symbol LEFT JOIN optimism.core.dim_labels as t3 ON t1.nft_address = t3.address -- WHERE block_timestamp::date >= CURRENT_DATE - {{date_range}} WHERE collection = '{{collection}}' ) , buy AS ( SELECT block_timestamp , 'buy' AS sale_action , buyer_address AS wallet , token_id , sale_price FROM nft_sales ) , sell AS ( SELECT block_timestamp , 'sell' AS sale_action , seller_address AS wallet , token_id , sale_price FROM nft_sales ) , union_ AS ( SELECT * , row_number() over (PARTITION BY wallet, token_id ORDER BY block_timestamp) AS action_order FROM (SELECT * FROM buy UNION SELECT * FROM sell) ) , agg_ AS ( SELECT t1.wallet , t1.token_id , t1.block_timestamp AS sale_timestamp , t2.block_timestamp AS buy_timestamp , DATEDIFF('day', buy_timestamp, sale_timestamp) as day_hold , t1.sale_price , t2.sale_price AS buy_price , t1.sale_price - t2.sale_price AS profit_usd FROM union_ AS t1 LEFT JOIN union_ AS t2 ON t1.wallet = t2.wallet AND t1.token_id = t2.token_id AND t1.sale_action = 'sell' AND t2.sale_action = 'buy' AND t1.action_order = t2.action_order + 1 AND t1.block_timestamp::date >= CURRENT_DATE - {{date_range}} WHERE day_hold >= 0 ) SELECT CASE WHEN day_hold <= 1 THEN 'hold ≤ 1 day' WHEN day_hold <= 7 THEN '1 day < hold ≤ 1 week' WHEN day_hold <= 30 THEN '1 week < hold ≤ 1 month' WHEN day_hold <= 90 THEN '1 month < hold ≤ 3 months' WHEN day_hold <= 180 THEN '3 months < hold ≤ 1 year' ELSE '1 year < hold' END AS hold_duration , CASE WHEN hold_duration = 'hold ≤ 1 day' THEN '1. hold ≤ 1 day' WHEN hold_duration = '1 day < hold ≤ 1 week' THEN '2. 1 day < hold ≤ 1 week' WHEN hold_duration = '1 week < hold ≤ 1 month' THEN '3. 1 week < hold ≤ 1 month' WHEN hold_duration = '1 month < hold ≤ 3 months' THEN '4. 1 month < hold ≤ 3 months' WHEN hold_duration = '3 months < hold ≤ 1 year' THEN '5. 3 months < hold ≤ 1 year' WHEN hold_duration = '1 year < hold' THEN '6. 1 year < hold' END AS hold_numbering , CAST(COUNT(DISTINCT wallet) AS float) AS wallet_count , AVG(profit_usd) AS avg_profit FROM agg_ GROUP BY 1, 2 ORDER BY 2 -- SELECT -- hold_duration -- , CASE -- WHEN hold_duration = 'hold ≤ 1 day' THEN '1. hold ≤ 1 day' -- WHEN hold_duration = '1 day < hold ≤ 1 week' THEN '2. 1 day < hold ≤ 1 week' -- WHEN hold_duration = '1 week < hold ≤ 1 month' THEN '3. 1 week < hold ≤ 1 month' -- WHEN hold_duration = '1 month < hold ≤ 3 months' THEN '4. 1 month < hold ≤ 3 months' -- WHEN hold_duration = '3 months < hold ≤ 1 year' THEN '5. 3 months < hold ≤ 1 year' -- WHEN hold_duration = '1 year < hold' THEN '6. 1 year < hold' -- END AS hold_numbering -- , measure -- , CASE -- WHEN measure = 'WALLET_COUNT' THEN '1. wallet count' -- WHEN measure = 'AVG_PROFIT' THEN '2. average profit (USD)' -- END as measure_numbering -- , value -- FROM ( -- SELECT -- CASE -- WHEN day_hold <= 1 THEN 'hold ≤ 1 day' -- WHEN day_hold <= 7 THEN '1 day < hold ≤ 1 week' -- WHEN day_hold <= 30 THEN '1 week < hold ≤ 1 month' -- WHEN day_hold <= 90 THEN '1 month < hold ≤ 3 months' -- WHEN day_hold <= 180 THEN '3 months < hold ≤ 1 year' -- ELSE '1 year < hold' END AS hold_duration -- , CAST(COUNT(DISTINCT wallet) AS float) AS wallet_count -- , AVG(profit_usd) AS avg_profit -- FROM agg_ -- GROUP BY 1 -- ) unpivot (value FOR measure in (wallet_count, avg_profit)) -- ORDER BY measure_numbering, hold_numbering

Secondary NFT Market Mega Dashboard