This materialized view goal's is to sum up NFT collection stats based on their on-chain activity (number of total transfers/transfer last day/hour and number of owners). The view is based on the 3 following tables:
CREATE TABLE collection (
"address" varchar(42) NOT NULL,
"name" varchar NULL,
CONSTRAINT data_unique_collection UNIQUE ("address"),
PRIMARY KEY ("address")
);
CREATE TABLE nft_transfer (
"collection" varchar(42) NOT NULL,
"block_number" BIGINT NOT NULL,
"transaction_hash" varchar(66) NOT NULL,
"transaction_index" NUMERIC NULL,
"tx_to" varchar(42) NULL,
"tx_from" varchar(42) NULL,
"log_index" BIGINT NOT NULL,
"transfer_index" BIGINT NOT NULL,
"timestamp" timestamp NULL,
"to" varchar(42) NULL,
"from" varchar(42) NULL,
"amount" NUMERIC NULL,
"token_id" NUMERIC NULL,
"gas_price" NUMERIC NULL,
"created_at" TIMESTAMP DEFAULT NOW(),
"trade_currency" varchar NULL,
"trade_price" numeric NULL,
"trade_marketplace" int2 NULL,
CONSTRAINT unique_nft_transfer UNIQUE (
"transaction_hash",
"log_index",
"transfer_index"
)
);
CREATE TABLE nft (
"address" varchar(42),
"token_id" NUMERIC,
"owner" varchar NULL,
CONSTRAINT data_unique_nft UNIQUE ("address", "token_id"),
PRIMARY KEY ("address", "token_id")
);
We currently have a Materialized view that does this that is lacking optimization:
create materialized view nft_collection_stats as
SELECT
c."address",
c."name",
coalesce(T.transfers_total, 0) as transfers_total,
coalesce(H.transfers_hourly, 0) as transfers_hourly,
coalesce(H.receivers_hourly, 0) as receivers_hourly,
coalesce(H.senders_hourly, 0) as senders_hourly,
coalesce(D.transfers_daily, 0) as transfers_daily,
coalesce(D.receivers_daily, 0) as receivers_daily,
coalesce(D.senders_daily, 0) as senders_daily,
N."supply",
N."owners"
FROM
collection c
LEFT JOIN (
SELECT
collection,
SUM(1) as transfers_total
FROM
nft_transfer nt
GROUP BY
collection
) T ON T.collection = c.address
LEFT JOIN (
SELECT
collection,
SUM(1) as transfers_daily,
COUNT(distinct("to")) as receivers_daily,
COUNT(distinct("from")) as senders_daily
FROM
nft_transfer nt
where
nt."timestamp" > (CURRENT_DATE - '1 day' :: interval)
GROUP BY
collection
) D ON D.collection = c.address
LEFT JOIN (
SELECT
collection,
SUM(1) as transfers_hourly,
COUNT(distinct("to")) as receivers_hourly,
COUNT(distinct("from")) as senders_hourly
FROM
nft_transfer nt
where
nt."timestamp" > (CURRENT_DATE - '1 hour' :: interval)
GROUP BY
collection
) H ON H.collection = c.address
LEFT JOIN (
SELECT
address,
SUM(1) as supply,
COUNT(distinct("owner")) as owners
FROM
nft
GROUP BY
address
) N ON N.address = c.address
where
c."type" = 721
or c."type" = 1155 CREATE UNIQUE INDEX nft_collection_stats_idx ON nft_collection_stats (address);
We are looking for someone able to rewrite this query in a more optimized way first. A second step would be to add information about trade/price as you can see that the nft_transfer
table contains trade_price
so we could compute the floor price during the last and current hour/day.
Comment here if you're interested in this issue and how you'd start.