verynifty / musedao-contribution Goto Github PK
View Code? Open in Web Editor NEWA list of issues open for contributions. Build Together!
A list of issues open for contributions. Build Together!
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.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.