niloxx
Updated 2023-06-07
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
›
⌄
SELECT DATE_TRUNC('month',BLOCK_TIMESTAMP) AS DATE,
COUNT (DISTINCT TX_ID) AS TXN,
SUM (CASE WHEN CURRENCY = 'A.1654653399040a61.FlowToken' THEN PRICE * FLOW_USD ELSE PRICE END ) AS USD,
CASE
WHEN (CASE WHEN CURRENCY = 'A.1654653399040a61.FlowToken' THEN PRICE * FLOW_USD ELSE PRICE END ) < 50 THEN 'Less than 50 USD'
WHEN (CASE WHEN CURRENCY = 'A.1654653399040a61.FlowToken' THEN PRICE * FLOW_USD ELSE PRICE END ) BETWEEN 50 and 100 THEN 'Between 50 and 100 USD'
WHEN (CASE WHEN CURRENCY = 'A.1654653399040a61.FlowToken' THEN PRICE * FLOW_USD ELSE PRICE END ) BETWEEN 100 and 200 THEN 'Between 100 and 200 USD'
WHEN (CASE WHEN CURRENCY = 'A.1654653399040a61.FlowToken' THEN PRICE * FLOW_USD ELSE PRICE END ) BETWEEN 200 and 500 THEN 'Between 200 and 500 USD'
WHEN (CASE WHEN CURRENCY = 'A.1654653399040a61.FlowToken' THEN PRICE * FLOW_USD ELSE PRICE END ) BETWEEN 500 and 1000 THEN 'Between 500 and 1K USD'
ELSE 'More than 1K USD' end as type,
sum (TXN) over (partition by type order by date ) as cum_TXN ,
sum (USD) over (partition by type order by date ) as cum_USD
FROM flow.core.ez_nft_sales SALES
LEFT JOIN (
SELECT
DATE_TRUNC('day', RECORDED_HOUR) AS TIMEF,
AVG(CLOSE) AS FLOW_USD
FROM
flow.core.fact_hourly_prices
WHERE
TOKEN = 'Flow' GROUP BY 1) FLOW ON TIMEF = DATE_TRUNC('day',BLOCK_TIMESTAMP)
WHERE NFT_COLLECTION ='A.e3ad6030cbaff1c2.DimensionX'
AND TX_SUCCEEDED = 'TRUE'
and PRICE > 0
GROUP BY 1,type
Run a query to Download Data