SELECT
overall.dayz,
overall.tps AS overall_TPS,
success.tps AS success_TPS
FROM
(SELECT DATE(block_timestamp) as dayz, COUNT(tx_id)/86400 AS tps -- divide by 86400 as there is a total of 86400 seconds in a day (60secs * 60min *24hr)
FROM solana.core.fact_transactions
WHERE dayz >= CURRENT_DATE - 30 AND dayz < CURRENT_DATE
GROUP BY 1) overall
LEFT JOIN (SELECT DATE(block_timestamp) as dt, COUNT(tx_id)/86400 AS tps
FROM solana.core.fact_transactions
WHERE dt >= CURRENT_DATE - 30 AND dt < CURRENT_DATE AND succeeded = 'TRUE'
GROUP BY 1) success
ON dayz = dt ORDER BY overall.dayz