John_GaltLimit Order Status - perfect!
Updated 2022-07-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
30
31
32
33
34
35
36
›
⌄
with order_check as (select
block_timestamp as date,
event_attributes:order_id as order_id,
event_attributes:"0_action" as token_action,
event_attributes:action as coin_action,
case
when token_action = 'execute_order' or coin_action = 'execute_order' then 'dead' ---can remove, for searching
when token_action = 'cancel_order' or coin_action = 'cancel_order' then 'dead' ---can remove
else 'live'
end as status,
ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY date desc) as rank,
SPLIT_PART(event_attributes:offer_asset, ':', 2) as offer_asset,
SPLIT_PART(event_attributes:ask_asset, ':', 2) as ask_asset,
tx_id
from terra.msg_events
where date(block_timestamp) > '2022-04-23'
and (event_attributes:"0_contract_address" = 'terra1zctyc83qmcunc5zww7hzgzmrtxhjhsj4kfgvg6' or
event_attributes:contract_address = 'terra1zctyc83qmcunc5zww7hzgzmrtxhjhsj4kfgvg6')
and event_index = 3
order by order_id
),
order_check_final_live as (select order_id, status, offer_asset, ask_asset, tx_id ---can remove this - just for searching
from order_check
where (rank = 1 and status = 'live') ----this can be removed to see status of all past and present orders
),
current_live1 as (select date(date) as day,
case
when status = 'live' then 'one_created'
end as created,
case
when status = 'dead' then 'one_removed'
end as removed
from order_check
),
Run a query to Download Data