MLDZMNhxro2
    Updated 2023-05-12
    with tb2 AS (
    select
    RECORDED_HOUR::date as day,
    SYMBOL,
    TOKEN_ADDRESS,
    avg(close) as price_token
    from solana.core.ez_token_prices_hourly
    group by 1,2,3)


    select
    s.BLOCK_TIMESTAMP::date as date,
    'Short position' as position,
    count(distinct s.tx_id) as no_positions,
    sum(AMOUNT*price_token) as volume,
    avg(AMOUNT*price_token) as avg_volume
    from solana.core.fact_transactions s
    left join solana.core.fact_transfers a on s.tx_id=a.tx_id
    join tb2 on a.mint=tb2.TOKEN_ADDRESS
    where INSTRUCTIONS[0]:programId='GUhB2ohrfqWspztgCrQpAmeVFBWmnWYhPcZuwY52WWRe'
    and s.BLOCK_TIMESTAMP>='2023-05-05'
    and array_contains('Program log: Entering Short Position'::variant, log_messages)
    and SUCCEEDED='TRUE'
    and mint in ('DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263','EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v','So11111111111111111111111111111111111111112')
    group by 1

    union all

    select
    s.BLOCK_TIMESTAMP::date as date,
    'Long position' as position,
    count(distinct s.tx_id) as no_positions,
    sum(AMOUNT*price_token) as volume,
    avg(AMOUNT*price_token) as avg_volume
    from solana.core.fact_transactions s
    left join solana.core.fact_transfers a on s.tx_id=a.tx_id
    Run a query to Download Data