WITH deployed_contracts AS (
  SELECT
    contract_address
  FROM
    transaction
  WHERE
    block_number >= {{block_number}} AND contract_address IS NOT NULL
)
SELECT
  dc.contract_address, SUM(t.gas_used) AS total_gas_used
FROM
  transaction as t
JOIN
  deployed_contracts as dc ON t.to = dc.contract_address
WHERE
  block_number >= {{block_number}}
GROUP BY
  dc.contract_address
ORDER BY
  total_gas_used DESC
LIMIT 50

WITH period1_mints AS (
  SELECT
    contract_address,
    COUNT(*) AS mints
  FROM erc721_transfer
  WHERE "from" = '\x0000000000000000000000000000000000000000'
    AND block_number BETWEEN {{block}} - 4000 AND {{block}} - 2000
  GROUP BY contract_address
),
period2_mints AS (
  SELECT
    contract_address,
    COUNT(*) AS mints
  FROM erc721_transfer
  WHERE "from" = '\x0000000000000000000000000000000000000000'
    AND block_number BETWEEN {{block}} - 2000 AND {{block}}
  GROUP BY contract_address
)
SELECT
    p1.contract_address,
    p1.mints AS period1_mints,
    p2.mints AS period2_mints,
    p2.mints - p1.mints AS absolute_change,
    (p2.mints::numeric / p1.mints - 1) * 100 AS relative_change_percentage
FROM period1_mints p1
JOIN period2_mints p2 ON p1.contract_address = p2.contract_address
ORDER BY relative_change_percentage DESC

-- QUERY ALL GEMESIS OWNERS
WITH gemesis_owners AS (
  SELECT DISTINCT ON (token_id)
    "token_id",
    "to" AS owner
  FROM erc721_transfer
  WHERE contract_address = '\xbe9371326F91345777b04394448c23E2BFEaa826'
  ORDER BY token_id ASC, block_number DESC, log_index DESC
)
-- COUNT HOW MANY EVERY OWNER OWNS
SELECT
    count("owner") AS number_of_tokens_owned,
    "owner"
FROM gemesis_owners
GROUP BY "owner"
ORDER BY number_of_tokens_owned DESC;

-- FIND ALL GEMESIS OWNERS
WITH gemesis_owners AS (
  SELECT DISTINCT ON (token_id)
    "token_id",
    "to" AS owner
  FROM erc721_transfer
  WHERE contract_address = '\xbe9371326F91345777b04394448c23E2BFEaa826'
  ORDER BY token_id ASC, block_number DESC, log_index DESC
),
-- GROUP THEM BY OWNER, COUNTING HOW MUCH THEY OWN
gemesis_unique_owners AS (
  SELECT
    COUNT("owner") AS number_of_tokens_owned,
    "owner"
  FROM gemesis_owners
  GROUP BY "owner"
),
-- BUILD A CUMULATIVE DISTRIBUTION
owners_with_cumulative_tokens AS (
  SELECT
    "owner",
    number_of_tokens_owned,
    SUM(number_of_tokens_owned) OVER (ORDER BY number_of_tokens_owned DESC) AS cumulative_tokens
  FROM gemesis_unique_owners
)
-- SELECT EVERY 10k SEGMENT
SELECT
  CEIL(cumulative_tokens / 10000.0)::bigint * 10000 AS segment,
  COUNT(owner) AS owner_count
FROM owners_with_cumulative_tokens
GROUP BY CEIL(cumulative_tokens / 10000.0)::bigint
ORDER BY segment ASC;

-- QUERY ALL GEMESIS OWNERS
WITH gemesis_owners AS (
  SELECT DISTINCT ON (token_id)
    "token_id",
    "to" AS owner
  FROM erc721_transfer
  WHERE contract_address = '\xbe9371326F91345777b04394448c23E2BFEaa826'
  ORDER BY token_id ASC, block_number DESC, log_index DESC
),
-- FIND THE TOP 500 OWNERS
top_owners AS (
  SELECT
    COUNT("owner") AS number_of_tokens_owned,
    "owner"
  FROM gemesis_owners
  GROUP BY "owner"
  ORDER BY number_of_tokens_owned DESC LIMIT 500
)
-- FIND OUT TO WHICH CONTRACTS THEY TRANSACTED
SELECT
  txs.to as contract,
  count(txs.to) as transactions_made
FROM top_owners
INNER JOIN transaction AS txs
  ON txs.block_number > 16975847 AND txs.from = top_owners.owner
GROUP BY txs.to
ORDER BY transactions_made DESC LIMIT 5;