glass_blossomsdistinct users for different actions
Updated 2022-04-18
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 t1 as(with pp as(
WITH join_date_per_terra_user as (
SELECT
msg_value:sender::string sender_address,
DATE(block_timestamp) bid_date
-- DATEDIFF(day, MIN(DATE(block_timestamp)), CURRENT_DATE) user_age_days
FROM terra.msgs
WHERE (MSG_VALUE:contract='terra1e25zllgag7j9xsun3me4stnye2pcg66234je3u' or MSG_VALUE:contract='terra1w9ky73v4g7v98zzdqpqgf3kjmusnx4d4mvnac6')
and TX_STATUS='SUCCEEDED'
GROUP BY 1,2
ORDER BY 2
)
SELECT
bid_date,
sender_address
FROM
join_date_per_terra_user
WHERE bid_date >= '2021-03-17'
GROUP BY 1,2
ORDER BY 1
)
select
msg_value:sender::string as users,
msg_value:contract::string as program_used,
tx_id
FROM terra.msgs
where users in (select sender_address from pp))
select
CASE
WHEN program_used = 'terra1sepfj7s0aeg5967uxnfk4thzlerrsktkpelm5s' THEN 'Deposit on Anchor'
WHEN program_used = 'terra1l7xu2rl3c7qmtx3r5sd2tz25glf6jh8ul7aag7' THEN 'sell'
WHEN program_used = 'terra1jxazgm67et0ce260kvrpfv50acuushpjsz2y0p' THEN 'swap'
when program_used is null THEN 'hold'
end as participated_program,
Run a query to Download Data