MLDZMNsol.open10
    Updated 2022-11-25
    with tb2 as (select block_timestamp::date as day,
    avg (swap_to_amount/swap_from_amount) as price_token
    from solana.fact_swaps
    where swap_from_mint = 'So11111111111111111111111111111111111111112'
    and swap_to_mint in ('EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v','Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB')
    and swap_to_amount > 0
    and swap_from_amount > 0
    and succeeded = 'TRUE'
    group by 1),
    buy as (
    select
    purchaser as buyer,
    sum(SALES_AMOUNT*price_token) as volume_usd_buy
    from solana.core.fact_nft_sales x join tb2 y on x.BLOCK_TIMESTAMP::date=y.day
    where SUCCEEDED='TRUE'
    and BLOCK_TIMESTAMP>=CURRENT_DATE- {{Time_period}}
    and MARKETPLACE = '{{Marketplace}}'
    and SALES_AMOUNT>0
    group by 1 having volume_usd_buy is not null
    ),
    sel as (select
    seller as seller,
    sum(SALES_AMOUNT*price_token) as volume_usd_sell
    from solana.core.fact_nft_sales x join tb2 y on x.BLOCK_TIMESTAMP::date=y.day
    where SUCCEEDED='TRUE'
    and BLOCK_TIMESTAMP>=CURRENT_DATE- {{Time_period}}
    and MARKETPLACE = '{{Marketplace}}'
    and SALES_AMOUNT>0
    group by 1 having volume_usd_sell is not null
    )
    select
    buyer as nft_trader,
    sum (volume_usd_sell - volume_usd_buy) as profit_usd,
    row_number() over (order by profit_usd desc) as rank1
    Run a query to Download Data