MLDZMNTop domain creators on .bonk
    Updated 2023-08-14
    with t2 as (select
    RECORDED_HOUR::date as day,
    avg(close) as price_bonk
    from solana.core.fact_token_prices_hourly
    where SYMBOL ilike 'bonk'
    group by 1),

    tb1 as (select
    signers[0] as user,
    count(distinct tx_id) as no_contrubition,
    count(distinct tx_id)*69000000 as volume_bonk,
    count(distinct tx_id)*69000000*price_bonk as volume_usd
    from solana.core.fact_events s
    left join t2 on date_trunc('day',s.block_timestamp)=t2.day
    WHERE program_id = 'TLDHkysf5pCnKsVA4gXpNvmy7psXLPEu4LAdDJthT9S'
    and inner_instruction:instructions[0]:parsed:info:mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263'
    AND inner_instruction:instructions[0]:parsed:type = 'burn'
    and BLOCK_TIMESTAMP>='2022-10-21'
    group by 1, price_bonk
    )

    select
    Distinct user as "User",
    sum(no_contrubition) as "Total domains",
    sum(volume_bonk) as "Total volume in BONK",
    sum(volume_usd) as "Total volume in USD"
    from tb1
    group by 1
    order by 2 desc
    limit 50

    Run a query to Download Data