NEAR Explorer SQLite cheatsheet

explorer-sqlite.js

sqlite3 = require('sqlite3')

db = new sqlite3.Database('db/testnet-database.sqlite')

db.all(`select name from sqlite_master where type='table'`, console.log)

db.all(`select * from transactions where actions like '%deploy%' limit 10`, console.log)

db.all(`select count(*) from transactions where actions like '%deploy%' limit 10`, console.log)

db.all(`select count(distinct signer_id) from transactions where actions like '%deploy%' limit 10`, console.log)

db.all(`select count(*) from transactions where actions like '%deploy%' and signer_id not like 'studio-%' limit 10`, console.log)

db.all(`select count(distinct signer_id) from transactions where actions like '%deploy%' and signer_id not like 'studio-%' limit 10`, console.log)

db.all(`select * from transactions as t join blocks as b on t.block_hash = b.hash where t.actions like '%deploy%' and t.signer_id not like 'studio-%' limit 10`, console.log)

db.all(`select min(d), max(d) from (select *, datetime(timestamp/1000, 'unixepoch') as d from transactions as t join blocks as b on t.block_has
h = b.hash where t.actions like '%deploy%' and t.signer_id not like 'studio-%') limit 10`, console.log)