Sandesheth_nft wash trading
Updated 2022-11-29
999
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
-- reference : https://app.flipsidecrypto.com/dashboard/fCW2IB
------ v2 Updates / 24th Oct 2022
-- Simplified Wash Volumes Filter: coalesce function - so long it is flagged wash -> label as wash
-- [3] Transfer wash: A sells to B, B transfers back NFT to A, A sells to B again
-- Added oracle prices of WETH to estimate Prices of non-ETH sales to ETH
-- Removed filters for ETH/WETH sales
-- Fix erroneous ETH prices on Sudoswap sales
------ v1 Genesis / 5 Oct 2022
-- [1] Point of sales wash: A sells to B, B sells back to A
-- [2] Self-funded wash: A funds B, A sells to B
-- Misc: To take note while working on ETH tables
-- Q: can ez_nft_sales tx_hash be found in ez_nft_transfers // YES // i.e. sales are a subset of transfers, mints are also in transfers
-- Q: Are ETH sales from ethereum.core.ez_nft_sales found in ethereum.core.ez_eth_transfers? // YES // i.e. you need to EXCLUDE tx_hash in ez_eth_transfers that are from ez_nft_sales
WITH ORACLE_PRICES AS (
SELECT
hour,
price as eth_usd
FROM ethereum.core.fact_hourly_token_prices
WHERE symbol = 'WETH'
AND token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'
),
eth_weth_transfers AS ( -- combine WETH and ETH transfers
SELECT
date_trunc('day', block_timestamp) as date,
eth_from_address, -- A
eth_to_address, -- B
amount as eth
FROM ethereum.core.ez_eth_transfers
WHERE 1=1
AND block_timestamp > '2019-12-01'
AND eth_from_address IN (SELECT distinct buyer_address FROM ethereum.core.ez_nft_sales) -- filter out users that traded NFT
AND tx_hash NOT IN (SELECT distinct tx_hash FROM ethereum.core.ez_nft_sales) -- filter out ETH transfer from buying/selling
UNION
Run a query to Download Data