bachiwallet3
    Updated 2022-11-06
    with wallet_dtls as (
    select *
    from (values ('0x55ad22f01ef568a1','Blocto')
    , ('0x8234007b36f8113c','Monsoon')
    , ('0x18eb4ee6b3c026d2','Dapper')
    , ('0x39e42c67cc851cfb','Lilico')
    , ('0x94bb84386cfc3b5d','Everbloom')
    , ('0xecfad18ba9582d4f','Joyride')
    ) tab (wallet_provider_address,
    wallet_provider)
    )

    SELECT
    wallet_provider,
    count(DISTINCT(proposer)) AS no_of_users,
    count(DISTINCT(tx_id)) AS no_of_txns,
    round(SUM(asset_volume),2) AS total_volume,
    round(avg(asset_volume),2) AS avg_volume,
    (no_of_txns/no_of_users) AS avg_txns_per_wallet
    FROM (
    SELECT DATE(date_trunc('week', a.block_timestamp)) AS week,
    wallet_provider,
    proposer,
    a.tx_id,
    split(event_contract, '.')[2] AS asset_name,
    event_data:amount AS asset_volume
    FROM flow.core.fact_events a
    join flow.core.fact_transactions b
    ON a.tx_id = b.tx_id
    join wallet_dtls c
    ON payer = c.wallet_provider_address
    WHERE wallet_provider IS NOT NULL
    AND a.tx_succeeded = 'TRUE'
    AND payer IN
    (
    SELECT wallet_provider_address
    Run a query to Download Data