saeide-ahmadi-7Solana: sale activity in Coral Cube on Subscribe_collections
Updated 2022-11-01
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
with tab1 as(
SELECT
distinct (ADDRESS_NAME) as collection
FROM solana.core.fact_nft_sales join solana.core.dim_labels on MINT = ADDRESS
where marketplace IN ('coral cube') and SUCCEEDED = TRUE and ADDRESS_NAME is not null
),
tab2 as(
SELECT
distinct (ADDRESS_NAME) as collection
FROM solana.core.fact_nft_sales join solana.core.dim_labels on MINT = ADDRESS
where marketplace IN ('hyperspace') and SUCCEEDED = TRUE and ADDRESS_NAME is not null
) ,
Subscribe_collections as(
SELECT tab1.collection as collection
from
tab1, tab2
where
tab1.collection = tab2.collection
order by 1 asc
)
SELECT
date_trunc('day', block_timestamp) as day,
ADDRESS_NAME as collection,
count(DISTINCT tx_id) as sales,
COUNT(DISTINCT PURCHASER) as PURCHASER,
sum(sales_amount) as sales_volume,
avg(sales_amount) as avg_sales_amt
FROM solana.core.fact_nft_sales join solana.core.dim_labels on MINT = ADDRESS
where marketplace IN ('coral cube') and SUCCEEDED = TRUE and
date_trunc('day', block_timestamp) >= '2022-09-22' and ADDRESS_NAME is not null and ADDRESS_NAME in (select collection
from Subscribe_collections
)
GROUP BY day, collection
Run a query to Download Data