nitsChains
    Updated 2022-07-17
    -- -Number of transactions -Transaction success rates -Number of unique wallets to make a transaction -Number of wallets that used the chain everyday since June 15th -Transaction fees
    with flow_data as
    (SELECT date(block_timestamp) as day, count(DISTINCT tx_id) as total_txs, sum(success)/(sum(success)+sum(failed))*100 as percent_sucess,
    avg(percent_sucess) over (order by day) as avg_sucess, sum(total_txs) over (order by day) as cumulative_txs, count(DISTINCT payer) as daily_users,
    sum(daily_users) over (order by day) as cumulative_wallets, 'flow' as type, '0' AS FEES, sum(FEES) over (order by day) as cumulative_fees
    from (SELECT *, iff(tx_succeeded, '1', '0') as success,iff(tx_succeeded, '0', '1') as failed
    from flow.core.fact_transactions)
    where day >= '2022-06-15'
    GROUP by 1 ),
    osmosis_data as
    (SELECT date(block_timestamp) as day, count(DISTINCT tx_id) as total_txs, sum(success)/(sum(success)+sum(failed))*100 as percent_sucess,
    avg(percent_sucess) over (order by day) as avg_sucess, sum(total_txs) over (order by day) as cumulative_txs, count(DISTINCT tx_from) as daily_users,
    sum(daily_users) over (order by day) as cumulative_wallets , 'osmosis' as type, sum(fee_)/pow(10,6) as fees, sum(FEES) over (order by day) as cumulative_fees
    from (SELECT *, iff(tx_status ='SUCCEEDED', '1', '0') as success,iff(tx_status='FAILED', '1', '0') as failed, replace(fee,'uosmo','') as fee_
    from osmosis.core.fact_transactions)
    where day >= '2022-06-15' and contains(fee, 'uosmo')
    GROUP by 1 ),

    eth_data as
    (SELECT date(block_timestamp) as day, count(DISTINCT tx_hash) as total_txs, sum(success)/(sum(success)+sum(failed))*100 as percent_sucess,
    avg(percent_sucess) over (order by day) as avg_sucess, sum(total_txs) over (order by day) as cumulative_txs, count(DISTINCT from_address) as daily_users,
    sum(daily_users) over (order by day) as cumulative_wallets , 'ethereum' as type, sum(tx_fee) as fees, sum(FEES) over (order by day) as cumulative_fees
    from (SELECT *, iff(status ='SUCCESS', '1', '0') as success,iff(status='FAIL', '1', '0') as failed
    from ethereum_core.fact_transactions)
    where day >= '2022-06-15'
    GROUP by 1 ),
    sol_data as
    (SELECT date(block_timestamp) as day, count(DISTINCT tx_id) as total_txs, sum(success)/(sum(success)+sum(failed))*100 as percent_sucess,
    avg(percent_sucess) over (order by day) as avg_sucess, sum(total_txs) over (order by day) as cumulative_txs, count(DISTINCT signers[0]) as daily_users,
    sum(daily_users) over (order by day) as cumulative_wallets , 'solana' as type, sum(fee)/pow(10,6) as fees, sum(FEES) over (order by day) as cumulative_fees
    from (SELECT *, iff(succeeded, '1', '0') as success,iff(succeeded, '0', '1') as failed
    from solana.fact_transactions)
    where day >= '2022-06-15'
    GROUP by 1 ),
    algo_data as
    (SELECT date(block_timestamp) as day, count(DISTINCT tx_id) as total_txs, '95' as percent_sucess,
    Run a query to Download Data