SELECT

dayname(BLOCK_TIMESTAMP::date) as day_of_week,

HOUR(block_timestamp) as hour_of_day,

count(*) as tx_cnt,

STATUS,

count(CASE WHEN STATUS = 'FAIL' THEN 1 END) as fail_tx_cnt

FROM ethereum.core.fact_transactions

WHERE ORIGIN_FUNCTION_SIGNATURE in (SELECT ORIGIN_FUNCTION_SIGNATURE FROM ethereum.core.ez_dex_swaps WHERE PLATFORM = 'sushiswap')

and BLOCK_TIMESTAMP::date >= CURRENT_DATE-60

and BLOCK_TIMESTAMP::date < CURRENT_DATE

GROUP BY 1, 2,4

day_of_week,

hour_of_day,

(tx_cnt-fail_tx_cnt)/60 as stpm,

fail_tx_cnt/tx_cnt as tfr

FROM swap_tx

ORDER by 1, 2