Sbhn_NPtransfer active
    Updated 2023-02-02
    with luna_price as (
    select
    date_trunc('{{Date}}',RECORDED_HOUR) as date,
    avg(CLOSE) as usdprice
    from crosschain.core.fact_hourly_prices
    where ID ilike 'terra-luna-2'
    group by 1
    ),
    active_wallets as ( select tx_sender,
    count(DISTINCT tx_id) as count_tx
    from terra.core.fact_transactions
    where tx_succeeded=TRUE
    group by 1
    having count_tx > {{TransactionCount}}),
    main as (select date_trunc('{{Date}}',block_timestamp) as date,
    transfer_type as type,
    count (distinct tx_id) as Transactions,
    count (distinct sender) as Senders,
    count (distinct receiver) as Receivers,
    sum(amount/pow(10,6)) as total_luna_volume
    from terra.core.ez_transfers
    join active_wallets on sender=tx_sender
    where tx_succeeded = TRUE
    and amount/pow(10,6) <1e9
    group by 1,2)

    select a.date,
    type,
    Transactions,
    Senders,
    Receivers,
    total_luna_volume,
    total_luna_volume*usdprice as total_usd_volume
    from main a
    Run a query to Download Data