yasmin-n-d-r-hhazf
Updated 2023-09-10
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 rate AS (
SELECT
RECORDED_HOUR::date AS date,
AVG(close) AS rate
FROM
flow.core.fact_hourly_prices
WHERE
token = 'Flow'
GROUP BY
1
)
SELECT
-- DATE_TRUNC('day', block_timestamp) AS date,
COUNT(*) AS transaction_count,
COUNT(DISTINCT event_data:buyer::string) AS unique_buyers,
SUM(event_data:salePrice::float * r.rate) AS total_sale_price_usd,
SUM(event_data:commissionAmount::float * r.rate) AS total_commission_usd
FROM
FLOW.CORE.FACT_EVENTS e
JOIN
rate r
ON
DATE_TRUNC('day', block_timestamp) = r.date
WHERE
EVENT_CONTRACT IN ('A.3cdbb3d569211ff3.NFTStorefrontV2', 'A.b8ea91944fd51c43.OffersV2' )
AND EVENT_TYPE IN ('ListingCompleted', 'OfferCompleted')
AND TX_SUCCEEDED = TRUE
AND EVENT_DATA:purchased::string = 'true'
-----------------
Run a query to Download Data