theericstoneUntitled Query
Updated 2021-12-23
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 all_nft as(
SELECT
date_trunc('day',block_timestamp) as day,
count(msg_value) as num_sales,
sum(msg_value:execute_msg:execute_order:order:order:taker_asset:amount / POW(10,6)) as all_sales_LUNA
FROM terra.msgs
WHERE tx_status = 'SUCCEEDED'
AND msg_value:contract::string = 'terra1eek0ymmhyzja60830xhzm7k7jkrk99a60q2z2t'
AND (msg_value:execute_msg:execute_order is not null or msg_value:execute_msg:ledger_proxy:msg:execute_order is not null)
and day >= '2021-10-01'
GROUP BY 1 ORDER BY 1),
REgalactic as (
SELECT
date_trunc('day',block_timestamp) as reday,
count(msg_value:execute_msg:execute_order:order:order:maker_asset:info:nft:contract_addr) as txs_of_GalacticPunks,
sum(msg_value:execute_msg:execute_order:order:order:taker_asset:amount / POW(10,6)) as GP_sales_LUNA
FROM terra.msgs
WHERE tx_status = 'SUCCEEDED'
AND msg_value:contract::string = 'terra1eek0ymmhyzja60830xhzm7k7jkrk99a60q2z2t'
AND msg_value:execute_msg:execute_order:order:order:maker_asset:info:nft:contract_addr = 'terra103z9cnqm8psy0nyxqtugg6m7xnwvlkqdzm4s4k'
AND msg_value:execute_msg:execute_order:order:order:maker_asset:info:nft:contract_addr is not NULL
and reday >= '2021-10-01'
GROUP BY 1 ORDER BY 1 )
SELECT
day,
num_sales,
all_sales_LUNA,
txs_of_GalacticPunks,
GP_sales_LUNA
FROM all_nft an LEFT JOIN REgalactic re ON an.day = re.reday
ORDER BY 1
-- nvl(txs_of_GalacticPunks,0) + nvl(txs_of_GalacticPunkss,0 ) as total_txs, nvl(GP_sales_LUNA,0) + nvl(GP_sales_LUNAA,0) as total_sales_GP*/
Run a query to Download Data