Flipside Team2nd CEX Inflow/Outflow
    Updated 2024-09-25
    -- forked from 2nd CEX Inflow/Outflow @ https://flipsidecrypto.xyz/edit/queries/4f77a2c0-8a04-40b3-9192-1e8df8ff3e89

    -- fork from https://flipsidecrypto.xyz/adriaparcerisas/near-cex-flows-Dk5vaE
    WITH
    active_receivers as (
    select
    trunc(x.block_timestamp,'day') as date,
    project_name as cex,
    count(distinct tx_receiver) as n_users,
    count(distinct x.tx_hash) as txs,
    sum(deposit/pow(10,24)) as volume
    from near.core.fact_transfers x
    join near.core.dim_address_labels y
    on tx_signer=address and label_type='cex' and label_subtype='hot_wallet'
    where block_timestamp>='{{starting_date}}'
    group by 1,2),

    active_senders as (
    select
    trunc(x.block_timestamp,'day') as date,
    project_name as cex,
    count(distinct tx_signer) as n_users,
    count(distinct x.tx_hash) as txs,
    sum(deposit/pow(10,24))as volume
    from near.core.fact_transfers x
    join near.core.dim_address_labels y
    on tx_receiver=address and label_type='cex' and label_subtype='hot_wallet'
    where block_timestamp>='{{starting_date}}'
    group by 1,2),

    results as (
    select
    ifnull(x.date,y.date) as date,
    ifnull(x.cex,y.cex) as cex,
    ifnull(x.n_users,0) as senders_to_cex,
    ifnull(y.n_users,0) as receivers_from_cex,
    Auto-refreshes every 3 hours
    QueryRunArchived: QueryRun has been archived