Querying dcrdata

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/.

Querying dcrdata
Querying dcrdata

By Marcelo Martins - June 29, 2020

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.

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)

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

To analyse some characteristics of Decred governance we can collect statistics per day and per block:

  1. Model in paper or mindmap what you are trying to achieve
  2. Formulate your hypothesis
  3. 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
(...)