FlippppppaOTC KPI copy
    -- forked from h4wk / OTC KPI @ https://flipsidecrypto.xyz/h4wk/q/otc-daily-6-months-E_Otbx

    -- forked from OTC daily 6 months @ https://flipsidecrypto.xyz/edit/queries/a3b124cd-833c-41d4-af39-6155a61df25e

    -- forked from OTC Base @ https://flipsidecrypto.xyz/edit/queries/3a26b1ed-d6e9-4159-a277-ce6c5584d70e
    with daily_avg_price as (
    select block_timestamp::date as price_date,
    label as collection,
    avg(sales_amount) as price
    from solana.core.fact_nft_sales
    join solana.core.dim_labels on mint = address
    where price_date >= CURRENT_DATE - 180
    and succeeded = True
    group by 1,2
    )

    , base as (
    select tx.block_timestamp,
    tx_id,
    signers[0] as signer,
    case when signer = tx_to then 'receiver'
    when tx_to = '98Ni7vVRR1tggtWWruPVcfFXHTH11bPbNryJZGkCGvaD' then 'fees_receiver'
    else 'offeror' end as user_type,
    tx_to as trader,
    amount,
    case when label = 'wrapped sol' then amount
    else amount*price end as volume_sol,
    mint,
    label as collection,
    case when label = 'wrapped sol' then 'SOL'
    else 'NFT' end as trade_type
    from solana.core.fact_transactions tx
    join lateral flatten (input => log_messages) logs
    join (select distinct block_timestamp, tx_id
    Run a query to Download Data