grahamNEAR CEX transactions
    Updated 2022-10-25
    with near_prices as (
    select
    TRUNC(TIMESTAMP,'hour') as timestamp_h,
    avg(price_usd) as price_usd
    from near.core.fact_prices
    where timestamp >= (current_date - {{metric_days}})
    AND symbol = 'wNEAR'
    group by 1
    ),
    deposits as (
    select
    a.tx_signer as user_address,
    b.project_name as exchange_name,
    count(distinct a.tx_hash) as n_deposits,
    sum(a.deposit / pow(10,24)) as near_tokens_deposited,
    sum(a.deposit / pow(10,24) * c.price_usd) as usd_deposited
    from near.core.fact_transfers a
    inner join (select distinct address, project_name
    from crosschain.core.address_labels
    where blockchain = 'near' and label_subtype = 'deposit_wallet') b
    on a.tx_receiver = b.address
    left join near_prices c
    on TRUNC(a.block_timestamp,'hour') = c.timestamp_h
    where
    block_timestamp >= (current_date - {{metric_days}})
    group by 1,2
    ),
    withdraws as (
    select tx_receiver as user_address,
    project_name as exchange_name,
    count(distinct tx_hash) as n_withdraws,
    sum(deposit / pow(10,24)) as near_tokens_withdrawn,
    sum(a.deposit / pow(10,24) * c.price_usd) as usd_withdrawn
    from near.core.fact_transfers a
    inner join (select distinct address, project_name
    from crosschain.core.address_labels
    Run a query to Download Data