SandBox Info

caution

The following tables are indexed based on the columns mentioned with each table. The queries executed using those columns perform well as compared to something that is not indexed.

If you need to do analysis on any other column, reach out to us. Contact Us

TABLES:

NFT_BY_CHAIN_ID_AND_CREATOR_AND_DATE

The NFT_BY_CHAIN_ID_AND_CREATOR_AND_DATE contains the NFT transaction data by chain Id, creatorName and date.

Indexed on: chainId,contractName,timestamp

Sample Queries

`1. Daily Transaction Count for all Creators`

Query

SELECT
    CAST(date as date) date,
    count(1) as DailyTransactions,
    creatorName
FROM
    NFT_BY_CHAIN_ID_AND_CREATOR_AND_DATE
where
    creatorName not in (null)
group by
    date,
    creatorName
order by
    date,
    DailyTransactions desc

Result

date	DailyTransactions	creatorName
2022-06-01	19010	Gary Vaynerchuk
2022-06-01	1181	HENI
2022-06-01	1120	Elftown King
2022-06-01	872	Anonymous
2022-06-01	671	Yuga Labs
2022-06-01	291	Proof Collective
2022-06-01	251	Takashi Murakami
2022-06-01	216	Prince Lail
2022-06-01	175	Otter Eth
2022-06-01	159	Timpers.eth

`2.Count of Transactions grouped by Creator Name`

Query

SELECT
    count(1) as transactions,
    creatorName
FROM
    NFT_BY_CHAIN_ID_AND_CREATOR_AND_DATE
where
    creatorName not in (null)
group by
    creatorName
order by
    transactions desc

Result

transactions	creatorName
359885	Gary Vaynerchuk
81096	Yuga Labs
22434	Takashi Murakami
16422	Proof Collective
13973	Anonymous
9662	Otter Eth
9631	Yam Karkai
8129	Augminted Labs

`3.Sum of value of contracts grouped by Creator Name`

Query

SELECT
    sum(value) as total,
    creatorName creatorName
FROM
    NFT_BY_CHAIN_ID_AND_CREATOR_AND_DATE
where
    creatorName not in (null)
group by
    creatorName
order by
    total desc

Result

total	creatorName
282128308016571108932102	Yuga Labs
25200000000000000000	Gravity Game Link
2303733036530396548	Gary Vaynerchuk
600000000000000000	Yam Karkai
460000000000000000	Aaron Chang
94000000000000000	Elftown King
91000000000000000	Otter Eth
80000000000000000	Orange Comet
17000000000000	Anonymous

DEFI_BY_CHAIN_ID_AND_CONTRACT_NAME_AND_DATE

The DEFI_BY_CHAIN_ID_AND_CONTRACT_NAME_AND_DATE contains the DeFi transaction data by chain Id, contract name and date.

Indexed on: chainId,contractName,timestamp

Sample Queries

`1. Weekly Top Bridge Protocols`

Query

select
    contractName as ContractName,
    count(*) as TransactionCount
from
    default.DEFI_BY_CHAIN_ID_AND_CONTRACT_NAME_AND_DATE
group by
    contractName
order by
    TransactionCount desc

Result

ContractName	TransactionCount
Uniswap V3 Router 2	4750616
Wrapped Ether Token	1933368
1Inch v4 Router	818813
Uniswap Labs: NonfungiblePositionManager	160199
Uniswap Labs: SwapRouter	117891
Aave Lending PoolV2	95610
Compound: COMP	37962
AAVE v2 Main Market: WETHGateway	17379
Compound: cETH Token	12721
Compound: cUSDC Token	9015

`2.Daily Top From Address Transactions by Contract`

Query

select
    date as Date,
from
    as
From,
    DailyTransactions as "Daily Transactions"
from
    (
        select
            cast(date as date) date,
        from,
            contractName,
            count(1) as DailyTransactions,
            rank() over (
                partition by date
                order by
                    date,
                    DailyTransactions desc
            ) as rnk
        from
            default.DEFI_BY_CHAIN_ID_AND_CONTRACT_NAME_AND_DATE
        group by
            contractName,
            date,
        from
        order by
            date,
            DailyTransactions desc
    ) tmp
WHERE
    tmp.rnk < 11

Result

