mamad-5XN3k3Untitled Query
Updated 2022-10-25
999
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 flowpricet as (
select TIMESTAMP::date as date, avg(price_usd) as flowprice
from flow.core.fact_prices
where symbol ='FLOW' and source ='coinmarketcap'
group by 1),
base as (
SELECT
*
FROM near.core.fact_transactions
WHERE 1=1
AND (tx_receiver LIKE '%paras%')
),
extracts as (
SELECT
tx_hash, block_timestamp,
--logs,
replace(logs[0], '\\') as logs_pre,
CASE WHEN logs_pre like 'EVENT_JSON:%' THEN replace( replace(split(logs_pre,'EVENT_JSON:')[1], '\:\"\{\"', '\:\{\"'), '\"}\"\}', '\"}\}') ELSE logs_pre END as logs_cleaned,
check_json(logs_cleaned) as checks,
TRY_PARSE_JSON(logs_cleaned) as log_json
FROM near.core.fact_receipts
WHERE 1=1
AND tx_hash IN (SELECT tx_hash from base)
AND ARRAY_SIZE(logs)>0
),
types as (
SELECT
e.tx_hash,
e.block_timestamp,
tx_signer,
tx_receiver,
coalesce(log_json:type, log_json:event) as type,
Run a query to Download Data