Humans sleep.
Machines don't.

Long lists are shortened here. python -m pipeline.pull_bigquery --print 10 prints any step in full, ready to paste. Steps other than 10 and 15 read the table built by step 00.

sql/00_account_windows.sql

-- Step 0. One row per account per 14-day window, with its daily rhythm features.
-- Everything else reads this table, so the expensive scan of `transactions` happens once.
-- The filter on block_date is literal: only the sampled days are read and billed.
-- Not partitioned by date on purpose: BigQuery's free tier deletes date partitions older than 60 days.
CREATE OR REPLACE TABLE `your-project.proof_of_sleep.account_windows`
CLUSTER BY window_start, class AS
WITH slots AS (                             -- one row per account, day and hour with activity
  SELECT
    signer_account_id AS account,
    DATE_TRUNC(block_date, MONTH) AS window_start,
    block_date,
    EXTRACT(HOUR FROM block_timestamp_utc) AS hour,
    COUNT(*) AS n_tx
  FROM `bigquery-public-data.crypto_near_mainnet_us.transactions`
  WHERE block_date BETWEEN ... -- 70 two-week windows, listed in pipeline/config.py
  GROUP BY 1, 2, 3, 4
),
days AS (                                   -- which hours each active day used
  SELECT
    account, window_start, block_date,
    SUM(n_tx) AS n_tx,
    COUNT(*) AS n_slots,
    BIT_OR(1 << hour) AS day_mask
  FROM slots
  GROUP BY 1, 2, 3
),
accounts AS (                               -- all the hours of the clock the account ever used
  SELECT
    account, window_start,
    SUM(n_tx) AS n_tx,
    COUNT(*) AS n_days,
    SUM(n_slots) AS n_slots,
    BIT_OR(day_mask) AS hour_mask
  FROM days
  GROUP BY 1, 2
),
hours AS (
  SELECT *, ARRAY(SELECT h FROM UNNEST(GENERATE_ARRAY(0, 23)) AS h WHERE (hour_mask >> h) & 1 = 1) AS hs
  FROM accounts
),
gaps AS (                                   -- the longest stretch of hours never used, wrapping past midnight
  SELECT *,
    (SELECT AS STRUCT gap, start_h
     FROM (
       SELECT h AS start_h,
              COALESCE((SELECT MIN(h2) FROM UNNEST(hs) AS h2 WHERE h2 > h), hs[OFFSET(0)] + 24) - h - 1 AS gap
       FROM UNNEST(hs) AS h
     )
     ORDER BY gap DESC, start_h
     LIMIT 1) AS g
  FROM hours
),
classified AS (
  SELECT
    account, window_start, n_tx, n_days, n_slots, hour_mask,
    ARRAY_LENGTH(hs) AS n_hours,
    g.gap AS gap,
    -- middle of the quiet stretch, as a clock hour (UTC)
    (g.start_h + 1 + g.gap / 2) - 24 * FLOOR((g.start_h + 1 + g.gap / 2) / 24) AS mid,
    CASE
      WHEN n_days < 5 THEN 'too_few'
      WHEN g.gap <= 2 AND n_slots / n_days >= 8 THEN 'machine'
      WHEN ARRAY_LENGTH(hs) <= 3 THEN 'timer'
      WHEN g.gap >= 5 AND g.gap >= [99,99,99,20,17,15,13,12,11,10,9,8,7,7,6,6,5,5,4,4,3,3,99,99,99][OFFSET(ARRAY_LENGTH(hs))] AND g.gap <= 14 AND ARRAY_LENGTH(hs) >= 6 THEN 'sleeper'
      WHEN g.gap >= 5 AND g.gap >= [99,99,99,20,17,15,13,12,11,10,9,8,7,7,6,6,5,5,4,4,3,3,99,99,99][OFFSET(ARRAY_LENGTH(hs))] THEN 'routine'
      ELSE 'unclear'
    END AS class
  FROM gaps
)
SELECT                                      -- accounts seen on 1-2 days are kept only as counts
  IF(n_days >= 3, account, '_light_') AS account,
  window_start,
  COUNT(*) AS accounts,
  SUM(n_tx) AS n_tx,
  n_days,
  SUM(n_slots) AS n_slots,
  IF(n_days >= 3, ANY_VALUE(hour_mask), NULL) AS hour_mask,
  IF(n_days >= 3, ANY_VALUE(n_hours), NULL) AS n_hours,
  IF(n_days >= 3, ANY_VALUE(gap), NULL) AS gap,
  IF(n_days >= 3, ANY_VALUE(mid), NULL) AS mid,
  IF(n_days >= 3, ANY_VALUE(class), 'light') AS class
