kirastudioExploring unknowns copy
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
›
⌄
-- CTE for BNB Prices
WITH bnb_prices AS (
SELECT
date_trunc('hour', HOUR) AS date,
AVG(price) AS bnb_price
FROM
crosschain.core.ez_hourly_prices
WHERE
symbol = 'BNB'
AND hour BETWEEN '{{from_date}}' AND '{{to_date}}'
GROUP BY
1
),
-- CTE to classify transactions into different categories
transaction_classification AS (
SELECT
ft.tx_hash,
CASE
WHEN t1.tx_hash IS NOT NULL THEN 'unknown token transaction'
WHEN t2.tx_hash IS NOT NULL THEN 'unknown native transfer'
WHEN t3.tx_hash IS NOT NULL OR t4.tx_hash IS NOT NULL THEN 'unknown nft transaction'
ELSE 'unknown dapp transaction'
END AS category
FROM
bsc.core.fact_transactions AS ft
-- Joining tables to classify transaction types
LEFT JOIN bsc.core.fact_token_transfers AS t1 ON ft.tx_hash = t1.tx_hash
LEFT JOIN bsc.core.ez_bnb_transfers AS t2 ON ft.tx_hash = t2.tx_hash
LEFT JOIN bsc.nft.ez_nft_transfers AS t3 ON ft.tx_hash = t3.tx_hash
LEFT JOIN bsc.nft.ez_nft_sales AS t4 ON ft.tx_hash = t4.tx_hash
WHERE
ft.block_timestamp BETWEEN '{{from_date}}' AND '{{to_date}}'
),
-- CTE for Transaction Analysis
Run a query to Download Data