DiamondFlowty Daily (Non-Offers + Offers) with prices 7day,14day,30day copy
Updated 2023-09-06
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
daily_prices AS (
SELECT
token,
date_trunc('day', recorded_hour) AS day,
AVG(close) AS price
FROM flow.core.fact_hourly_prices
WHERE recorded_hour >= '2023-01-01'
GROUP BY token, day
),
CombinedData AS (
SELECT
TO_DATE(CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', BLOCK_TIMESTAMP)) AS day,
COUNT(*) AS "Total Transactions",
COUNT(DISTINCT event_data:buyer::string) AS "Unique Buyers",
COUNT(DISTINCT event_data:seller::string) AS "Unique Sellers",
SUM(event_data:salePrice::float) AS total_sale_price,
SUM(event_data:commissionAmount::float) AS total_commission,
SUM(COUNT(*)) OVER (ORDER BY TO_DATE(CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', BLOCK_TIMESTAMP)) ASC) AS "Cumulative Transactions"
FROM
FLOW.CORE.FACT_EVENTS
WHERE
(
EVENT_CONTRACT IN ('A.3cdbb3d569211ff3.NFTStorefrontV2')
AND EVENT_TYPE = 'ListingCompleted'
AND TX_SUCCEEDED = TRUE
AND EVENT_DATA:purchased::string = 'true'
)
OR
(
EVENT_CONTRACT IN ('A.b8ea91944fd51c43.OffersV2')
AND EVENT_TYPE = 'OfferCompleted'
AND BLOCK_TIMESTAMP >= CONVERT_TIMEZONE('America/Los_Angeles', 'UTC', '2023-01-01')
AND tx_succeeded = 'true'
AND EVENT_DATA::string LIKE '%0x6590f8918060ef13%'
AND EVENT_DATA:purchased LIKE '%true%'
Run a query to Download Data