FROM classified
GROUP BY 1, 2, n_days

sql/01_classes.sql

-- Headline table: how many accounts fall in each class in each 14-day window,
-- and how many transactions each class sent.
SELECT
  window_start,
  class,
  SUM(accounts) AS accounts,
  SUM(n_tx) AS txs
FROM `your-project.proof_of_sleep.account_windows`
GROUP BY 1, 2
ORDER BY 1, 2

Long lists are shortened here. python -m pipeline.pull_bigquery --print 10 prints any step in full, ready to paste. Steps other than 10 and 15 read the table built by step 00.

sql/01_classes.sql

-- Headline table: how many accounts fall in each class in each 14-day window,
-- and how many transactions each class sent.
SELECT
  window_start,
  class,
  SUM(accounts) AS accounts,
  SUM(n_tx) AS txs
FROM `your-project.proof_of_sleep.account_windows`
GROUP BY 1, 2
ORDER BY 1, 2

sql/07_concentration.sql

-- How concentrated are transactions? Share of each window's transactions sent by the
-- busiest accounts. Aggregate counts only.
WITH ranked AS (
  SELECT
    window_start,
    n_tx,
    class,
    ROW_NUMBER() OVER (PARTITION BY window_start ORDER BY n_tx DESC, account) AS rnk
  FROM `your-project.proof_of_sleep.account_windows`
  WHERE account != '_light_'
)
SELECT
  window_start,
  SUM(n_tx) AS txs_in_table,
  SUM(IF(rnk <= 10, n_tx, 0)) AS top_10,
  SUM(IF(rnk <= 100, n_tx, 0)) AS top_100,
  SUM(IF(rnk <= 1000, n_tx, 0)) AS top_1000,
  COUNTIF(rnk <= 100 AND class = 'machine') AS top100_machines,
  COUNTIF(rnk <= 100 AND class = 'sleeper') AS top100_sleepers
FROM ranked
GROUP BY 1
ORDER BY 1

Long lists are shortened here. python -m pipeline.pull_bigquery --print 10 prints any step in full, ready to paste. Steps other than 10 and 15 read the table built by step 00.

sql/16_verified.sql

