-- not considered liquidations bend + nftx buybacks with eth_price_extended as ( SELECT date_trunc('day', block_timestamp) as "day", AVG(CASE WHEN block_timestamp <= '2017-11-04' THEN 300 WHEN block_timestamp > '2017-11-04' AND block_timestamp <= '2019-12-10' THEN 400 WHEN block_timestamp > '2017-12-10' AND block_timestamp <= '2018-01-01' THEN 750 WHEN block_timestamp > '2018-01-01' AND block_timestamp <= '2018-02-01' THEN 1000 WHEN block_timestamp > '2018-01-01' AND block_timestamp <= '2018-02-04' THEN 1000 WHEN block_timestamp > '2018-02-04' AND block_timestamp <= '2018-03-03' THEN 800 WHEN block_timestamp > '2018-03-03' AND block_timestamp <= '2018-03-20' THEN 600 WHEN block_timestamp > '2018-03-20' AND block_timestamp <= '2018-04-10' THEN 400 WHEN block_timestamp > '2018-04-10' AND block_timestamp <= '2018-06-15' THEN 600 WHEN block_timestamp > '2018-06-15' AND block_timestamp <= '2018-08-01' THEN 400 WHEN block_timestamp > '2018-08-01' AND block_timestamp <= '2018-11-14' THEN 200 WHEN block_timestamp > '2018-11-14' AND block_timestamp <= '2019-05-01' THEN 150 END ) as eth_price FROM ethereum.core.fact_blocks WHERE block_timestamp <= '2019-05-01' AND block_timestamp >= '2017-06-23' GROUP BY 1 UNION -- Uniswap v1 ETH/USDC price SELECT date_trunc('day', t.block_timestamp) as "day", MEDIAN(raw_amount / 1e6 / amount) as eth_price FROM ( SELECT block_timestamp, tx_hash, raw_amount FROM ethereum.core.fact_token_transfers WHERE contract_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' AND (to_address = '0x97dec872013f6b5fb443861090ad931542878126' OR from_address = '0x97dec872013f6b5fb443861090ad931542878126')) t INNER JOIN (SELECT block_timestamp, tx_hash, amount FROM ethereum.core.ez_eth_transfers WHERE eth_to_address = '0x97dec872013f6b5fb443861090ad931542878126' OR eth_from_address = '0x97dec872013f6b5fb443861090ad931542878126') e ON (t.tx_hash = e.tx_hash) WHERE "day" <= '2020-06-01' AND "day" > '2019-05-01' GROUP BY 1 UNION -- Uniswap v2 ETH/USDC price SELECT date_trunc('day', t.block_timestamp) as "day", MEDIAN(t.raw_amount / 1e6 / (e.raw_amount / 1e18)) as eth_price FROM ( SELECT block_timestamp, tx_hash, raw_amount FROM ethereum.core.fact_token_transfers WHERE contract_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' AND (to_address = '0xb4e16d0168e52d35cacd2c6185b44281ec28c9dc' OR from_address = '0xb4e16d0168e52d35cacd2c6185b44281ec28c9dc')) t INNER JOIN (SELECT block_timestamp, tx_hash, raw_amount FROM ethereum.core.fact_token_transfers WHERE contract_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' AND (to_address = '0xb4e16d0168e52d35cacd2c6185b44281ec28c9dc' OR from_address = '0xb4e16d0168e52d35cacd2c6185b44281ec28c9dc') AND raw_amount !=0 ) e ON (t.tx_hash = e.tx_hash) WHERE "day" > '2020-06-01' GROUP BY 1 ORDER BY 1 ), sales as (SELECT block_timestamp, n.tx_hash, coalesce(nft_to_address, buyer_address) as buyer, seller_address as seller, tokenid, CASE WHEN price2 IS NOT NULL THEN price2 ELSE price END as price_eth, CASE WHEN price_usd IS NOT NULL AND price_usd !=0 THEN price_usd ELSE eth_price * price_eth END as price_usd FROM ethereum.core.ez_nft_sales n LEFT JOIN (SELECT tx_hash, tokenflow_eth.hextoint(right(input_data, 64))/1e18 as price2 FROM ethereum.core.fact_transactions WHERE origin_function_signature = '0x23165b75') e ON (n.tx_hash = e.tx_hash) LEFT JOIN (SELECT tx_hash, concat('0x', right(topics[2], 40)) as nft_to_address FROM ethereum.core.fact_event_logs WHERE contract_address = '0xb47e3cd837ddf8e4c57f05d70ab865de6e193bbb' AND topics[0] = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef' AND origin_function_signature = '0x23165b75') t ON (n.tx_hash = t.tx_hash) LEFT JOIN eth_price_extended ON (date_trunc('day', block_timestamp) = "day" AND (price_usd IS NULL OR price_usd = 0)) WHERE nft_address = '0xb47e3cd837ddf8e4c57f05d70ab865de6e193bbb' AND price_eth != 0 AND n.tx_hash IN (SELECT DISTINCT tx_hash FROM ethereum.core.fact_event_logs WHERE contract_address = '0xb47e3cd837ddf8e4c57f05d70ab865de6e193bbb' AND topics[0] = '0x58e5d5a525e3b40bc15abaa38b5882678db1ee68befd2f60bafe3a7fd06db9e3') ORDER BY 1) -- SELECT * FROM sales SELECT address, SUM(price_usd), MAX(CASE WHEN price_usd > 0 THEN price_usd END) / MIN(CASE WHEN price_usd < 0 THEN -1 * price_usd END) as "Max profit to min expense", MEDIAN(CASE WHEN price_usd > 0 THEN price_usd END) / MEDIAN(CASE WHEN price_usd < 0 THEN -1 * price_usd END) as "Median profit to median expense", SUM(amount) as "Total punks balance" FROM (SELECT buyer as address, -1 * price_usd as price_usd, 1 as amount FROM sales UNION SELECT seller as address, price_usd, -1 as amount FROM sales) GROUP BY 1 ORDER BY 1

Methodology