annie__Daily Bitcoin Transaction Volumes & Average
Updated 2025-01-13
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
›
⌄
-- CTE to calculate daily transaction volumes and their average values
WITH DailyTransactionData AS (
SELECT
DATE(BLOCK_TIMESTAMP) AS Transaction_Date,
COUNT(*) AS Total_Transactions,
SUM(OUTPUT_VALUE) AS Total_Output_Value, -- Sum of all output values for the day
AVG(OUTPUT_VALUE / OUTPUT_COUNT) AS Avg_Transaction_Value -- Average output value per transaction
FROM
bitcoin.core.fact_transactions
WHERE
IS_COINBASE = FALSE -- Excluding coinbase transactions as they are not market transactions
GROUP BY
DATE(BLOCK_TIMESTAMP)
)
-- Selecting formatted daily transaction data
SELECT
Transaction_Date,
Total_Transactions,
ROUND(Total_Output_Value, 2) AS Total_Output_Value_BTC,
ROUND(Avg_Transaction_Value, 2) AS Avg_Transaction_Value_BTC
FROM
DailyTransactionData
ORDER BY
Transaction_Date DESC;
QueryRunArchived: QueryRun has been archived