-- Outside check. the verified accounts were verified as unique humans by a face scan
-- (NEAR's I-Am-Human soulbound tokens, issuer fractal.i-am-human.near). How do they score?
-- Output is counts by feature, never individual accounts.
SELECT
  window_start,
  gap,
  n_hours,
  n_days,
  n_slots,
  class,
  COUNT(*) AS account_windows
FROM `your-project.proof_of_sleep.account_windows`
WHERE account IN UNNEST([ ... the verified account names, read from the chain by pipeline/pull_verified.py ... ]) AND n_days >= 5
GROUP BY 1, 2, 3, 4, 5, 6
ORDER BY 1, 2, 3, 4, 5, 6

Long lists are shortened here. python -m pipeline.pull_bigquery --print 10 prints any step in full, ready to paste. Steps other than 10 and 15 read the table built by step 00.

sql/16_verified.sql

-- Outside check. the verified accounts were verified as unique humans by a face scan
-- (NEAR's I-Am-Human soulbound tokens, issuer fractal.i-am-human.near). How do they score?
-- Output is counts by feature, never individual accounts.
SELECT
  window_start,
  gap,
  n_hours,
  n_days,
  n_slots,
  class,
  COUNT(*) AS account_windows
FROM `your-project.proof_of_sleep.account_windows`
WHERE account IN UNNEST([ ... the verified account names, read from the chain by pipeline/pull_verified.py ... ]) AND n_days >= 5
GROUP BY 1, 2, 3, 4, 5, 6
ORDER BY 1, 2, 3, 4, 5, 6

Long lists are shortened here. python -m pipeline.pull_bigquery --print 10 prints any step in full, ready to paste. Steps other than 10 and 15 read the table built by step 00.

sql/15_calibration.sql

-- Chance check. About 8,000 randomly chosen accounts from one 14-day window, each as a list
-- of daily activity masks (one 24-bit number per active day). No account names leave BigQuery.
-- pipeline/analyze.py shuffles each day around the clock and re-runs the test on the result.
WITH slots AS (
  SELECT signer_account_id AS account, block_date, EXTRACT(HOUR FROM block_timestamp_utc) AS hour
  FROM `bigquery-public-data.crypto_near_mainnet_us.transactions`
  WHERE block_date BETWEEN DATE '2026-10-01' AND DATE '2026-10-14'
  GROUP BY 1, 2, 3
),
daymask AS (
  SELECT account, block_date, BIT_OR(1 << hour) AS m
  FROM slots
  GROUP BY 1, 2
),
acct AS (
  SELECT account, ARRAY_AGG(m ORDER BY block_date) AS ms
  FROM daymask
  GROUP BY 1
  HAVING ARRAY_LENGTH(ms) >= 5
)
SELECT ARRAY_TO_STRING(ARRAY(SELECT FORMAT('%x', m) FROM UNNEST(ms) AS m), ',') AS day_masks
FROM acct
WHERE MOD(ABS(FARM_FINGERPRINT(account)), 100) < 14

Long lists are shortened here. python -m pipeline.pull_bigquery --print 10 prints any step in full, ready to paste. Steps other than 10 and 15 read the table built by step 00.

sql/08_dst.sql

-- Natural experiment: when clocks change, do accounts move with them?
-- 1. Take accounts that pass the sleep test in the 14 days before a clock change.
-- 2. Sort them into regions using only where their quiet stretch sat BEFORE the change.
-- 3. Count when those same accounts were active before and after the change.
-- Placebo events use ordinary dates with no clock change anywhere.
-- Output is counts per event, group, period and hour: never individual accounts.
WITH slots AS (
  SELECT signer_account_id AS account, block_date, EXTRACT(HOUR FROM block_timestamp_utc) AS hour
  FROM `bigquery-public-data.crypto_near_mainnet_us.transactions`
  WHERE block_date BETWEEN ... -- days around each clock change, see pipeline/dst_events.py
  GROUP BY 1, 2, 3
),
tagged AS (
  SELECT e.event, IF(s.block_date <= e.pre_end, 'pre', 'post') AS period, s.account, s.block_date, s.hour
  FROM slots AS s
  JOIN UNNEST([ ... the clock-change dates, see pipeline/dst_events.py ... ]) AS e
    ON s.block_date BETWEEN e.pre_start AND e.pre_end
    OR s.block_date BETWEEN e.post_start AND e.post_end
),
days AS (
  SELECT event, period, account, block_date,
         COUNT(*) AS n_slots,
         BIT_OR(1 << hour) AS day_mask,
         ARRAY_AGG(hour) AS hours
  FROM tagged
  GROUP BY 1, 2, 3, 4
),
periods AS (
  SELECT event, period, account,
         COUNT(*) AS n_days,
         SUM(n_slots) AS n_slots,
         BIT_OR(day_mask) AS hour_mask,
         ARRAY_CONCAT_AGG(hours) AS hours
  FROM days
  GROUP BY 1, 2, 3
),
with_hours AS (
  SELECT *, ARRAY(SELECT h FROM UNNEST(GENERATE_ARRAY(0, 23)) AS h WHERE (hour_mask >> h) & 1 = 1) AS hs
  FROM periods
),
features AS (
  SELECT event, period, account, n_days, n_slots, hours, ARRAY_LENGTH(hs) AS n_hours,
    (SELECT AS STRUCT gap, start_h
     FROM (
       SELECT h AS start_h,
              COALESCE((SELECT MIN(h2) FROM UNNEST(hs) AS h2 WHERE h2 > h), hs[OFFSET(0)] + 24) - h - 1 AS gap
       FROM UNNEST(hs) AS h
     )
     ORDER BY gap DESC, start_h
     LIMIT 1) AS g
  FROM with_hours
),
pivoted AS (                                  -- both periods side by side for each account
  SELECT event, account,
    MAX(IF(period = 'pre', n_days, NULL)) AS n_days,
    MAX(IF(period = 'post', n_days, NULL)) AS post_days,
    MAX(IF(period = 'pre', n_slots, NULL)) AS n_slots,
    MAX(IF(period = 'pre', n_hours, NULL)) AS n_hours,
    MAX(IF(period = 'pre', g.gap, NULL)) AS gap,
    MAX(IF(period = 'pre', (g.start_h + 1 + g.gap / 2) - 24 * FLOOR((g.start_h + 1 + g.gap / 2) / 24), NULL)) AS pre_mid,
    ARRAY_CONCAT_AGG(IF(period = 'pre', hours, CAST([] AS ARRAY<INT64>))) AS pre_hours,
    ARRAY_CONCAT_AGG(IF(period = 'post', hours, CAST([] AS ARRAY<INT64>))) AS post_hours
  FROM features
  GROUP BY 1, 2
),
cohort AS (
  SELECT event, pre_hours, post_hours,
    CASE
      WHEN (CASE
      WHEN n_days < 5 THEN 'too_few'
      WHEN gap <= 2 AND n_slots / n_days >= 8 THEN 'machine'
      WHEN n_hours <= 3 THEN 'timer'
      WHEN gap >= 5 AND gap >= [99,99,99,20,17,15,13,12,11,10,9,8,7,7,6,6,5,5,4,4,3,3,99,99,99][OFFSET(n_hours)] AND gap <= 14 AND n_hours >= 6 THEN 'sleeper'
      WHEN gap >= 5 AND gap >= [99,99,99,20,17,15,13,12,11,10,9,8,7,7,6,6,5,5,4,4,3,3,99,99,99][OFFSET(n_hours)] THEN 'routine'
      ELSE 'unclear'
    END) = 'machine' THEN 'machines'
      WHEN (CASE
      WHEN n_days < 5 THEN 'too_few'
      WHEN gap <= 2 AND n_slots / n_days >= 8 THEN 'machine'
      WHEN n_hours <= 3 THEN 'timer'
      WHEN gap >= 5 AND gap >= [99,99,99,20,17,15,13,12,11,10,9,8,7,7,6,6,5,5,4,4,3,3,99,99,99][OFFSET(n_hours)] AND gap <= 14 AND n_hours >= 6 THEN 'sleeper'
      WHEN gap >= 5 AND gap >= [99,99,99,20,17,15,13,12,11,10,9,8,7,7,6,6,5,5,4,4,3,3,99,99,99][OFFSET(n_hours)] THEN 'routine'
      ELSE 'unclear'
    END) != 'sleeper' THEN 'drop'
      WHEN pre_mid >= 23 OR pre_mid < 3.5 THEN 'europe'
      WHEN pre_mid >= 6.5 AND pre_mid < 13 THEN 'americas'
      WHEN pre_mid >= 16 AND pre_mid < 23 THEN 'asia'
      ELSE 'in_between'
    END AS grp
  FROM pivoted
  WHERE n_days IS NOT NULL AND post_days >= 3     -- same accounts, active on both sides of the change
)
SELECT event, grp, x.period, x.hour, COUNT(*) AS slots
FROM cohort,
  UNNEST(ARRAY_CONCAT(
    ARRAY(SELECT AS STRUCT 'pre' AS period, h AS hour FROM UNNEST(pre_hours) AS h),
    ARRAY(SELECT AS STRUCT 'post' AS period, h AS hour FROM UNNEST(post_hours) AS h)
  )) AS x
WHERE grp != 'drop'
GROUP BY 1, 2, 3, 4
ORDER BY 1, 2, 3, 4

Long lists are shortened here. python -m pipeline.pull_bigquery --print 10 prints any step in full, ready to paste. Steps other than 10 and 15 read the table built by step 00.

sql/06_sensitivity.sql

-- How much does the count depend on the thresholds? Recounts sleepers under other choices,
-- changing one rule at a time. Uses the table from step 0, so it costs almost nothing.
WITH t AS (
  SELECT window_start, n_days, n_slots, n_hours, gap, [99,99,99,20,17,15,13,12,11,10,9,8,7,7,6,6,5,5,4,4,3,3,99,99,99][OFFSET(n_hours)] AS c05, [99,99,99,99,19,17,15,14,13,11,10,9,9,8,7,7,6,6,5,5,4,99,99,99,99][OFFSET(n_hours)] AS c01
  FROM `your-project.proof_of_sleep.account_windows`
  WHERE account != '_light_'
)
SELECT
  window_start,
  COUNTIF(n_days >= 5 AND n_hours >= 6 AND gap BETWEEN 5 AND 14 AND gap >= c05) AS base,
  COUNTIF(n_days >= 5 AND n_hours >= 6 AND gap BETWEEN 5 AND 14 AND gap >= c01) AS alpha_01,
  COUNTIF(n_days >= 5 AND n_hours >= 4 AND gap BETWEEN 5 AND 14 AND gap >= c05) AS hours_4,
  COUNTIF(n_days >= 5 AND n_hours >= 8 AND gap BETWEEN 5 AND 14 AND gap >= c05) AS hours_8,
  COUNTIF(n_days >= 5 AND n_hours >= 10 AND gap BETWEEN 5 AND 14 AND gap >= c05) AS hours_10,
  COUNTIF(n_days >= 5 AND n_hours >= 6 AND gap BETWEEN 5 AND 12 AND gap >= c05) AS gap_max_12,
  COUNTIF(n_days >= 5 AND n_hours >= 6 AND gap BETWEEN 5 AND 18 AND gap >= c05) AS gap_max_18,
  COUNTIF(n_days >= 4 AND n_hours >= 6 AND gap BETWEEN 5 AND 14 AND gap >= c05) AS days_4,
  COUNTIF(n_days >= 6 AND n_hours >= 6 AND gap BETWEEN 5 AND 14 AND gap >= c05) AS days_6,
  COUNTIF(n_days >= 7 AND n_hours >= 6 AND gap BETWEEN 5 AND 14 AND gap >= c05) AS days_7,
  COUNTIF(n_days >= 5 AND n_hours >= 6 AND gap BETWEEN 5 AND 14) AS no_chance_test
FROM t
WHERE n_hours IS NOT NULL
GROUP BY 1
ORDER BY 1

Long lists are shortened here. python -m pipeline.pull_bigquery --print 10 prints any step in full, ready to paste. Steps other than 10 and 15 read the table built by step 00.

sql/10_daily_totals.sql

-- Plain daily activity from the raw table, for the most recent stretch of data:
-- transactions, distinct signing accounts, distinct contracts called.
SELECT
  block_date,
  COUNT(*) AS transactions,
  COUNT(DISTINCT signer_account_id) AS active_accounts,
  COUNT(DISTINCT receiver_account_id) AS contracts_called
FROM `bigquery-public-data.crypto_near_mainnet_us.transactions`
WHERE block_date BETWEEN DATE '2026-09-01' AND DATE '2026-11-08'
GROUP BY 1
ORDER BY 1

Long lists are shortened here. python -m pipeline.pull_bigquery --print 10 prints any step in full, ready to paste. Steps other than 10 and 15 read the table built by step 00.

sql/11_families.sql

-- Who are the accounts that pass the sleep test? Sleepers by account family, with the
-- UTC hour of each sleeper's quiet stretch. A family whose sleepers all sit in the same
-- few hours looks like one program or one place, not a worldwide crowd of people.
WITH labelled AS (
  SELECT
    window_start, n_tx, class, mid,
    CASE
      WHEN REGEXP_CONTAINS(account, r'^[0-9a-f]{64}$') THEN 'implicit'
      WHEN REGEXP_CONTAINS(account, r'^0x[0-9a-f]{40}$') THEN 'eth_implicit'
      WHEN ENDS_WITH(account, '.tg') THEN 'telegram'
      WHEN ENDS_WITH(account, '.kaiching') THEN 'kaiching'
      WHEN ENDS_WITH(account, '.sweat') THEN 'sweat'
      WHEN REGEXP_CONTAINS(account, r'^[^.]+\.near$') THEN 'near_name'
      WHEN ENDS_WITH(account, '.near') THEN 'near_subaccount'
      ELSE 'other'
    END AS family
  FROM `your-project.proof_of_sleep.account_windows`
  WHERE account != '_light_'
)
SELECT
  window_start, family, class,
  IF(class = 'sleeper', CAST(FLOOR(mid) AS INT64), -1) AS mid_hour_utc,
  COUNT(*) AS accounts,
  SUM(n_tx) AS txs
FROM labelled
GROUP BY 1, 2, 3, 4
ORDER BY 1, 2, 3, 4

Long lists are shortened here. python -m pipeline.pull_bigquery --print 10 prints any step in full, ready to paste. Steps other than 10 and 15 read the table built by step 00.

sql/09_apps.sql

-- What do people on NEAR use? For a set of sample windows: per contract, how many
-- sleepers and how many machines call it, and how many transactions each sends.
WITH tx AS (
  SELECT
    DATE_TRUNC(block_date, MONTH) AS window_start,
    signer_account_id AS account,
    receiver_account_id AS contract,
    COUNT(*) AS n
  FROM `bigquery-public-data.crypto_near_mainnet_us.transactions`
  WHERE block_date BETWEEN ... -- sample windows, see pipeline/config.py
  GROUP BY 1, 2, 3
),
joined AS (
  SELECT t.window_start, t.contract, t.account, t.n, COALESCE(a.class, 'light') AS class
  FROM tx AS t
  LEFT JOIN `your-project.proof_of_sleep.account_windows` AS a
    ON a.account = t.account AND a.window_start = t.window_start
),
per_contract AS (
  SELECT
    window_start, contract,
    COUNT(*) AS signers,
    SUM(n) AS txs,
    COUNTIF(class = 'sleeper') AS sleepers,
    SUM(IF(class = 'sleeper', n, 0)) AS sleeper_txs,
    COUNTIF(class = 'machine') AS machines,
    SUM(IF(class = 'machine', n, 0)) AS machine_txs
  FROM joined
  GROUP BY 1, 2
),
ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY window_start ORDER BY sleepers DESC, contract) AS by_sleepers,
    ROW_NUMBER() OVER (PARTITION BY window_start ORDER BY txs DESC, contract) AS by_txs
  FROM per_contract
)
SELECT window_start, contract, signers, txs, sleepers, sleeper_txs, machines, machine_txs, by_sleepers, by_txs
FROM ranked
WHERE (by_sleepers <= 40 OR by_txs <= 40)
  AND NOT REGEXP_CONTAINS(contract, r'^[^.]+\.tg$')   -- a bare .tg name is a person's account, not an app
