nickpayiatis_l1 transactions hourly
    Updated 2022-04-05
    with successful_tx as(
    select date_trunc('hour',block_timestamp) as blockconfirm,count(tx_id) as successfultxs from solana.transactions where blockconfirm > DATEADD(Day ,-7, CURRENT_TIMESTAMP(3)) and blockconfirm < DATEADD(HOUR ,-8, CURRENT_TIMESTAMP(3)) and TRANSFER_TX_FLAG = 'true' group by blockconfirm),
    failed_tx as(
    select date_trunc('hour',block_timestamp) as blockconfirm,count(tx_id) as failedtxs from solana.transactions where blockconfirm > DATEADD(Day ,-7, CURRENT_TIMESTAMP(3)) and blockconfirm < DATEADD(HOUR ,-8, CURRENT_TIMESTAMP(3)) and TRANSFER_TX_FLAG = 'false' group by blockconfirm)
    ,
    terra_tx as (select date_trunc('hour',block_timestamp) as blockconfirm,count(tx_id) as successfultxs from terra.transactions where blockconfirm > DATEADD(Day ,-7, CURRENT_TIMESTAMP(3)) and blockconfirm < DATEADD(HOUR ,-8, CURRENT_TIMESTAMP(3)) group by blockconfirm),

    algorand_tx as (select date_trunc('hour',ab.block_timestamp) as blockconfirm,count(tx_id) as successfultxs from algorand.transactions att left join algorand.block ab on att.block_id = ab.block_id where blockconfirm > DATEADD(Day ,-7, CURRENT_TIMESTAMP(3)) and blockconfirm < DATEADD(HOUR ,-8, CURRENT_TIMESTAMP(3)) group by blockconfirm)
    ,
    eth_txs as (select date_trunc('hour',block_timestamp) as blockconfirm,count(tx_id) as successfultxs from ethereum.transactions where blockconfirm > DATEADD(Day ,-7, CURRENT_TIMESTAMP(3)) and blockconfirm < DATEADD(HOUR ,-8, CURRENT_TIMESTAMP(3)) group by blockconfirm)

    select sx.blockconfirm, sx.successfultxs as solana_successful_txs, ft.failedtxs as solana_failed_txs, tt.successfultxs as terra_txs, atx.successfultxs as algorand_txs, etx.successfultxs as Ethereum_txs from successful_tx sx left join failed_tx ft on sx.blockconfirm = ft.blockconfirm left join terra_tx tt on sx.blockconfirm = tt.blockconfirm left join algorand_tx atx on sx.blockconfirm = atx.blockconfirm left join eth_txs etx on sx.blockconfirm = etx.blockconfirm

    Run a query to Download Data