SQL Friday – April 14th

It’s friday! Every week we take a look at another this query.

mint.fun dropped their !funpoints pass this week, so let’s write a query to help you and your users find new and rising mints.

A mint is a transfer coming from the null address. We can easily query these on our ERC721 transfers table, and count mints by contract address.

To calculate the change over two periods, we write two separate queries in CTEs and subtract the results. We order by absolute mints, and also show the percentage change.

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

This query runs super fast, and can easily be integrated in apps such as wallets and explorers.

Snapshot taken at block 17045352

absolute_change	contract_address	period1_mints	period2_mints	relative_change_percentage
944	0x85c4209f7965d36742291f9222bd2cdb104df75c	179	1123	527.37
565	0x0000000000664ceffed39244a8312bd895470803	3867	4432	14.61
475	0x873b0ba7d721edb1cdef6b5a48efe1b6a2b925a0	240	715	197.92
434	0x5f04d47d698f79d76f85e835930170ff4c4ebdb7	10744	11178	4.04
408	0x14a89e4eb4716916f21e8bc6551bf34c78d1b2d6	60	468	680.00
189	0x61a073585e637aacde3135284054a47d42e19b50	195	384	96.92
59	0x45704edabe7d2e038c35876dd3b6789511e452cf	21	80	280.95
52	0x87182ac8551425245326295ede5ece47694ec306	14	66	371.43
48	0x6761bc096d2537b47673476b483ec1da54c8088d	67	115	71.64
43	0x57f1887a8bf19b14fc0df6fd9b2acc9af147ea85	122	165	35.25

➡️ Create a Basement account to write and deploy queries yourself https://dashboard.basement.dev/auth/signup

See you next Friday folks ✌️