ORDER BY window_start, by_sleepers

Long lists are shortened here. python -m pipeline.pull_bigquery --print 10 prints any step in full, ready to paste. Steps other than 10 and 15 read the table built by step 00.

sql/05_retention.sql

-- Do accounts come back? For every window and class: of the accounts in that class,
-- how many are seen again (on at least 3 days) 1, 3, 6 and 12 months later.
WITH a AS (
  SELECT account, window_start, class
  FROM `your-project.proof_of_sleep.account_windows`
  WHERE account != '_light_'
),
later AS (
  SELECT account, window_start AS later_start, class AS later_class
  FROM `your-project.proof_of_sleep.account_windows`
  WHERE account != '_light_'
)
SELECT
  a.window_start,
  a.class,
  m AS months_later,
  COUNT(DISTINCT a.account) AS cohort,
  COUNT(DISTINCT later.account) AS seen_again,
  COUNT(DISTINCT IF(later.later_class = 'sleeper', later.account, NULL)) AS seen_again_sleeping
FROM a
CROSS JOIN UNNEST([1, 3, 6, 12]) AS m
LEFT JOIN later
  ON later.account = a.account
  AND later.later_start = DATE_ADD(a.window_start, INTERVAL m MONTH)
WHERE DATE_ADD(a.window_start, INTERVAL m MONTH) <= DATE '2026-10-01'
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3

