SQL Direct - example queries

Transactions

Get all TXN for an address - reverse order

SELECT
  encode(t.txid,'escape') tid -- no idea why textual TXID is saved in a bytea field
  ,t.*
FROM
  txn_participation tp JOIN txn t using(round,intra)
WHERE
  tp.addr =  AddressTxt2Bin('ALGONODEIBJTET5OSEAXIHDSIEG7C2DOFB2WDYLRZTXN3NXVJ3NJD26L4E');
ORDER BY round desc, intra desc
LIMIT 1000

Get TXN info by TXN id

SELECT * FROM txn WHERE txid = 'PHFCDXJOLNQX47Q3YFS6LBYMUMB2TG3QNHKFO3QXKG3P3BGE5U6Q'

Accounts

Get account info

SELECT
  AddressBin2Txt(addr) address, * FROM account
WHERE
  addr = AddressTxt2Bin('ALGONODEIBJTET5OSEAXIHDSIEG7C2DOFB2WDYLRZTXN3NXVJ3NJD26L4E');

Stats

Top ten transacted assets

SELECT
  topn(topn_add_agg(asset::text),10)
FROM
  txn
WHERE
  round > 18000000 and typeenum =6

Unique asset senders/receivers

SELECT
     COUNT(DISTINCT(txn -> 'txn' ->> 'snd')) senders
    ,COUNT(DISTINCT(txn -> 'txn' ->> 'arcv'  )) receivers
    ,COUNT(*) txcnt
FROM
    txn
WHERE
    txn.asset = 312769 and typeenum in (3,4,5)
    AND txn.round BETWEEN 18000000 and 18100000

Unique asset senders/receivers [faster]

SELECT
     hll_cardinality(hll_add_agg(hll_hash_text(txn -> 'txn' ->> 'snd'),12)) senders
    ,hll_cardinality(hll_add_agg(hll_hash_text(txn -> 'txn' ->> 'arcv'),12)) receivers
    ,COUNT(*) txcnt
FROM
    txn
WHERE
    txn.asset = 312769 and typeenum in (3,4,5)
    AND txn.round BETWEEN 18000000 and 18100000

Avg block time for a range

with tr as (
  select min(realtime) rt0, max(realtime) rt1
  from block_header bh
  where realtime between '2024-02-01' and '2024-03-01'
)
, b0 as (select round r0, (header#>>'{ts}')::int4 t0 from block_header bh,tr where realtime = tr.rt0)
, b1 as (select round r1, (header#>>'{ts}')::int4 t1 from block_header bh,tr where realtime = tr.rt1)
select 1.0 * (b1.t1-b0.t0) / (b1.r1-b0.r0) abt from b0,b1

Consensus

Stake needed to vote / propose

with rt as (
  select realtime, round from block_header bh order by round desc limit 100
), btime as
  (select EXTRACT(EPOCH FROM ((max(realtime) - min(realtime))/100)) as btime, max(round) lr from rt)
, stake as
  (select sum(microalgos)/pow(10,6) as stake from account
  cross join btime where account_data->>'onl' = '1' and (account_data->>'voteLst')::bigint > btime.lr)
select '1 hr' frequency, round(stake.stake/ (1*3600/btime.btime)) algo,
  round(10*(ln(1-1/(3600/btime.btime))/-2990)*stake.stake)/10 svalgo
  from btime cross join stake
union all
select '1 day' frequency, round(stake.stake/ (24*3600/btime.btime)) algo,
  round(10*(ln(1-1/(24*3600/btime.btime))/-2990)*stake.stake)/10 svalgo
  from btime cross join stake
union all
select '1 week' frequency, round(stake.stake/ (168*3600/btime.btime)) algo,
  round(10*(ln(1-1/(168*3600/btime.btime))/-2990)*stake.stake)/10 svalgo
  from btime cross join stake
union all
select '30 days' frequency, round(stake.stake/ (30*24*3600/btime.btime)) algo,
  round(10*(ln(1-1/(30*24*3600/btime.btime))/-2990)*stake.stake)/10 svalgo
  from btime cross join stake

All accounts with non-expired participation key

select
  addressbin2txt(addr) as haddr
  ,*
from
  account
where
  account_data->>'onl' = '1'
  and (account_data->>'voteLst')::bigint > (select max(round) from block_header)
order by
  microalgos desc