SQL on Lisk blockchain via Lisk Docker and Adminer

Lisk Explorer is a great tool to view information stored on the Lisk blockchain, but in order to inspect the blockchain data in more detail we'll run SQL queries on Lisk blockchain database. By querying the database directly, more information can be extracted and the queries can be customized to select and analyze the data we want. E.g. it's easy to find out which delegate paid you the most, show all transactions for an account for the past n days, view lisk distribution and so on.

Lisk Docker will be used to automate the setup of the server, Lisk Core and PostgreSQL web client.

This tutorial assumes you have Docker installed and running.

First, download and unzip this archive.

(The archive contains docker-compose.yml, a copy of Lisk Docker mainnet image with additional postgres port exposed[1] and added Adminer image - PostgreSQL web client.)

Open Terminal, cd to directory where you unzipped archive and run:

make coldstart

Get a coffee and wait until it completes. This can take a few minutes.

(It will download the latest Lisk blockchain snapshot, docker images, create docker containers, start Lisk Core and restore Lisk blockchain database from the snapshot.)

Once done, it will auto-start synchronizing Lisk Core with main chain.

(You can visit http://localhost:8000/api/blocks/getHeight to check the current block height.)

Now open http://localhost:8080 to access Adminer login page:

Use:

System: PostgreSQL  
Server: db  
Username: lisk  
Password: password  
Database: lisk_main

Once logged in, click SQL command link and you're ready to execute SQL queries!

Here are some SQL queries you may find useful:

Which delegate paid me the most?

-- Which delegate paid me the most
WITH xs AS (  
  SELECT "senderId" AS address, username AS delegate
  FROM trs
  JOIN delegates ON delegates."transactionId" = trs."id"
)
SELECT  
  delegate, 
  SUM(trunc(amount::numeric/100000000, 8)) as amount
FROM trs  
JOIN xs ON xs.address = trs."senderId"  
WHERE "recipientId" = '14775206447342278732L' AND amount > 0  
GROUP BY delegate  
ORDER BY amount DESC;

-- Which delegate paid me the most in the last n days, months...
WITH xs AS (  
  SELECT "senderId" AS address, username AS delegate
  FROM trs
  JOIN delegates ON delegates."transactionId" = trs."id"
), constants(LISK_EPOCH) AS (
  -- 1464109200 = Lisk Epoch (2016–05–24T17:00:00.000Z)
  VALUES (1464109200)
)
SELECT  
  delegate, 
  SUM(trunc(amount::numeric/100000000, 8)) as amount
FROM constants, trs  
JOIN xs ON xs.address = trs."senderId"  
JOIN blocks ON blocks.id = trs."blockId"  
WHERE to_timestamp(blocks."timestamp" + LISK_EPOCH)  
  BETWEEN (now() - '6 months'::interval) AND now() -- Use '1 day', '2 days', '1 week', '6 months'...
AND amount > 0  
AND "recipientId" = '14775206447342278732L'  
GROUP BY delegate  
ORDER BY amount DESC;

Lisk Distribution

SELECT t.arange AS amount_range, COUNT(*) AS num_accounts  
FROM (  
  SELECT address, username AS delegate,
    CASE
      WHEN balance/100000000 <= 0                                             THEN '0'
      WHEN balance/100000000 > 0          AND balance/100000000 <= 1          THEN '0-1'
      WHEN balance/100000000 > 1          AND balance/100000000 <= 10         THEN '1-10'
      WHEN balance/100000000 > 10         AND balance/100000000 <= 100        THEN '10-100'
      WHEN balance/100000000 > 100        AND balance/100000000 <= 1000       THEN '100-1000'
      WHEN balance/100000000 > 1000       AND balance/100000000 <= 10000      THEN '1000-10000'
      WHEN balance/100000000 > 10000      AND balance/100000000 <= 100000     THEN '10000-100000'
      WHEN balance/100000000 > 100000     AND balance/100000000 <= 1000000    THEN '100000-1000000'
      WHEN balance/100000000 > 1000000    AND balance/100000000 <= 10000000   THEN '1000000-10000000'
      WHEN balance/100000000 > 10000000   AND balance/100000000 <= 100000000  THEN '10000000-100000000'
      WHEN balance/100000000 > 100000000                                      THEN '100000000+'
      ELSE 'unknown'
    END AS arange
  FROM mem_accounts
) AS t
GROUP BY t.arange  
ORDER BY t.arange DESC;

How much lisk is forged by each delegate

WITH xs AS (  
  SELECT "senderId" AS address, username AS delegate, "senderPublicKey" AS publicKey
  FROM trs
  JOIN delegates ON delegates."transactionId" = trs."id"
)
SELECT  
  delegate,
  SUM(reward/100000000) as reward
FROM blocks  
JOIN xs ON xs.publicKey = blocks."generatorPublicKey"  
WHERE reward > 0  
GROUP BY delegate  
ORDER BY reward DESC;

Total Lisk from forging (block rewards)

SELECT  
  SUM(reward/100000000) as reward
FROM blocks;

Above returns 24,453,036. Current supply according to Lisk Explorer is 124,453,036. I guess supply is the sum of the block (forging) rewards and 100,000,000 (premind Lisk)

All outgoing transactions for an account

-- Outgoing transactions
WITH constants(LISK_EPOCH) AS (  
  -- 1464109200 = Lisk Epoch (2016–05–24T17:00:00.000Z)
  VALUES (1464109200)
)
SELECT to_timestamp(blocks."timestamp" + LISK_EPOCH) as datetime,  
  "senderId", trunc(amount::numeric/100000000, 8) as amount
FROM constants, trs  
JOIN blocks ON blocks.id = trs."blockId"  
WHERE "senderId" = '14775206447342278732L' AND amount > 0;

All incoming transactions for an account

-- Incoming transactions
WITH constants(LISK_EPOCH) AS (  
  -- 1464109200 = Lisk Epoch (2016–05–24T17:00:00.000Z)
  VALUES (1464109200)
)
SELECT to_timestamp(blocks."timestamp" + LISK_EPOCH) as datetime,  
  "senderId",
  --"recipientId", 
  trunc(amount::numeric/100000000, 8) as amount
FROM constants, trs  
JOIN blocks ON blocks.id = trs."blockId"  
WHERE "recipientId" = '14775206447342278732L' AND amount > 0  
ORDER BY amount DESC;  
--ORDER BY datetime DESC;

To clean

docker-compose down

To start again

docker-compose up

[1] Not necessary for this tutorial, it's required if you want to use own PostgreSQL client eg. pgAdmin