Long lists are shortened here. python -m pipeline.pull_bigquery --print 10 prints any step in full, ready to paste. Steps other than 10 and 15 read the table built by step 00.

sql/04_timezones.sql

-- Where sleepers are on the clock: the UTC hour at the middle of each sleeper's quiet stretch.
-- Rounded down to the hour. Aggregate counts only, never individual accounts.
SELECT
  window_start,
  CAST(FLOOR(mid) AS INT64) AS mid_hour_utc,
  COUNT(*) AS sleepers
FROM `your-project.proof_of_sleep.account_windows`
WHERE class = 'sleeper'
GROUP BY 1, 2
ORDER BY 1, 2

sql/11_families.sql

-- Who are the accounts that pass the sleep test? Sleepers by account family, with the
-- UTC hour of each sleeper's quiet stretch. A family whose sleepers all sit in the same
-- few hours looks like one program or one place, not a worldwide crowd of people.
WITH labelled AS (
  SELECT
    window_start, n_tx, class, mid,
    CASE
      WHEN REGEXP_CONTAINS(account, r'^[0-9a-f]{64}$') THEN 'implicit'
      WHEN REGEXP_CONTAINS(account, r'^0x[0-9a-f]{40}$') THEN 'eth_implicit'
      WHEN ENDS_WITH(account, '.tg') THEN 'telegram'
      WHEN ENDS_WITH(account, '.kaiching') THEN 'kaiching'
      WHEN ENDS_WITH(account, '.sweat') THEN 'sweat'
      WHEN REGEXP_CONTAINS(account, r'^[^.]+\.near$') THEN 'near_name'
      WHEN ENDS_WITH(account, '.near') THEN 'near_subaccount'
      ELSE 'other'
    END AS family
  FROM `your-project.proof_of_sleep.account_windows`
  WHERE account != '_light_'
)
SELECT
  window_start, family, class,
  IF(class = 'sleeper', CAST(FLOOR(mid) AS INT64), -1) AS mid_hour_utc,
  COUNT(*) AS accounts,
  SUM(n_tx) AS txs