Date	From	Daily Transactions
2022-06-01	0xe2e54270813c5c44a6f97d123120952a12b00000	254
2022-06-01	0x839d4641f97153b0ff26ab837860c479e2bd0242	215
2022-06-01	0xe2f266d1d65a42728a59131a92cee4727ab84024	191
2022-06-01	0x167003a4b849f182e8baf0b10777c99f6e1222cb	175
2022-06-01	0xa3b83d0e2f3d2c675439188ef1aa13d1c6abca16	157
2022-06-01	0x67ddb2184f3429359dca8c9f9166a5020bb1603f	132
2022-06-01	0x55961e9fc41c07c13ec0c7103b3d34c0ae32c2eb	121
2022-06-01	0xf07704777d6bc182bf2c67fbda48913169b84983	112
2022-06-01	0x8b9d1dda24014b1670c5dbc4df227387cdbb8aec	108
2022-06-01	0xc37704a457b1ee87eb657cae584a34961e86acac	101

BRIDGE_BY_CHAIN_ID_AND_CONTRACT_NAME_AND_DATE

The BRIDGE_BY_CHAIN_ID_AND_CONTRACT_NAME_AND_DATE contains the Bridge transaction data by chain Id, contract name and date.

Indexed on: chainId,contractName,timestamp

Sample Queries

`1. Weekly Top Bridge Protocols`

Query

select
    week,
    bridge.contractName as contractName,
    count(bridge.contractName) as count
from
    (
        select
            concat(
                CAST(
                    CAST(
                        YEAR(CAST(FROM_UNIXTIME(bridge.timestamp) AS DATE)) AS integer
                    ) as varchar
                ),
                '-',
                leftPad(
                    CAST(
                        CAST(
                            toWeek(CAST(FROM_UNIXTIME(bridge.timestamp) AS DATE)) AS integer
                        ) as varchar
                    ),
                    2,
                    '0'
                )
            ) as week,
            *
        from
            default.BRIDGE_BY_CHAIN_ID_AND_CONTRACT_NAME_AND_DATE as bridge
    ) as bridge
where
     bridge.contractName = 'Wormhole Solana'
group by
    week,
    bridge.contractName
order by
    week desc

Result

week	contractName	count
2022-34	Wormhole Solana	2
2022-28	Wormhole Solana	4
2022-26	Wormhole Solana	10
2022-24	Wormhole Solana	10
2022-23	Wormhole Solana	17

`2.Weekly Bridge Transaction Count`

Query

select
    concat(
        CAST(
            CAST(
                YEAR(CAST(FROM_UNIXTIME(bridge.timestamp) AS DATE)) AS integer
            ) as varchar
        ),
        '-',
        leftPad(
            CAST(
                CAST(
                    toWeek(CAST(FROM_UNIXTIME(bridge.timestamp) AS DATE)) AS integer
                ) as varchar
            ),
            2,
            '0'
        )
    ) as week,
    count(week) as count
from
    default.BRIDGE_BY_CHAIN_ID_AND_CONTRACT_NAME_AND_DATE as bridge
group by
    week

Result

week	count
2022-32	16822
2022-23	19902
2022-39	9563
2022-28	17677
2022-34	16901
2022-25	22399
2022-35	16962

TOKEN_PAIRS

The TOKEN_PAIRS table contains the data of token pairs exchanges across a defined protocol.

Indexed on: timestamp

Sample Queries

`1.Token Pairs Data`

Query

select
    _id,
    chainId,
    pairName,
    fees,
    protocolName
from
    TOKEN_PAIRS tp

Result

_id	chainId	pairName	fees	protocolName
1-0xb4e16d0168e52d35cacd2c6185b44281ec28c9dc	1	USDC-WETH	3000	Uniswap
1-0x0d4a11d5eeaac28ec3f61d100daf4d40471f1852	1	WETH-USDT	3000	Uniswap
1-0x9cbfb60a09a9a33a10312da0f39977cbdb7fde23	1	SAITAMA-WETH	3000	Uniswap
1-0x5281e311734869c64ca60ef047fd87759397efe6	1	WETH-CULT	3000	Uniswap
1-0xdc00ba87cc2d99468f7f34bc04cbf72e111a32f7	1	WETH-LOOKS	3000	Uniswap
1-0x453a43e2bf3080f7a23c9bb034ccdd869e306102	1	WETH-STRNGR	3000	Uniswap
1-0x63b61e73d3fa1fb96d51ce457cabe89fffa7a1f1	1	SHINJA-WETH	3000	Uniswap
1-0x6033368e4a402605294c91cf5c03d72bd96e7d8d	1	X2Y2-WETH	3000	Uniswap

