Most of NEAR’s active accounts are one Telegram game.

-- Monthly headline signers vs Net Demand.
-- Net Demand = distinct signers who transacted with at least one receiver
-- whose account id does NOT contain 'hot.tg' (the HOT Telegram game,
-- including game.hot.tg). A signer who used both HOT and something else
-- still counts in net_demand; a HOT-only signer does not.
-- Columns read: block_date, signer_account_id, receiver_account_id.
-- Partition filter: block_date BETWEEN DATE '2025-11-01' AND
--   DATE_SUB(CURRENT_DATE('UTC'), INTERVAL 1 DAY).
-- End bound is the last fully-closed UTC day. Never include today.

SELECT
  FORMAT_DATE('%Y-%m', block_date) AS mo,
  COUNT(DISTINCT signer_account_id) AS all_signers,
  COUNT(DISTINCT IF(receiver_account_id NOT LIKE '%hot.tg',
                    signer_account_id, NULL)) AS net_demand
FROM `bigquery-public-data.crypto_near_mainnet_us.transactions`
WHERE block_date BETWEEN DATE '2025-11-01'
  AND DATE_SUB(CURRENT_DATE('UTC'), INTERVAL 1 DAY)
GROUP BY 1
ORDER BY 1;

-- Top 20 receiver contracts per day by distinct signers.
-- Generalizes the verified one-day attribution query across the extract
-- window so burst days can be attributed offline after classification,
-- with no second BigQuery round-trip.
-- top_receiver_share is computed in classify/ as
--   (signers of the day's #1 receiver) / (that day's distinct signers).
-- Columns read: block_date, receiver_account_id, signer_account_id.
-- Partition filter: block_date BETWEEN DATE '2025-10-04' AND
--   DATE_SUB(CURRENT_DATE('UTC'), INTERVAL 1 DAY).
-- Window matches daily_activity.sql. End bound is the last fully-closed
-- UTC day. Never include today.

WITH per_receiver AS (
  SELECT
    block_date,
    receiver_account_id,
    COUNT(DISTINCT signer_account_id) AS signers
  FROM `bigquery-public-data.crypto_near_mainnet_us.transactions`
  WHERE block_date BETWEEN DATE '2025-10-04'
    AND DATE_SUB(CURRENT_DATE('UTC'), INTERVAL 1 DAY)
  GROUP BY 1, 2
),
ranked AS (
  SELECT
    block_date,
    receiver_account_id,
    signers,
    ROW_NUMBER() OVER (
      PARTITION BY block_date
      ORDER BY signers DESC, receiver_account_id
    ) AS rn
  FROM per_receiver
)
SELECT
  block_date,
  receiver_account_id,
  signers
FROM ranked
WHERE rn <= 20
ORDER BY block_date, signers DESC;