WITH transitions AS (

SELECT

BLOCK_TIMESTAMP::DATE AS date,

CASE

WHEN INPUTS[0]['type'] = 'private' OR INPUTS[1]['type'] = 'private' OR INPUTS[2]['type'] = 'private' OR INPUTS[3]['type'] = 'private' THEN 'private'

ELSE 'public'

END AS type,

tx_id

FROM aleo.core.fact_transitions

WHERE SUCCEEDED = 'TRUE'

SELECT

date,

COUNT(tx_id) AS total_txn_count,

COUNT(DISTINCT (CASE WHEN Type = 'private' THEN TX_ID ELSE NULL END)) AS private_txn_count,

total_txn_count - private_txn_count AS public_txn_count

FROM transitions

GROUP BY date

ORDER BY date