DiamondEvaluate Trades : Dapper Sports NBATS
Updated 2023-11-04
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
›
⌄
-- forked from Evaluate Trades : Dapper Sports NFLAD @ https://flipsidecrypto.xyz/edit/queries/1035bf17-a2a1-4022-ab1d-fc6b71eff426
SELECT
CONVERT_TIMEZONE('America/Los_Angeles', to_date(BLOCK_TIMESTAMP)) as "Date",
COUNT(NFT_ID) as "Trades",
SUBSTRING(NFT_TYPE,
POSITION('.' IN SUBSTR(NFT_TYPE, POSITION('.' IN NFT_TYPE) + 1)) + 1 + POSITION('.' IN NFT_TYPE),
LENGTH(NFT_TYPE) - POSITION('.' IN NFT_TYPE) - POSITION('.' IN SUBSTR(NFT_TYPE, POSITION('.' IN NFT_TYPE) + 1))) as "Contract",
CASE
WHEN "Contract" = 'TopShot.NFT' THEN 'NBA Top Shot'
ELSE "Contract"
END as "Contract Name",
SUM("Trades") OVER (ORDER BY "Date") as "Total Traded"
FROM (
WITH swaps_raw AS (
SELECT
FE.TX_ID,
FE.BLOCK_TIMESTAMP,
FE.BLOCK_HEIGHT,
f.*,
parse_json(f.VALUE) AS payload
FROM FLOW.CORE.FACT_EVENTS FE,
table(flatten(event_data)) f
WHERE event_contract IN ('A.9066631feda9e518.Swap', 'A.15f55a75d7843780.Swap')
AND event_type = 'ProposalExecuted'
AND TX_SUCCEEDED = TRUE
AND FE.BLOCK_TIMESTAMP BETWEEN '2022-11-16' AND '2025-12-31'
),
assets_swapped AS (
SELECT
TX_ID,
CONVERT_TIMEZONE('America/Los_Angeles', BLOCK_TIMESTAMP) as BLOCK_TIMESTAMP,
BLOCK_HEIGHT,
CASE WHEN f.VALUE:name = 'leftUserOffer' THEN GET(payload, 3):value:value ELSE GET(payload, 5):value:value END AS sending_user,
f.VALUE:value:value AS assets_nested
Run a query to Download Data