`2.Top Token Pair Swap`

Query

select
    monthName,
    pairName,
    noOfPairs
from
    (
        select
            monthName(cast(timestamp as date)) as monthName,
            month(cast(timestamp as date)) as num,
            pairName,
            count(1) as noOfPairs,
            rank() over (
                partition by monthName
                order by
                    num,
                    noOfPairs desc
            ) as rnk
        from
            (
                select
                    dte.contractAddress as contractAddress,
                    dte.contractName as contractName,
                    dse.`timestamp` as timestamp,
                    dse.entrySignature as entrySignature,
                    dse.hash as transactionHash,
                    dse.tokenAddress as pairAddress,
                    tp.pairName as pairName,
                    dse.amount0In as amount0In,
                    dse.amount1In as amount1In,
                    dse.amount0Out as amount0Out,
                    dse.amount1Out as amount1Out,
                    tp.token0symbol as token0Symbol,
                    tp.token1symbol as token1Symbol
                from
                    default.defi_transaction_events dte,
                    DEFI_SWAP_EVENTS dse,
                    TOKEN_PAIRS tp
                where
                    dse.tokenAddress = tp.token1address
                    and dse.hash = dte.hash
            )
        group by
            monthName,
            pairName,
            num
        order by
            num,
            noOfPairs desc
    ) tmp
where
    tmp.rnk <= 5

Result

monthName	pairName	noOfPairs
June	WETH-NIL	12
July	WETH-NIL	10
August	WETH-NIL	8

TOKEN_PRICES

The TOKEN_PRICES table contains the data of token prices updated on an hourly basis.

Indexed on: timestamp

Sample Queries

`1.Top Token Price Data`

Query

select
    *
from
    TOKEN_PRICES tp
order by
    tokenPriceInUSD desc

Result

tokenName	tokenSymbol	tokenPriceInUSD	timestamp
Wrapped Gen-0 CryptoKitties	WG0	99.99653000	2022-09-18 07:40:00.000
COAL	MLB	99.99619000	2022-09-21 05:40:00.000
3X Long Bitcoin Token	BULL	99.99346000	2022-09-18 07:40:00.000
Whiteheart	WHITE	99.99285000	2022-09-19 13:40:00.000
COAL	MLB	99.99162000	2022-09-16 11:40:00.000
Wrapped Gen-0 CryptoKitties	WG0	99.98540000	2022-09-17 19:40:00.000
Wrapped Gen-0 CryptoKitties	WG0	99.98466000	2022-09-18 02:40:00.000
Opyn Squeeth	OSQTH	99.98401000	2022-09-18 03:41:00.000

SandBox Info​

TABLES:​

NFT_BY_CHAIN_ID_AND_CREATOR_AND_DATE​

1. Daily Transaction Count for all Creators​

2.Count of Transactions grouped by Creator Name​

3.Sum of value of contracts grouped by Creator Name​

DEFI_BY_CHAIN_ID_AND_CONTRACT_NAME_AND_DATE​

1. Weekly Top Bridge Protocols​

2.Daily Top From Address Transactions by Contract​

BRIDGE_BY_CHAIN_ID_AND_CONTRACT_NAME_AND_DATE​

1. Weekly Top Bridge Protocols​

2.Weekly Bridge Transaction Count​

TOKEN_PAIRS​

1.Token Pairs Data ​

2.Top Token Pair Swap​

TOKEN_PRICES​

1.Top Token Price Data ​

SandBox Info

TABLES:

NFT_BY_CHAIN_ID_AND_CREATOR_AND_DATE

`1. Daily Transaction Count for all Creators`

`2.Count of Transactions grouped by Creator Name`

`3.Sum of value of contracts grouped by Creator Name`

DEFI_BY_CHAIN_ID_AND_CONTRACT_NAME_AND_DATE

`1. Weekly Top Bridge Protocols`

`2.Daily Top From Address Transactions by Contract`

BRIDGE_BY_CHAIN_ID_AND_CONTRACT_NAME_AND_DATE

`1. Weekly Top Bridge Protocols`

`2.Weekly Bridge Transaction Count`

TOKEN_PAIRS

`1.Token Pairs Data`

`2.Top Token Pair Swap`

TOKEN_PRICES

`1.Top Token Price Data`