SELECT date(block_timestamp) as day,

count(CASE WHEN status = 'SUCCESS' THEN 1 end) as no_of_succ_txns,

count(CASE WHEN status = 'FAIL' THEN 1 end) as no_of_fail_txns,

round(100*(no_of_succ_txns/(no_of_fail_txns+no_of_succ_txns)),2) as success_rate,

round(100*(no_of_fail_txns/(no_of_fail_txns+no_of_succ_txns)),2) as failure_rate

FROM ethereum.core.fact_transactions

WHERE (from_Address in (

SELECT pool_address

FROM ethereum.core.dim_dex_liquidity_pools

WHERE platform ilike 'sushiswap') OR

to_address in (

SELECT pool_address

FROM ethereum.core.dim_dex_liquidity_pools

WHERE platform ilike 'sushiswap'))

AND tx_hash not IN (SELECT tx_hash

FROM ethereum.sushi.ez_lending)

AND tx_hash not IN (SELECT tx_hash

FROM ethereum.sushi.ez_borrowing)

AND tx_hash not IN (SELECT tx_hash

FROM ethereum.sushi.ez_swaps)

and block_timestamp >= dateadd(month, -12, getdate())

group by day

),

swap_dtls as (

SELECT date(block_timestamp) as day,

count(CASE WHEN status = 'SUCCESS' THEN 1 end) as no_of_succ_txns,

count(CASE WHEN status = 'FAIL' THEN 1 end) as no_of_fail_txns,

100*(no_of_succ_txns/(no_of_fail_txns+no_of_succ_txns)) as success_rate,

round(100*(no_of_fail_txns/(no_of_fail_txns+no_of_succ_txns)),2) as failure_rate

FROM ethereum.core.fact_transactions

WHERE origin_function_signature in (

SELECT DISTINCT origin_function_signature

FROM ethereum.sushi.ez_swaps)

and block_timestamp >= dateadd(month, -12, getdate())

group by day