Querying dcrdata
1. Introduction
dcrdata provides us a web interface to analyse the content of blocks, transations, wallet addresses, tickets and votes. To view dcrdata working live go to https://dcrdata.decred.org/.
The inner workings of this infrastructure include a PostgreSQL database where the information contained in Decred blockchain is converted and stored. This information can also be queried using SQL language.
To learn more about dcrdata installation, read article dcrdata: running your own block explorer.
This article assumes the reader has a basic working knowledge of SQL statements. To brush up your skills, please check these PostgreSQL tutorials.
2. Performing Queries
Note 1: The numbers shown here will differ from yours because new blocks will have been mined by the time you are reading this.
Note 2: Some functions here should not be used on a web app database or on a database with high average load. But because I’m running these queries only once, I’m not concerned with performance (I am, but I know that a mistake won’t have long-term impact).
In this section I’m going to show several easy queries to get you familiar with querying dcrdata. If it gets too boring, skip to section 3.
After logging in to the terminal, access psql console to start performing queries on the database.
$ sudo su - postgres
postgres@ip-172-31:~$ psql
psql (12.2 (Ubuntu 12.2-2.pgdg18.04+1))
Type "help" for help.
postgres=#
Command \l shows the list of databases:
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+---------+-----------------------
dcrdata | dcrdata | UTF8 | C.UTF-8 | C.UTF-8 |
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
postgres=#
Check if the settings were configured as they should have been (check dcrdata article):
SELECT version();
SELECT name, current_setting(name), source
FROM pg_settings
You can also check the roles with command \du:
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
dcrdata | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=#
Let’s try a few queries. First, we connect to dcrdata database:
postgres=# \c dcrdata
You are now connected to database "dcrdata" as user "postgres".
Then, we take a look at the tables:
dcrdata=# \dt
List of relations
Schema | Name | Type | Owner
--------+----------------+-------+---------
public | addresses | table | dcrdata
public | agenda_votes | table | dcrdata
public | agendas | table | dcrdata
public | block_chain | table | dcrdata
public | blocks | table | dcrdata
public | meta | table | dcrdata
public | misses | table | dcrdata
public | proposal_votes | table | dcrdata
public | proposals | table | dcrdata
public | stats | table | dcrdata
public | testing | table | dcrdata
public | tickets | table | dcrdata
public | transactions | table | dcrdata
public | vins | table | dcrdata
public | votes | table | dcrdata
public | vouts | table | dcrdata
(16 rows)
dcrdata=#
Let’s take a closer look at the table blocks and list its columns:
dcrdata=# \d blocks
Table "public.blocks"
Column | Type | Collation | Nullable | Default
---------------+--------------------------+-----------+----------+------------------------------------
id | integer | | not null | nextval('blocks_id_seq'::regclass)
hash | text | | not null |
height | integer | | |
size | integer | | |
is_valid | boolean | | |
is_mainchain | boolean | | |
version | integer | | |
numtx | integer | | |
num_rtx | integer | | |
tx | text[] | | |
txdbids | bigint[] | | |
num_stx | integer | | |
stx | text[] | | |
stxdbids | bigint[] | | |
time | timestamp with time zone | | |
nonce | bigint | | |
vote_bits | smallint | | |
voters | smallint | | |
fresh_stake | smallint | | |
revocations | smallint | | |
pool_size | integer | | |
bits | integer | | |
sbits | bigint | | |
difficulty | double precision | | |
stake_version | integer | | |
previous_hash | text | | |
chainwork | text | | |
winners | text[] | | |
Indexes:
"blocks_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "stats" CONSTRAINT "stats_blocks_id_fkey" FOREIGN KEY (blocks_id) REFERENCES blocks(id) ON DELETE CASCADE
dcrdata=#
Now that we know the column names, let’s perform a very simple query, showing only block ID, hash and the time when this block was mined, querying only the last available block on this table:
dcrdata=# select id, hash, height, time from blocks where is_mainchain order by height desc limit 1;
id | hash | height | time
--------+------------------------------------------------------------------+--------+------------------------
428923 | 00000000000000001407d9857e98a398bcbdcfcc68f3b790b56eff8b7a265d6d | 428922 | 2020-03-04 08:03:09+00
(1 row)
dcrdata=#
You can write SQL commands in lowercase (select) or uppercase (SELECT). You can also write your query on a text editor and paste a multiline query directly on psql:
dcrdata=# SELECT id, hash, height, time
dcrdata-# FROM blocks
dcrdata-# WHERE is_mainchain
dcrdata-# ORDER BY height DESC
dcrdata-# LIMIT 1;
id | hash | height | time
--------+------------------------------------------------------------------+--------+------------------------
428923 | 00000000000000001407d9857e98a398bcbdcfcc68f3b790b56eff8b7a265d6d | 428922 | 2020-03-04 08:03:09+00
(1 row)
To find the time of the Genesis block:
dcrdata=# SELECT time FROM blocks WHERE height = 0 AND is_mainchain;
time
------------------------
2016-02-08 18:00:00+00
(1 row)
To count how many votes were missed on each block, we join tables blocks and misses where the block hash is the same:
dcrdata=# SELECT blocks.height, count(*)
FROM blocks
LEFT JOIN misses ON misses.block_hash = blocks.hash
WHERE blocks.is_mainchain
AND misses.block_hash IS NOT NULL
GROUP BY blocks.hash, blocks.height, misses.block_hash
ORDER BY blocks.height DESC
LIMIT 10;
height | count
--------+-------
430235 | 1
430213 | 1
430211 | 1
430192 | 1
430179 | 1
430143 | 1
430046 | 1
430034 | 1
430025 | 1
430024 | 1
(10 rows)
To export the information just queried, we can insert the query into a COPY statement and have the results exported to a local CSV file on the PostgreSQL server:
dcrdata=# COPY (SELECT blocks.height, count(*)
FROM blocks
LEFT JOIN misses ON misses.block_hash = blocks.hash
WHERE blocks.is_mainchain
AND misses.block_hash IS NOT NULL
GROUP BY blocks.hash, blocks.height, misses.block_hash
ORDER BY blocks.height)
TO '/opt/decred/query_blocks_number_missing_votes.copy' (DELIMITER ',');
COPY 27786
If the user has write privileges and the query statement is valid, COPY statement will return the number of blocks affected by the query as shown above. But if the user running psql (in this example, postgres) doesn’t have write privileges on the specified folder, psql will return an error:
ERROR: could not open file "/opt/decred/query_blocks_number_missing_votes.copy" for writing: Permission denied
You can also export a single table to the screen or to CSV format running the following statements:
dcrdata=# COPY agendas TO STDOUT (DELIMITER ',');
1,fixlnseqlocks,4,334720,342784,0
2,headercommitments,3,423424,431488,0
3,lnfeatures,4,181504,189568,0
4,lnsupport,4,141184,149248,0
5,sdiffalgorithm,4,141184,149248,0
dcrdata=# COPY agendas TO '/tmp/table_agendas.copy' (DELIMITER ',');
COPY 5
The resulting data can later be imported into a CSV spreadsheet:
$ cat /tmp/table_agendas.copy
1,fixlnseqlocks,4,334720,342784,0
2,headercommitments,3,423424,431488,0
3,lnfeatures,4,181504,189568,0
4,lnsupport,4,141184,149248,0
5,sdiffalgorithm,4,141184,149248,0
The data can also be directly exported to a gzip compressed file:
dcrdata=# COPY agendas TO PROGRAM 'gzip > /tmp/table_agendas.gz' (DELIMITER ',');
COPY 5
$ cd /tmp
$ gunzip table_agendas.gz
$ cat table_agendas
1,fixlnseqlocks,4,334720,342784,0
2,headercommitments,3,423424,431488,0
3,lnfeatures,4,181504,189568,0
4,lnsupport,4,141184,149248,0
5,sdiffalgorithm,4,141184,149248,0
Queries can be saved in a file and executed from psql interface:
$ cat saved_query.sql
select id, hash, time from blocks order by id desc limit 1;
On psql console:
dcrdata=# \i /tmp/saved_query.sql
id | hash | time
--------+------------------------------------------------------------------+------------------------
429359 | 0000000000000000071a908d391de522359772584cfe01cdfed7679526c26e23 | 2020-03-05 20:23:58+00
(1 row)
The same query can also be executed from terminal calling psql with flag ‘-f’, the query file and the name of the database:
$ sudo su - postgres
$ psql -f /tmp/saved_query.sql dcrdata
id | hash | time
--------+------------------------------------------------------------------+------------------------
429360 | 000000000000000017abdd65034289701cb5459999432c67a974db7b03ab6952 | 2020-03-05 20:39:14+00
(1 row)
To export the same information to a CSV file available to the psql client we can use psql \copy. In this architecture you could run psql client on a workstation:
dcrdata=# \copy (SELECT blocks.height, count(*)
FROM blocks
LEFT JOIN misses ON misses.block_hash = blocks.hash
WHERE blocks.is_mainchain
AND misses.block_hash IS NOT NULL
GROUP BY blocks.hash, blocks.height, misses.block_hash
ORDER BY blocks.height)
TO '/tmp/psql_copy_query_blocks_number_missing_votes.copy' (DELIMITER ',');
COPY 27795
To count the number of transactions seen on a single day:
dcrdata=#
SELECT date_trunc('day', transactions.time) "day", count(*) count
FROM transactions
GROUP BY 1
ORDER BY 1 DESC
LIMIT 5;
day | count
------------------------+-------
2020-03-05 00:00:00+00 | 3485
2020-03-04 00:00:00+00 | 4320
2020-03-03 00:00:00+00 | 3776
2020-03-02 00:00:00+00 | 4518
2020-03-01 00:00:00+00 | 4249
(5 rows)
If we would like to measure the number of transaction by day of the month, we would notice that there isn’t a considerable difference between days, except for the 31st, which occurs only in 5 months of the year.
dcrdata=#
SELECT date_part('day', date_trunc('day', transactions.time)) "day", count(*) count
FROM transactions
WHERE transactions.is_mainchain
GROUP BY 1
ORDER BY 1 ASC;
day | count
-----+--------
1 | 246694
2 | 233318
3 | 254283
4 | 233808
5 | 240129
6 | 233029
7 | 252154
8 | 243241
9 | 254109
10 | 249953
11 | 243353
12 | 238609
13 | 248959
14 | 234290
15 | 246270
16 | 241284
17 | 256803
18 | 240874
19 | 249321
20 | 243396
21 | 253549
22 | 228318
23 | 254519
24 | 247674
25 | 242932
26 | 248428
27 | 249318
28 | 249812
29 | 227964
30 | 223903
31 | 139490
(31 rows)
The beginning of the week has been a little bit busier then its end, from Monday (1) to Sunday (7).
dcrdata=#
SELECT date_part('isodow', date_trunc('day', transactions.time)) "day", count(*) count
FROM transactions
GROUP BY 1
ORDER BY 1 asc;
day | count
-----+---------
1 | 1085754
2 | 1079497
3 | 1067169
4 | 1061087
5 | 1052175
6 | 1053308
7 | 1037039
(7 rows)
3. Analysing the blockchain
Now that we analysed how information can be queried and extracted from the database, let’s try more useful queries:
Number of voters per block:
dcrdata=#
SELECT voters, count(*) count
FROM blocks
WHERE blocks.is_mainchain
GROUP BY 1
ORDER BY 1 ASC;
voters | count
--------+--------
0 | 4096
3 | 5594
4 | 22219
5 | 398405
(4 rows)
Hard-fork vote counts per proposal, per vote type:
dcrdata=#
SELECT agendas.id, agendas.name, agendas.locked_in, count(agenda_votes.agenda_vote_choice), agenda_votes.agenda_vote_choice
FROM agenda_votes
LEFT JOIN agendas ON agendas.id = agenda_votes.agendas_row_id
GROUP BY 1, 2, 5
ORDER BY 1;
id | name | locked_in | count | agenda_vote_choice
----+-------------------+-----------+--------+--------------------
1 | fixlnseqlocks | 334720 | 206426 | 0
1 | fixlnseqlocks | 334720 | 206679 | 1
1 | fixlnseqlocks | 334720 | 383 | 2
2 | headercommitments | 423424 | 60315 | 0
2 | headercommitments | 423424 | 52830 | 1
2 | headercommitments | 423424 | 31 | 2
3 | lnfeatures | 181504 | 336637 | 0
3 | lnfeatures | 181504 | 398081 | 1
3 | lnfeatures | 181504 | 3933 | 2
4 | lnsupport | 141184 | 130743 | 0
4 | lnsupport | 141184 | 82563 | 1
4 | lnsupport | 141184 | 2318 | 2
5 | sdiffalgorithm | 141184 | 135855 | 0
5 | sdiffalgorithm | 141184 | 77558 | 1
5 | sdiffalgorithm | 141184 | 2211 | 2
(15 rows)
Ticket price per day, ordered by when ticket prices were higher:
dcrdata=#
SELECT tickets.price, date_trunc('day', blocks.time) "day"
FROM tickets
LEFT JOIN blocks ON tickets.block_hash = blocks.hash
GROUP BY 1, 2
ORDER BY 1 DESC
LIMIT 10;
price | day
--------------+------------------------
238.87091996 | 2017-05-17 00:00:00+00
229.42820188 | 2017-07-02 00:00:00+00
210.51896924 | 2017-05-04 00:00:00+00
192.19848074 | 2017-06-10 00:00:00+00
182.41479251 | 2017-05-08 00:00:00+00
180.42908734 | 2017-06-08 00:00:00+00
177.77634486 | 2017-06-04 00:00:00+00
175.588235 | 2017-04-23 00:00:00+00
168.90177663 | 2017-05-16 00:00:00+00
167.67392382 | 2017-04-06 00:00:00+00
(10 rows)
Ticket prices ordered by date:
dcrdata=#
SELECT round(avg(tickets.price)::numeric, 8) AS price, date_trunc('day', blocks.time) "day"
FROM tickets
LEFT JOIN blocks ON tickets.block_hash = blocks.hash
GROUP BY 2
ORDER BY 2 DESC
LIMIT 10;
price | day
--------------+------------------------
159.87989362 | 2020-03-06 00:00:00+00
163.78460567 | 2020-03-05 00:00:00+00
166.42117114 | 2020-03-04 00:00:00+00
165.62853168 | 2020-03-03 00:00:00+00
161.34051020 | 2020-03-02 00:00:00+00
159.19549174 | 2020-03-01 00:00:00+00
157.08347541 | 2020-02-29 00:00:00+00
152.40068677 | 2020-02-28 00:00:00+00
147.12867181 | 2020-02-27 00:00:00+00
139.57991829 | 2020-02-26 00:00:00+00
(10 rows)
Ticket price, ticket pool size and ticket pool size in DCRs for a limited time/block range:
dcrdata=#
SELECT split_part(CAST (date_trunc('day', blocks.time) AS text), ' ', 1) "day", round(avg(tickets.price)::numeric, 8) AS price_avg, round(avg(stats.pool_size)::numeric,0) AS pool_avg, to_char(round(avg(pool_val / 100000000)::numeric,8), '999G999G999D99999999') AS pool_dcr_avg
FROM tickets
LEFT JOIN blocks ON tickets.block_hash = blocks.hash
LEFT JOIN stats ON tickets.block_height = stats.height
GROUP BY 1
ORDER BY 1 DESC
LIMIT 10;
day | price_avg | pool_avg | pool_dcr_avg
------------+--------------+----------+-----------------------
2020-03-07 | 155.11263808 | 40493 | 5,589,930.60869565
2020-03-06 | 159.55606587 | 40655 | 5,597,250.48536355
2020-03-05 | 163.78460567 | 40975 | 5,610,394.03621908
2020-03-04 | 166.42117114 | 41329 | 5,625,127.40570934
2020-03-03 | 165.62853168 | 41502 | 5,612,721.80111008
2020-03-02 | 161.34051020 | 41256 | 5,536,460.62266501
2020-03-01 | 159.19549174 | 41523 | 5,548,136.23614275
2020-02-29 | 157.08347541 | 41723 | 5,545,257.63585434
2020-02-28 | 152.40068677 | 41857 | 5,541,337.43271375
2020-02-27 | 147.12867181 | 42131 | 5,562,362.08414726
(10 rows)
PoS rewards for a limited time/block range:
dcrdata=#
SELECT vins.block_time, to_char(round(sum(vins.value_in)/100000000, 8), '999G999G999D99999999') as total_reward
FROM vins
JOIN transactions ON vins.tx_hash = transactions.tx_hash
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND transactions.block_height > 429700
AND NOT (vins.is_valid = false AND vins.tx_tree = 0)
AND vins.is_mainchain
GROUP BY vins.block_time, transactions.block_height
ORDER BY transactions.block_height DESC
LIMIT 10;
block_time | total_reward
------------------------+-----------------------
2020-03-07 03:15:19+00 | 15.70079703
2020-03-07 03:02:02+00 | 15.70079703
2020-03-07 03:00:21+00 | 15.70079703
2020-03-07 02:49:05+00 | 15.70079703
2020-03-07 02:43:05+00 | 15.70079703
2020-03-07 02:38:51+00 | 15.70079703
2020-03-07 02:38:17+00 | 15.70079703
2020-03-07 02:37:34+00 | 15.70079703
2020-03-07 02:37:08+00 | 15.70079703
2020-03-07 02:36:28+00 | 15.70079703
(10 rows)
Network fund rewards for a limited time/block range:
dcrdata=#
SELECT blocks.time, transactions.block_height, transactions.tx_hash, vins.value_in, vouts.value AS value_out, vouts.script_addresses
FROM transactions
JOIN vins ON transactions.tx_hash = vins.tx_hash
JOIN vouts ON transactions.tx_hash = vouts.tx_hash
JOIN blocks ON blocks.height = transactions.block_height
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND vouts.script_addresses = '{Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}'
AND transactions.block_height > 430220
ORDER BY transactions.block_height DESC;
time | block_height | tx_hash | value_in | value_out | script_addresses
------------------------+--------------+------------------------------------------------------------------+------------+-----------+---------------------------------------
2020-03-08 18:23:41+00 | 430236 | 21e6946dc6ff2db0026d45f4d6d8eb1dd313d39fbb829812850e327c9a76d212 | 1088174052 | 155453436 | {Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}
2020-03-08 18:21:05+00 | 430235 | 0fd423064536a758d0979cc7cfe3e3ccf5d0ba11ea61acc6ee58a679e1a81dd7 | 870539240 | 124362748 | {Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}
2020-03-08 18:19:16+00 | 430234 | 72b10362b2f6ef8c1748ce404579eca6a67abccd718f63446f3a5fe18fba3fbb | 1088174052 | 155453436 | {Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}
2020-03-08 17:47:45+00 | 430233 | 1069b2b00523146224a25d2a1b53533e7005bc34aa3e3ee989fb058e73d626bf | 1088174052 | 155453436 | {Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}
2020-03-08 17:36:06+00 | 430232 | 6c98cb143474302b2212adcaf0a603e5b834f82c2e8dcf90a4f0c1741ca86d6c | 1088174052 | 155453436 | {Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}
2020-03-08 17:32:00+00 | 430231 | cd6c8597e4e8fd90c21dc7e2d99ad09708add3bbde860053bcf3aad9b9bba50c | 1088174052 | 155453436 | {Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}
2020-03-08 17:07:53+00 | 430230 | 61689f6a2c2371d6822bb3dd690ae4ef6a0cd2f8335756398c71431845306ee2 | 1088174052 | 155453436 | {Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}
2020-03-08 17:05:52+00 | 430229 | 6521268dd452e1ca27d060948120318a032ee20abe1f48a7ea20e5f17e010d78 | 1088174052 | 155453436 | {Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}
2020-03-08 17:03:35+00 | 430228 | af4a02431fd28dc1d389c5095ffd2a207deb04279f963a7ed3c916de0f156c72 | 1088174052 | 155453436 | {Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}
2020-03-08 17:00:14+00 | 430227 | 54f1a566f3f034e4abc92c8c7c2f75f728d7d7293ecea60fc5bbe1481dd404ee | 1088174052 | 155453436 | {Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}
2020-03-08 16:53:34+00 | 430226 | 29f2aae9f77f3bec1453e1b155b346cbe57b79d0e321e6d160564b91c28402be | 1088174052 | 155453436 | {Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}
2020-03-08 16:39:13+00 | 430225 | 6cc3ef2f6dff3d4d2ecbcb0257fef8eedf9742d67249872b66e9fc05a3b3978c | 1088174052 | 155453436 | {Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}
2020-03-08 16:31:46+00 | 430224 | f20090e4093d96936180e1415df39db2db386af6b45c1c52e97902357a399d88 | 1088174052 | 155453436 | {Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}
2020-03-08 16:19:20+00 | 430223 | 05e6c5c614d8c60530473a9c218e2fb9b7f8d1af591bcea9b5fcf39e8eeb71bf | 1088174052 | 155453436 | {Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}
2020-03-08 16:07:27+00 | 430222 | cbc400c2e345481d266211b8f33624a0f72f70c0c5724a6060d510b293192c66 | 1088174052 | 155453436 | {Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}
2020-03-08 15:56:40+00 | 430221 | e677fcc23c17b9ba0bf9ba872bfa42876badaa37afe30e4c6400dab5ad34f76a | 1088174052 | 155453436 | {Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}
(16 rows)
dcrdata=#
PoW miner reward + PoS miners reward, only 4 tickets:
dcrdata=#
SELECT blocks.height, transactions.tx_type, transactions.tx_hash, vins.value_in
FROM transactions
JOIN vins ON transactions.tx_hash = vins.tx_hash
JOIN blocks ON blocks.height = transactions.block_height
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND transactions.block_height = 430235;
height | tx_type | tx_hash | value_in
--------+---------+------------------------------------------------------------------+-----------
430235 | 0 | 0fd423064536a758d0979cc7cfe3e3ccf5d0ba11ea61acc6ee58a679e1a81dd7 | 870539240
430235 | 2 | 3ba558b0d30d6b05ebc9ad410f6fd4f31b2cf727cdf585006579d2b4d40bd201 | 93272061
430235 | 2 | 7eab6f02288e3b81132d80a7a9ac4e7249e2568a3930a885b4e286432a0e28d8 | 93272061
430235 | 2 | 956effcd7de8baba1fcd457ebb9b460dcb0424d0a53239328759bafe38d19b19 | 93272061
430235 | 2 | 98231fbb4a1d5221043f8f7ea65ec48bd8c76dc9a7b24e4f6616dd690ac72a35 | 93272061
(5 rows)
Query showing PoW miner reward + PoS miners reward, in a block with 5 mined tickets:
dcrdata=#
SELECT blocks.height, transactions.tx_type, transactions.tx_hash, vins.value_in
FROM transactions
JOIN vins ON transactions.tx_hash = vins.tx_hash
JOIN blocks ON blocks.height = transactions.block_height
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND transactions.block_height = 430236;
height | tx_type | tx_hash | value_in
--------+---------+------------------------------------------------------------------+------------
430236 | 0 | 21e6946dc6ff2db0026d45f4d6d8eb1dd313d39fbb829812850e327c9a76d212 | 1088174052
430236 | 2 | cbc7e8016459d074339e8100305d4116edea76273227af6428bb6d53a10bb4f1 | 93272061
430236 | 2 | c3586bed4d251913375f0bd785b667b25c75cfed6643f20d8d0cd201047c3f37 | 93272061
430236 | 2 | 90d2a41a776f29c434b00887cc9419a6ee80ed3d1bdc54b4be74802ac1750554 | 93272061
430236 | 2 | d973db0f97a4f02fe54dba6e0fc3f031e7ad2572d359b6f9a48eee1ff4120e43 | 93272061
430236 | 2 | 4654fee23f11e273546f78691112ed828623b03be779be474ec0d3f5ca8a7fa8 | 93272061
PoS miner votes transactions showing votes on block validity and their rewards.
dcrdata=#
SELECT transactions.block_height, transactions.tx_type, transactions.tx_hash, vins.value_in, votes.block_valid
FROM transactions
JOIN vins ON transactions.tx_hash = vins.tx_hash
JOIN votes ON transactions.tx_hash = votes.tx_hash
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND transactions.block_height = 430211;
height | tx_type | tx_hash | value_in | block_valid
--------+---------+------------------------------------------------------------------+----------+-------------
430211 | 2 | 2c9c7fd6be58e30e51a0615b4959d70d4aa07e0f02770072c689a687205468c9 | 93272061 | t
430211 | 2 | a4bac8236396cb697ca7ed80fb747ddb19b08696b3e2ea99e4d358c00d9936cf | 93272061 | t
430211 | 2 | e8cea42de220f2437a4735fb74f0ae458ceecaae9480b18424398cf2e3b88653 | 93272061 | t
430211 | 2 | 7be75a5c122d274c34414175cf7e6bdd9621c43687bce1131fcb92b6d36815dd | 93272061 | t
(4 rows)
Proof-of-Stake (PoS) number of tickets mined in the block and total PoS reward per block:
dcrdata=#
WITH pre_select AS (
SELECT DISTINCT transactions.block_height, transactions.tx_hash, vins.value_in, transactions.is_mainchain
FROM transactions
JOIN vins ON transactions.tx_hash = vins.tx_hash
JOIN votes ON transactions.tx_hash = votes.tx_hash
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND transactions.is_mainchain
AND transactions.block_height > 430210
AND transactions.block_height < 430215
)
SELECT pre_select.block_height, count(*) AS tickets, SUM(pre_select.value_in) AS total_pos_reward
FROM pre_select
GROUP BY 1
ORDER BY pre_select.block_height DESC;
block_height | tickets | total_pos_reward
--------------+---------+------------------
430214 | 5 | 466360305
430213 | 4 | 373088244
430212 | 5 | 466360305
430211 | 4 | 373088244
(4 rows)
Decred Treasury uses the hardcoded address Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx to fund the development of the project (network fund):
dcrdata=#
SELECT transactions.block_height, transactions.tx_type, transactions.tx_hash, vouts.value
FROM transactions
JOIN vins ON transactions.tx_hash = vins.tx_hash
JOIN vouts ON transactions.tx_hash = vouts.tx_hash
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND vouts.script_addresses = '{Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}'
AND transactions.is_mainchain
AND transactions.block_height = 430211;
block_height | tx_type | tx_hash | value
--------------+---------+------------------------------------------------------------------+-----------
430211 | 0 | 16ba1d52c2142866be6f9cab0b3a2ab909ccb7409325ae975e97ddcb7eeeb0e2 | 124362748
(1 row)
Proof-of-Work miner total reward (new coins + fees) can be obtained with the following query:
dcrdata=#
SELECT transactions.block_height, transactions.tx_hash, vouts.value, vouts.script_addresses
FROM transactions
JOIN vins ON transactions.tx_hash = vins.tx_hash
JOIN vouts ON transactions.tx_hash = vouts.tx_hash
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND NOT (vouts.script_addresses = '{Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}' OR vouts.script_addresses = '{}')
AND transactions.is_mainchain
AND transactions.tx_type = 0
AND transactions.block_height = 430211;
block_height | tx_hash | value | script_addresses
--------------+------------------------------------------------------------------+-----------+---------------------------------------
430211 | 16ba1d52c2142866be6f9cab0b3a2ab909ccb7409325ae975e97ddcb7eeeb0e2 | 747152367 | {DsnxqhJX2tjyjbfb9y4yPdpJ744G9fLhbbF}
(1 row)
Rewards and fees per block:
WITH pre_select AS (
SELECT transactions.block_time as block_time, transactions.block_height as block_height, transactions.tx_hash as tx_hash, transactions.tx_type as tx_type, vins.prev_tx_hash, vins.value_in as value_in, vouts.value as vouts_value, vouts.script_addresses as addresses
FROM transactions
JOIN vins ON transactions.tx_hash = vins.tx_hash
JOIN vouts ON transactions.tx_hash = vouts.tx_hash
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND transactions.is_mainchain
AND transactions.block_height > 430350
AND transactions.block_height < 430355
),
table_pos_reward AS (
SELECT pre_select.block_height, pre_select.value_in as value_in, pre_select.tx_hash
FROM pre_select
JOIN votes ON pre_select.tx_hash = votes.tx_hash
WHERE pre_select.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND pre_select.tx_type = 2
AND NOT pre_select.vouts_value = 0
GROUP BY 1,2,3
),
table_pow_reward AS (
SELECT pre_select.block_height, pre_select.vouts_value as value_out, pre_select.addresses
FROM pre_select
WHERE NOT (pre_select.addresses = '{Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}' OR pre_select.addresses = '{}')
AND pre_select.tx_type = 0
),
table_net_fund AS (
SELECT pre_select.block_height, pre_select.vouts_value as value_out
FROM pre_select
WHERE pre_select.addresses = '{Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}'
)
SELECT pre_select.block_time, pre_select.block_height, SUM(table_pos_reward.value_in) AS pos_reward,
pre_select.value_in AS pow_and_netfund, table_pow_reward.value_out AS fees_pow_reward,
table_net_fund.value_out AS net_fund, (pre_select.value_in - table_net_fund.value_out) AS pow_reward,
(table_pow_reward.value_out - (pre_select.value_in - table_net_fund.value_out)) AS fees
FROM pre_select
JOIN table_pos_reward ON table_pos_reward.block_height = pre_select.block_height
JOIN table_pow_reward ON table_pow_reward.block_height = pre_select.block_height
JOIN table_net_fund ON table_net_fund.block_height = pre_select.block_height
WHERE pre_select.addresses = '{Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}'
GROUP BY 1,2,4,5,6
ORDER BY pre_select.block_height DESC;
block_time | block_height | pos_reward | pow_and_netfund | fees_pow_reward | net_fund | pow_reward | fees
------------------------+--------------+------------+-----------------+-----------------+-----------+------------+---------
2020-03-09 03:38:00+00 | 430354 | 466360305 | 1088174052 | 932736880 | 155453436 | 932720616 | 16264
2020-03-09 03:37:49+00 | 430353 | 466360305 | 1088174052 | 932800966 | 155453436 | 932720616 | 80350
2020-03-09 03:36:44+00 | 430352 | 466360305 | 1088174052 | 936213068 | 155453436 | 932720616 | 3492452
2020-03-09 03:25:11+00 | 430351 | 466360305 | 1088174052 | 935152952 | 155453436 | 932720616 | 2432336
(4 rows)
If you want to know how much time you are spending one each query, turn on/off timing with the command \timing:
dcrdata=# \timing
Timing is on.
dcrdata=#
This query will run after being prepared and saved in memory with name ‘prep_query’. Prepared queries can use variables (created in the first line of the next statement and used inside first WITH statement) that will be set when executed (next block of code):
dcrdata=#
PREPARE prep_query(int, int) AS
WITH pre_select AS (
SELECT transactions.block_time as block_time, transactions.block_height as block_height, transactions.tx_hash as tx_hash, transactions.tx_type as tx_type, vins.prev_tx_hash, vins.value_in as value_in, vouts.value as vouts_value, vouts.script_addresses as addresses
FROM transactions
LEFT JOIN vins ON transactions.tx_hash = vins.tx_hash
LEFT JOIN vouts ON transactions.tx_hash = vouts.tx_hash
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND transactions.is_mainchain
AND transactions.block_height > $1
AND transactions.block_height < $2
),
table_pos_reward AS (
SELECT pre_select.block_height, pre_select.value_in as value_in
FROM pre_select
LEFT JOIN votes ON pre_select.tx_hash = votes.tx_hash
WHERE pre_select.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND pre_select.tx_type = 2
AND NOT pre_select.vouts_value = 0
GROUP BY 1,2
),
table_pow_reward AS (
SELECT pre_select.block_height, pre_select.vouts_value as value_out
FROM pre_select
WHERE NOT (pre_select.addresses = '{Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}' OR pre_select.addresses = '{}')
AND pre_select.tx_type = 0
),
table_net_fund AS (
SELECT pre_select.block_height, pre_select.vouts_value as value_out
FROM pre_select
WHERE pre_select.addresses = '{Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}'
)
SELECT split_part(CAST (date_trunc('day', pre_select.block_time) AS text), ' ', 1) "day",
pre_select.block_height,
table_pos_reward.value_in AS pos_reward,
pre_select.value_in AS pow_and_netfund,
table_pow_reward.value_out AS fees_pow_reward,
table_net_fund.value_out AS net_fund,
(pre_select.value_in - table_net_fund.value_out) AS pow_reward,
(table_pow_reward.value_out - (pre_select.value_in - table_net_fund.value_out)) AS fees
FROM pre_select
LEFT JOIN table_pos_reward ON table_pos_reward.block_height = pre_select.block_height
LEFT JOIN table_pow_reward ON table_pow_reward.block_height = pre_select.block_height
LEFT JOIN table_net_fund ON table_net_fund.block_height = pre_select.block_height
WHERE pre_select.addresses = '{Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}'
ORDER BY 1,2 DESC;
The previous command will return ‘PREPARE’. With this prepared query in memory, you can go ahead and execute it, setting the variables at execution time:
PREPARE
dcrdata=# EXECUTE prep_query(430350, 430356);
day | block_height | pos_reward | pow_and_netfund | fees_pow_reward | net_fund | pow_reward | fees
------------+--------------+------------+-----------------+-----------------+-----------+------------+---------
2020-03-09 | 430355 | 93272061 | 1088174052 | 933059337 | 155453436 | 932720616 | 338721
2020-03-09 | 430354 | 93272061 | 1088174052 | 932736880 | 155453436 | 932720616 | 16264
2020-03-09 | 430353 | 93272061 | 1088174052 | 932800966 | 155453436 | 932720616 | 80350
2020-03-09 | 430352 | 93272061 | 1088174052 | 936213068 | 155453436 | 932720616 | 3492452
2020-03-09 | 430351 | 93272061 | 1088174052 | 935152952 | 155453436 | 932720616 | 2432336
(5 rows)
Time: 1337.231 ms (00:01.337)
If a query is taking too long, you can use the EXPLAIN statement to make PostgreSQL show you how it is spending resources planning and executing the query so you can try to optimise it. For the already prepared query, just insert ‘EXPLAIN ANALYZE’ before the execution command.
dcrdata=# EXPLAIN ANALYZE EXECUTE query(430350, 430356);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=381285.52..381285.53 rows=1 width=88) (actual time=1323.605..1323.609 rows=5 loops=1)
Sort Key: (split_part((date_trunc('day'::text, pre_select.block_time))::text, ' '::text, 1)), pre_select.block_height DESC
Sort Method: quicksort Memory: 25kB
CTE pre_select
-> Nested Loop Left Join (cost=1001.25..381276.45 rows=4 width=213) (actual time=672.349..1321.550 rows=100 loops=1)
-> Nested Loop (cost=1000.56..381203.29 rows=1 width=158) (actual time=672.313..1320.606 rows=30 loops=1)
-> Gather (cost=1000.00..381122.32 rows=1 width=85) (actual time=672.250..1324.611 rows=89 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on transactions (cost=0.00..380122.22 rows=1 width=85) (actual time=657.914..1306.638 rows=30 loops=3)
Filter: (is_mainchain AND (block_height > 430350) AND (block_height < 430356))
Rows Removed by Filter: 2486292
-> Index Scan using uix_vin on vins (cost=0.56..80.94 rows=3 width=138) (actual time=0.020..0.021 rows=0 loops=89)
Index Cond: (tx_hash = transactions.tx_hash)
Filter: (prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'::text)
Rows Removed by Filter: 6
-> Index Scan using uix_vout_txhash_ind on vouts (cost=0.69..72.98 rows=17 width=120) (actual time=0.019..0.022 rows=3 loops=30)
Index Cond: (tx_hash = transactions.tx_hash)
(...)
To PREPARE another query with the same name, DEALLOCATE the already prepared query:
dcrdata=# DEALLOCATE prep_query;
DEALLOCATE
4. Analysing governance
To analyse some characteristics of Decred governance we can collect statistics per day and per block:
- Model in paper or mindmap what you are trying to achieve
- Formulate your hypothesis
- Build queries and extract data that might help you validate your hypothesis
These numbers will be used in another article.
-- # Daily statistics
-- ## PoS rewards
COPY (
SELECT split_part(CAST (date_trunc('day', transactions.block_time) AS text), ' ', 1) "day",
round(sum(vins.value_in)/ 100000000::numeric, 8) AS sum_pos_reward
FROM transactions
LEFT JOIN vins ON transactions.tx_hash = vins.tx_hash
LEFT JOIN votes ON transactions.tx_hash = votes.tx_hash
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND transactions.is_mainchain
AND transactions.tx_type = 2
GROUP BY 1
ORDER BY 1)
TO '/tmp/psql_daily_pos-rewards.csv' CSV HEADER;
-- ## Transactions count, volume
COPY (
SELECT split_part(CAST (date_trunc('day', transactions.block_time) AS text), ' ', 1) "day",
count(*) AS tx_count,
round(sum(transactions.sent)/ 100000000::numeric, 8) AS sum_sent,
round(sum(transactions.fees)/ 100000000::numeric, 8) AS sum_fees
FROM transactions
WHERE transactions.fees > 0
AND transactions.is_mainchain
GROUP BY 1
ORDER BY 1)
TO '/tmp/psql_daily_transactions-volume.csv' CSV HEADER;
-- ## Block count
COPY (
SELECT split_part(CAST (date_trunc('day', blocks.time) AS text), ' ', 1) "day",
COUNT(DISTINCT blocks.height) AS block_count
FROM blocks
WHERE blocks.is_mainchain
GROUP BY 1
ORDER BY 1)
TO '/tmp/psql_daily_block-count.csv' CSV HEADER;
-- ## Block count, historical average (count) per day
COPY (
SELECT split_part(CAST (date_trunc('day', blocks.time) AS text), ' ', 1) "day",
COUNT(*) AS block_count
FROM blocks
WHERE blocks.is_mainchain
GROUP BY 1
UNION ALL
SELECT 'avg = ', round(avg(a.block_count), 2) FROM
(SELECT split_part(CAST (date_trunc('day', blocks.time) AS text), ' ', 1) "day",
COUNT(*) AS block_count
FROM blocks
WHERE blocks.is_mainchain
GROUP BY 1) a
ORDER BY 1)
TO '/tmp/psql_daily_block-time-historical-average.csv' CSV HEADER;
-- ## PoS reward per ticket / per block / per day
COPY (
SELECT tickets.tx_hash, tickets.block_height, tickets.spend_height,
(tickets.spend_height - tickets.block_height) AS waited_blocks,
tickets.price, votes.vote_reward,
split_part(CAST (date_trunc('day', votes.block_time) AS text), ' ', 1) "day"
FROM tickets
LEFT JOIN votes ON tickets.tx_hash = votes.ticket_hash
WHERE tickets.is_mainchain
AND tickets.spend_type = 2
ORDER BY 3)
TO '/tmp/psql_block_pos-reward-time-ticket-blocks.csv' CSV HEADER;
-- ## Ticket price / pool size / pool value
COPY (
SELECT split_part(CAST (date_trunc('day', blocks.time) AS text), ' ', 1) "day",
round(avg(tickets.price)::numeric, 8) AS avg_price,
round(avg(stats.pool_size)::numeric, 0) AS avg_pool,
round(avg(pool_val / 100000000)::numeric, 8) AS avg_pool_dcr
FROM tickets
LEFT JOIN blocks ON tickets.block_hash = blocks.hash
LEFT JOIN stats ON tickets.block_height = stats.height
WHERE tickets.is_mainchain
GROUP BY 1
ORDER BY 1)
TO '/tmp/psql_daily_ticket-price_pool-size.csv' CSV HEADER;
-- ## Coin Issuance / Supply
COPY (
SELECT x.day,
x.total_reward AS sum_total_reward,
sum(x.total_reward) OVER (ORDER BY x.day ASC) as sum_reward
FROM (
SELECT t.day,
sum(t.total_reward) as total_reward
FROM (
SELECT split_part(CAST (date_trunc('day', transactions.block_time) AS text), ' ', 1) "day",
transactions.block_height,
round(sum(vins.value_in)/100000000, 8) as total_reward
FROM vins
JOIN transactions ON vins.tx_hash = transactions.tx_hash
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND NOT (vins.is_valid = false)
AND vins.is_mainchain
GROUP BY 1,2
ORDER BY 2 ASC
) AS t
GROUP BY 1
) AS x)
TO '/tmp/psql_daily_coin-issuance.csv' CSV HEADER;
-- ## Average voters per block per day, average difficulty and hashpower (GH/s) per day
COPY (
SELECT split_part(CAST (date_trunc('day', blocks.time) AS text), ' ', 1) "day",
round(avg(blocks.voters::numeric), 0) AS avg_block_voters,
round(avg(blocks.pool_size)::numeric, 0) AS avg_block_poolsize,
round(avg(blocks.difficulty)::numeric, 8) AS avg_block_difficulty,
round(avg(blocks.difficulty*(2^32)/300/1000000000)::numeric, 8) AS avg_hashrate
FROM blocks
WHERE blocks.is_mainchain
GROUP BY 1
ORDER BY 1)
TO '/tmp/psql_daily_voters_difficulty.csv' CSV HEADER;
-- ## Network funding per day
COPY (
SELECT split_part(CAST (date_trunc('day', transactions.block_time) AS text), ' ', 1) "day",
round(sum(vouts.value)/100000000::numeric, 8) AS netfund_reward
FROM transactions
JOIN vins ON transactions.tx_hash = vins.tx_hash
JOIN vouts ON transactions.tx_hash = vouts.tx_hash
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND vouts.script_addresses = '{Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}'
AND transactions.is_mainchain
GROUP BY 1
ORDER BY 1)
TO '/tmp/psql_daily_network-funding.csv' CSV HEADER;
-- ## Fees, difficulty, transaction count per day
COPY (
SELECT split_part(CAST (date_trunc('day', transactions.block_time) AS text), ' ', 1) "day",
round(sum(transactions.fees) / 100000000::numeric, 8) as sum_fees,
blocks.difficulty,
count(*) as count_tx
FROM transactions
LEFT JOIN blocks ON blocks.height = transactions.block_height
WHERE transactions.fees > 0
AND transactions.is_mainchain
GROUP BY 1,3
ORDER BY 1)
TO '/tmp/psql_daily_fees-difficulty-txcount.csv' CSV HEADER;
-- # Block statistics
-- ## Transactions count, volume
COPY (
SELECT transactions.block_height,
count(*) AS tx_count,
sum(transactions.sent) AS sum_sent,
sum(transactions.fees) AS sum_fees
FROM transactions
WHERE transactions.is_mainchain
GROUP BY 1
ORDER BY 1)
TO '/tmp/psql_block_transactions-volume.csv' (DELIMITER ',');
-- ## Ticket price / pool size / pool value
COPY (
SELECT blocks.height,
round(avg(tickets.price)::numeric, 8) AS avg_price,
round(avg(stats.pool_size)::numeric, 0) AS avg_pool,
round(avg(pool_val / 100000000)::numeric, 8) AS avg_pool_dcr
FROM tickets
LEFT JOIN blocks ON tickets.block_hash = blocks.hash
LEFT JOIN stats ON tickets.block_height = stats.height
WHERE tickets.is_mainchain
GROUP BY 1
ORDER BY 1)
TO '/tmp/psql_block_ticket-price_pool-size.csv' CSV HEADER;
-- ## Coin Issuance / Supply (total reward)
COPY (
SELECT t.block_time,
t.block_height,
t.total_reward AS sum_total_reward,
round(sum(t.total_reward) OVER (ORDER BY t.block_time ASC), 8) as acum_total_reward
FROM (
SELECT vins.block_time,
transactions.block_height,
round(sum(vins.value_in)/100000000, 8) as total_reward
FROM vins
JOIN transactions ON vins.tx_hash = transactions.tx_hash
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND NOT (vins.is_valid = false)
AND vins.is_mainchain
GROUP BY 1,2
ORDER BY 2 ASC)
AS t LIMIT 10)
TO '/tmp/psql_block_coin-issuance.csv' CSV HEADER;
-- ## Coin Issuance / Supply (PoS reward)
COPY (
SELECT transactions.block_time, transactions.block_height, transactions.tx_type, round(round(vins.value_in, 8)/100000000, 8) AS pos_reward, votes.block_valid
FROM transactions
JOIN vins ON transactions.tx_hash = vins.tx_hash
JOIN blocks ON blocks.height = transactions.block_height
JOIN votes ON transactions.tx_hash = votes.tx_hash
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND blocks.is_mainchain
ORDER BY 1)
TO '/tmp/psql_block_coin-issuance-pos_reward.csv' CSV HEADER;
-- ## Coin Issuance / Supply (Network fund)
COPY (
SELECT transactions.block_time, transactions.block_height, transactions.tx_type, round(round(vouts.value, 8)/100000000, 8) AS net_fund
FROM transactions
JOIN vins ON transactions.tx_hash = vins.tx_hash
JOIN vouts ON transactions.tx_hash = vouts.tx_hash
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND vouts.script_addresses = '{Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}'
AND transactions.is_mainchain
ORDER BY 1)
TO '/tmp/psql_block_coin-issuance-network_fund.csv' CSV HEADER;
-- ## Coin Issuance / Supply (PoW reward)
COPY (
SELECT transactions.block_time, transactions.block_height, transactions.tx_type, round(round(vouts.value, 8)/100000000, 8) AS pow_reward, vouts.script_addresses
FROM transactions
JOIN vins ON transactions.tx_hash = vins.tx_hash
JOIN vouts ON transactions.tx_hash = vouts.tx_hash
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND NOT (vouts.script_addresses = '{Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}' OR vouts.script_addresses = '{}')
AND transactions.is_mainchain
AND transactions.tx_type = 0
ORDER BY 1)
TO '/tmp/psql_block_coin-issuance-pow_reward.csv' CSV HEADER;
-- ## Voters / Difficulty
COPY (
SELECT blocks.time, blocks.height, blocks.voters, blocks.pool_size, blocks.difficulty
FROM blocks
WHERE blocks.is_mainchain
ORDER BY blocks.height)
TO '/tmp/psql_block_voters_difficulty.csv' CSV HEADER;
-- ## Fees, difficulty, transaction count per block
COPY (
SELECT transactions.block_height,
round(sum(transactions.fees) / 100000000::numeric, 8) AS sum_fees,
blocks.difficulty, count(*) AS tx_count
FROM transactions
LEFT JOIN blocks ON blocks.height = transactions.block_height
WHERE transactions.fees > 0
AND transactions.is_mainchain
GROUP BY 1,3
ORDER BY 1)
TO '/tmp/psql_block_fees-difficulty-txcount.csv' CSV HEADER;
-- # General statistics
-- ## Voters per block, mined tickets
COPY (
SELECT voters AS voters_per_block, count(*) count
FROM blocks
WHERE blocks.is_mainchain
GROUP BY 1
ORDER BY 1 ASC)
TO '/tmp/psql_general_voters-block-count-alltime-aggregate.csv' CSV HEADER;
COPY (
SELECT voters AS voters_per_block, count(*) count
FROM blocks
WHERE blocks.is_mainchain
AND split_part(CAST (date_trunc('day', blocks.time) AS text), ' ', 1) between '2016-02-08' and '2020-02-10'
GROUP BY 1
ORDER BY 1 ASC)
TO '/tmp/psql_general_voters-block-count-2016-02-08-to-2020-02-10-aggregate.csv' CSV HEADER;
COPY (
SELECT blocks.time, blocks.height, voters AS voters_per_block
FROM blocks
WHERE blocks.is_mainchain
ORDER BY 1 ASC)
TO '/tmp/psql_general_voters_per_block-alltime.csv' CSV HEADER;
-- ## Agendas, lock-in blocks (on-chain)
COPY (
SELECT agendas.id, agendas.name, agendas.status, agendas.locked_in, blocks.time AS lockin_time, agendas.activated
FROM agendas
LEFT JOIN blocks ON blocks.height = agendas.locked_in)
TO '/tmp/psql_general_agendas-lockin.csv' CSV HEADER;
-- ## Agendas, voting results (on-chain)
COPY (
SELECT agendas.id, agendas.name,
COUNT(CASE WHEN agenda_votes.agenda_vote_choice = '0' THEN 1 ELSE NULL END) AS yes,
COUNT(CASE WHEN agenda_votes.agenda_vote_choice = '1' THEN 1 ELSE NULL END) AS abs,
COUNT(CASE WHEN agenda_votes.agenda_vote_choice = '2' THEN 1 ELSE NULL END) AS no
FROM agenda_votes
LEFT JOIN agendas ON agendas.id = agenda_votes.agendas_row_id
LEFT JOIN votes ON votes.id = agenda_votes.votes_row_id
LEFT JOIN blocks ON blocks.height = votes.height
WHERE votes.is_mainchain
AND votes.height <= agendas.locked_in
AND votes.height > agendas.locked_in - 8064
GROUP BY 1, 2
ORDER BY 1)
TO '/tmp/psql_general_agenda_vote_results.csv' CSV HEADER;
-- ## Votes per proposal per day (off-chain)
COPY (
SELECT proposals.token, split_part(CAST (date_trunc('day', proposals.time) AS text), ' ', 1) "day",
COUNT(CASE WHEN proposal_votes.choice = 'No' THEN 1 ELSE NULL END) AS no,
COUNT(CASE WHEN proposal_votes.choice = 'Yes' THEN 1 ELSE NULL END) AS yes
FROM proposal_votes
INNER JOIN proposals on proposals.id = proposal_votes.proposals_row_id
GROUP BY 1,2
ORDER BY 1)
TO '/tmp/psql_general_vote-change.csv' CSV HEADER;
-- ## Votes per proposal (off-chain)
COPY (
SELECT proposals.token, split_part(CAST (date_trunc('year', proposals.time) AS text), '-', 1) "year",
COUNT(CASE WHEN proposal_votes.choice = 'No' THEN 1 ELSE NULL END) AS no,
COUNT(CASE WHEN proposal_votes.choice = 'Yes' THEN 1 ELSE NULL END) AS yes,
COUNT(*) AS total
FROM proposal_votes
INNER JOIN proposals on proposals.id = proposal_votes.proposals_row_id
GROUP BY 1,2
ORDER BY 1)
TO '/tmp/psql_general_vote-total-number.csv' CSV HEADER;
-- ## Historical average ticket pool size
COPY (
SELECT 'avg = ', round(avg(a.pool_size), 0) FROM
(SELECT pool_size FROM stats) a)
TO '/tmp/psql_general_average-ticket-pool-size.csv' CSV HEADER;
-- ## Ticket time to draw
COPY (
SELECT votes.block_time, votes.ticket_hash, votes.height, tickets.block_height, votes.ticket_price, votes.vote_reward, tickets.is_split
FROM votes
LEFT JOIN tickets ON tickets.tx_hash = votes.ticket_hash
WHERE tickets.is_mainchain
ORDER BY 1)
TO '/tmp/psql_ticket-time-draw.csv' CSV HEADER;
-- ## Revoked tickets (transactions)
COPY (
SELECT transactions.block_height, transactions.tx_hash, transactions.tx_type
FROM transactions
WHERE transactions.is_mainchain
AND transactions.is_valid
AND transactions.tx_type = 3
ORDER BY 1)
TO '/tmp/psql_ticket-revoked.csv' CSV HEADER;
-- ## Genesis block coinbase spent, airdrop users, spent or not
COPY (
SELECT vouts.script_addresses, vouts.value, transactions.tx_hash, transactions.block_height, transactions.num_vout
FROM vouts
LEFT JOIN transactions ON vouts.spend_tx_row_id = transactions.id
WHERE vouts.tx_hash = '5e29cdb355b3fc7e76c98a9983cd44324b3efdd7815c866e33f6c72292cb8be6'
AND vouts.value = 28263795424
ORDER BY 4,2)
TO '/tmp/psql_transactions-genesis_block-airdrop.csv' CSV HEADER;
-- ## Genesis block coinbase spent, airdrop users, spent likely in tickets
COPY (
SELECT vouts.script_addresses, vouts.value, transactions.tx_hash, transactions.block_height, transactions.num_vout
FROM vouts
LEFT JOIN transactions ON vouts.spend_tx_row_id = transactions.id
WHERE vouts.tx_hash = '5e29cdb355b3fc7e76c98a9983cd44324b3efdd7815c866e33f6c72292cb8be6'
AND vouts.value = 28263795424
AND transactions.num_vout > 2
ORDER BY 4,2)
TO '/tmp/psql_transactions-genesis_block-airdrop-spent-likely-tickets.csv' CSV HEADER;
-- ## Genesis block coinbase spent, founders, spent or not
COPY (
SELECT vouts.script_addresses, vouts.value, transactions.tx_hash, transactions.block_height, transactions.num_vout
FROM vouts
LEFT JOIN transactions ON vouts.spend_tx_row_id = transactions.id
WHERE vouts.tx_hash = '5e29cdb355b3fc7e76c98a9983cd44324b3efdd7815c866e33f6c72292cb8be6'
AND NOT vouts.value = 28263795424
ORDER BY 4,2)
TO '/tmp/psql_transactions-genesis_block-founders.csv' CSV HEADER;
-- ## Genesis block coinbase, founders, spent likely in tickets
COPY (
SELECT vouts.script_addresses, vouts.value, transactions.tx_hash, transactions.block_height, transactions.num_vout, unnest(transactions.vout_db_ids) spent_id
FROM vouts
LEFT JOIN transactions ON vouts.spend_tx_row_id = transactions.id
WHERE vouts.tx_hash = '5e29cdb355b3fc7e76c98a9983cd44324b3efdd7815c866e33f6c72292cb8be6'
AND NOT vouts.value = 28263795424
AND transactions.num_vout > 2
ORDER BY 4,2)
TO '/tmp/psql_transactions-genesis_block-founders-spent-likely-tickets.csv' CSV HEADER;
The previous set of queries was copied to a text file and executed by psql as shown below.
$ psql -f /tmp/psql-queries.txt dcrdata
COPY 1514
COPY 1527
(...)