WITH sol AS (

SELECT date(block_timestamp) as sol_date,

count(distinct signers[0]) as sol_address_count,

count(distinct tx_id) as sol_tx_count,

sum(fee) as lamport_fees,

lamport_fees/sol_tx_count as lamport_fees_per_tx,

count(case when succeeded='TRUE' then 1 else null end) as sol_successful_tx_count

FROM flipside_prod_db.solana.fact_transactions

WHERE block_timestamp >= CURRENT_DATE -90

GROUP BY 1

SELECT sol_date

, sol_address_count as "Unique Address"

, sol_tx_count as "Transactions"

, lamport_fees*pow(10,-9) as "Sum Fees (SOL)"

, lamport_fees*pow(10,-9)*1000 as "1000x Sum Fees (SOL)"

, sol_tx_count-sol_successful_tx_count as "Failed Transactions"

, sol_successful_tx_count as "Successful Transactions"

, sol_successful_tx_count/sol_tx_count as "TX Success Rate"

FROM sol