FROM labelled
GROUP BY 1, 2, 3, 4
ORDER BY 1, 2, 3, 4

Long lists are shortened here. python -m pipeline.pull_bigquery --print 10 prints any step in full, ready to paste. Steps other than 10 and 15 read the table built by step 00.

sql/12_relayed.sql

-- Humans who never sign. When a relayer pays gas for someone, the relayer signs the transaction
-- and the person only appears as the receiver of a DELEGATE_ACTION. This runs the same sleep test
-- on those people, for the windows in 2026-04-01, 2026-10-01.
WITH acted AS (
  SELECT
    DATE_TRUNC(block_date, MONTH) AS window_start,
    receipt_receiver_account_id AS account,
    block_date,
    EXTRACT(HOUR FROM block_timestamp_utc) AS hour,
    COUNT(*) AS n
  FROM `bigquery-public-data.crypto_near_mainnet_us.receipt_actions`
  WHERE (block_date BETWEEN ... -- see pipeline/config.py) AND action_kind = 'DELEGATE_ACTION'
  GROUP BY 1, 2, 3, 4
),
days AS (
  SELECT window_start, account, block_date, SUM(n) AS n, COUNT(*) AS n_slots, BIT_OR(1 << hour) AS day_mask
  FROM acted
  GROUP BY 1, 2, 3
),
accounts AS (
  SELECT window_start, account, SUM(n) AS n, COUNT(*) AS n_days, SUM(n_slots) AS n_slots, BIT_OR(day_mask) AS hour_mask
  FROM days
  GROUP BY 1, 2
),
hours AS (
  SELECT *, ARRAY(SELECT h FROM UNNEST(GENERATE_ARRAY(0, 23)) AS h WHERE (hour_mask >> h) & 1 = 1) AS hs
  FROM accounts
),
gaps AS (
  SELECT *,
    (SELECT AS STRUCT gap, start_h FROM (
       SELECT h AS start_h,
              COALESCE((SELECT MIN(h2) FROM UNNEST(hs) AS h2 WHERE h2 > h), hs[OFFSET(0)] + 24) - h - 1 AS gap
       FROM UNNEST(hs) AS h) ORDER BY gap DESC, start_h LIMIT 1) AS g
  FROM hours
),
classified AS (
  SELECT window_start, account, n, CASE
      WHEN n_days < 5 THEN 'too_few'
      WHEN g.gap <= 2 AND n_slots / n_days >= 8 THEN 'machine'
      WHEN ARRAY_LENGTH(hs) <= 3 THEN 'timer'
      WHEN g.gap >= 5 AND g.gap >= [99,99,99,20,17,15,13,12,11,10,9,8,7,7,6,6,5,5,4,4,3,3,99,99,99][OFFSET(ARRAY_LENGTH(hs))] AND g.gap <= 14 AND ARRAY_LENGTH(hs) >= 6 THEN 'sleeper'
      WHEN g.gap >= 5 AND g.gap >= [99,99,99,20,17,15,13,12,11,10,9,8,7,7,6,6,5,5,4,4,3,3,99,99,99][OFFSET(ARRAY_LENGTH(hs))] THEN 'routine'
      ELSE 'unclear'
    END AS relayed_class
  FROM gaps
)
SELECT
  c.window_start,
  c.relayed_class,
  COALESCE(w.class, 'no_signed_txs') AS signer_class,
  COUNT(*) AS accounts,
  SUM(c.n) AS delegate_actions
FROM classified AS c
LEFT JOIN `your-project.proof_of_sleep.account_windows` AS w
  ON w.account = c.account AND w.window_start = c.window_start
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3

Long lists are shortened here. python -m pipeline.pull_bigquery --print 10 prints any step in full, ready to paste. Steps other than 10 and 15 read the table built by step 00.

sql/01_classes.sql

-- Headline table: how many accounts fall in each class in each 14-day window,
-- and how many transactions each class sent.
SELECT
  window_start,
  class,
  SUM(accounts) AS accounts,
  SUM(n_tx) AS txs
FROM `your-project.proof_of_sleep.account_windows`
GROUP BY 1, 2
ORDER BY 1, 2

Long lists are shortened here. python -m pipeline.pull_bigquery --print 10 prints any step in full, ready to paste. Steps other than 10 and 15 read the table built by step 00.

sql/01_classes.sql

-- Headline table: how many accounts fall in each class in each 14-day window,
-- and how many transactions each class sent.
SELECT
  window_start,
  class,
  SUM(accounts) AS accounts,
  SUM(n_tx) AS txs
FROM `your-project.proof_of_sleep.account_windows`
GROUP BY 1, 2
ORDER BY 1, 2