Haisenbergwash-trades-by-type
    Updated 2024-10-13
    -- forked from alitaslimi / Marketplaces Wash Trades Over Time @ https://flipsidecrypto.xyz/alitaslimi/q/Na3ZbKDaghcc/marketplaces-wash-trades-over-time

    WITH
    -------------------- Raw Transfer Transactions --------------------
    transfers AS (
    SELECT
    block_timestamp,
    tx_hash,
    from_address,
    to_address,
    amount

    FROM aptos.core.ez_native_transfers
    ),
    -------------------- Raw Sale Transactions --------------------
    transactions AS (
    SELECT
    block_timestamp,
    tx_hash,
    platform_name as marketplace,
    seller_address,
    buyer_address,
    nft_address,
    tokenid,
    total_price,
    total_price_usd,
    count(tx_hash) over (partition by buyer_address, nft_address, tokenid) as buyer_count,
    count(tx_hash) over (partition by seller_address, nft_address, tokenid) as seller_count
    FROM aptos.nft.ez_nft_sales
    WHERE currency_address = '0x1::aptos_coin::AptosCoin'
    ),
    wash_trades AS (

    -- Case I: Self-funded wash trades
    SELECT
    sales.tx_hash,
    Last run: 3 months ago
    Date
    Type
    Sales
    Volume
    1
    2024-07-14 00:00:00.000I42
    2
    2024-07-14 00:00:00.000II32
    3
    2024-07-14 00:00:00.000III12
    4
    2024-07-14 00:00:00.000IV320
    5
    2024-07-15 00:00:00.000I869
    6
    2024-07-15 00:00:00.000II321
    7
    2024-07-15 00:00:00.000III23
    8
    2024-07-15 00:00:00.000IV33
    9
    2024-07-16 00:00:00.000I1026
    10
    2024-07-16 00:00:00.000II28
    11
    2024-07-17 00:00:00.000I1020
    12
    2024-07-17 00:00:00.000II320
    13
    2024-07-17 00:00:00.000III11
    14
    2024-07-17 00:00:00.000IV11
    15
    2024-07-18 00:00:00.000I435
    16
    2024-07-18 00:00:00.000II10
    17
    2024-07-19 00:00:00.000I9133
    18
    2024-07-19 00:00:00.000II62
    19
    2024-07-19 00:00:00.000III22
    20
    2024-07-19 00:00:00.000IV42
    ...
    234
    9KB
    57s