SQL Friday – April 7th

Earlier this Week we launched our biggest feature yet, Basement SQL. Every Friday we will take a look at another query.

This week @opensea launched their Pro platform, for which they dropped the Gemesis NFT. Let’s take a look at who accumulated this NFT.

We use the owners example from our documentation as a view in the query. On this view we count how many tokens every address owns.

-- 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;

This query runs in under a second, and 47,215 rows are returned. This tells us 47,215 addresses own the 83,872 tokens that have been minted 🤯.

We see some addresses accumulating a lot of tokens 🧹

number_of_tokens_owned	owner
1030	b3aa9923489bc2bfec323bf05346acd4afbc92a0
891	087ec385176f3c0e68d41b3e2f7fc72f0a6d1c2a
707	87180ec01780ef48f9a581ee22ea8651357a464a
660	29fee42319a41775679a244c4804784eb133e79d
611	5a14d368aa7d1aecbc437822cb021bc26b28fd7b
556	a8c9362e0218e6099ad692975722fe6be69719ba
507	98f6d93ba4f14517d3655f4481118182200d1d59
500	c58d63d59ad68930c9fdff6f1ac479c5c9941ef4
490	7d21d6f8e03746bef7fb64710c1569505f76dc18
398	a43435ff9f2ed85b63c53619ce0478f9fb0d0de0

Let’s find out how badly people have been sweeping 🧹 We can calculate the distribution of owners by writing some more complex SQL.

-- 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;

segment	owner_count
10000	25
20000	132
30000	529
40000	1705
50000	1878
90000	42976

157 addresses own 20k of the tokens. That’s a little less than 25% of the supply 🤯

But are the whales convinced? Are they on Blur, or back to the OG, OpenSea?

Let’s search the most used contracts among the top 500 owners. We use our previous query, find the top owners and any of the transactions they made. Then we group these and count them

-- 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;

Opensea is still king in number of txs! 4323 transactions went to Seaport since the Gemesis mint, whereas 2734 were made to Blur’s Marketplace contracts.

transactions_made	contract
4323	00000000000001ad428e4906ae43d8f9852d0dd6 (Seaport 1.4)
1791	000000000000ad05ccc4f10045630fb830b95127 (Blur)
943	39da41747a83aee658334415666f3ef92dd0d541 (Blur)
674	0000000000a39bb272e79075ade125fd351887ac (Blur Pool)
371	be9371326f91345777b04394448c23e2bfeaa826

These queries run super fast, and can be turned into production-grade endpoints with a single click.

Wanna try them yourself?

➡️ Create a Basement account and start building https://dashboard.basement.dev/auth/signup

See you next SQL Friday folks ✌️