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