MLDZMNXAR-com-6
    Updated 2023-01-27
    with tb1 as (select
    BLOCK_TIMESTAMP,
    Case when marketplace ilike '%magic%' then 'Magic Eden' else marketplace end as marketplaces,
    purchaser,
    ROW_NUMBER() OVER (partition by purchaser order by BLOCK_TIMESTAMP) as t_n
    from solana.core.fact_nft_sales
    where SUCCEEDED='TRUE'
    and SALES_AMOUNT>0
    ),
    tb2 as (select
    marketplaces,
    BLOCK_TIMESTAMP as first_transaction,
    purchaser
    from tb1
    where t_n=1),

    tb3 as (select
    marketplaces,
    BLOCK_TIMESTAMP as second_transaction,
    purchaser
    from tb1
    where purchaser in (select purchaser from tb2)
    and t_n=2)
    ,

    tb4 as (select
    tb2.marketplaces,
    tb2.purchaser,
    avg(DATEDIFF(day,first_transaction, second_transaction )) as time_between
    from tb2
    join tb3 on tb2.purchaser=tb3.purchaser
    group by 1,2)

    select
    marketplaces,
    Run a query to Download Data