Skip to content

Instantly share code, notes, and snippets.

@vgrichina
Last active December 3, 2021 17:29
Show Gist options
  • Save vgrichina/9ca87eb0d7b6c3b873279db44cfccd84 to your computer and use it in GitHub Desktop.
Save vgrichina/9ca87eb0d7b6c3b873279db44cfccd84 to your computer and use it in GitHub Desktop.
NEAR Explorer SQLite cheatsheet
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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment