rain_syndicaNewly Created Wallets First Interaction Between Marketplace Sales copy
    Updated 2024-02-07
    with

    first_tx as (
    select
    tx_to as user,
    min(date_trunc('day', block_timestamp)) as creation_date
    from solana.core.fact_transfers
    where mint = 'So11111111111111111111111111111111111111112'
    GROUP BY 1
    having creation_date >= '2024-01-01' and creation_date < '2024-02-01'
    )

    , nft_sales AS (
    SELECT
    tx_id,
    block_timestamp,
    purchaser AS buyer,
    seller,
    CASE
    when marketplace in ('Magic Eden', 'magic eden v2', 'magic eden v1') then 'magic eden'
    else marketplace
    end as marketplace,
    sales_amount AS sol_amount,
    (sales_amount * close) AS usd_amount,
    'Regular NFT' AS type
    FROM solana.nft.fact_nft_sales a LEFT JOIN solana.price.fact_token_prices_hourly b
    ON date_trunc('hour', a.block_timestamp) = b.recorded_hour
    WHERE succeeded = TRUE
    AND block_timestamp < '2024-02-01'
    AND block_timestamp >= '2024-01-01'
    AND b.symbol = 'SOL'
    and provider = 'coinmarketcap'
    )

    , cnft_me AS (
    SELECT
    Last run: about 1 year ago
    Day
    Marketplace
    First Time Users
    1
    2024-01-01 00:00:00.000magic eden127
    2
    2024-01-01 00:00:00.000tensorswap213
    3
    2024-01-02 00:00:00.000magic eden141
    4
    2024-01-02 00:00:00.000tensorswap390
    5
    2024-01-03 00:00:00.000magic eden225
    6
    2024-01-03 00:00:00.000tensorswap2048
    7
    2024-01-04 00:00:00.000magic eden301
    8
    2024-01-04 00:00:00.000tensorswap1544
    9
    2024-01-05 00:00:00.000magic eden263
    10
    2024-01-05 00:00:00.000tensorswap1219
    11
    2024-01-06 00:00:00.000magic eden239
    12
    2024-01-06 00:00:00.000tensorswap1047
    13
    2024-01-07 00:00:00.000magic eden240
    14
    2024-01-07 00:00:00.000tensorswap826
    15
    2024-01-08 00:00:00.000magic eden215
    16
    2024-01-08 00:00:00.000tensorswap1476
    17
    2024-01-09 00:00:00.000magic eden308
    18
    2024-01-09 00:00:00.000tensorswap1087
    19
    2024-01-10 00:00:00.000magic eden267
    20
    2024-01-10 00:00:00.000tensorswap1181
    62
    3KB
    807s