SocioCryptosuccess rate and number of txn change
Updated 2022-12-19Copy Reference Fork
99
1
2
3
4
5
6
7
8
9
10
11
12
›
⌄
SELECT
sum(CASE WHEN block_timestamp::date BETWEEN '2021-11-01' AND '2021-11-30' AND succeeded = 'TRUE' THEN 1 ELSE 0 END) as "Nov. 21 successful txn",
sum(CASE WHEN block_timestamp::date BETWEEN '2021-11-01' AND '2021-11-30' AND succeeded != 'TRUE' THEN 1 ELSE 0 END) as "Nov. 21 unsuccessful txn",
sum(CASE WHEN block_timestamp::date BETWEEN '2022-11-01' AND '2022-11-30' AND succeeded = 'TRUE' THEN 1 ELSE 0 END) as "Nov. 22 successful txn",
sum(CASE WHEN block_timestamp::date BETWEEN '2022-11-01' AND '2022-11-30' AND succeeded != 'TRUE' THEN 1 ELSE 0 END) as "Nov. 22 unsuccessful txn",
100*"Nov. 21 successful txn"/("Nov. 21 successful txn"+"Nov. 21 unsuccessful txn") as success_rate_21,
100*"Nov. 22 successful txn"/("Nov. 22 successful txn"+"Nov. 22 unsuccessful txn") as success_rate_22,
100*(success_rate_22-success_rate_21)/success_rate_21 as one_y_change,
100*("Nov. 22 successful txn"-"Nov. 21 successful txn")/"Nov. 21 successful txn" as one_y_change_txn
FROM solana.core.fact_transactions
WHERE block_timestamp::date between '2021-11-01' and '2021-11-30' OR block_timestamp::date BETWEEN '2022-11-01' AND '2022-11-30'
Run a query to Download Data