alleriaLevana Marathon Hunters: Legendary Meteors earned and Total UST Deposited
Updated 2021-11-14
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
meteor_hour as (
SELECT
block_timestamp as transaction_time,
minute(block_timestamp) as transaction_minute,
CASE
WHEN transaction_time < TO_TIMESTAMP('2021-11-08T06:00:00Z') THEN 1
ELSE TIMEDIFF(hour, TO_TIMESTAMP('2021-11-08T06:00:00Z'), transaction_time) + 1
END as meteor_shower_hour,
tx_id,
msg_value:amount[0]:amount / POW(10,6) as UST_donated,
msg_value:from_address::string as wallet_address
FROM terra.msgs
WHERE msg_value:to_address::string = 'terra1dax9mddsycvzkc429wwy494vhzhggw3d5594rt'
AND tx_status = 'SUCCEEDED'
AND meteor_shower_hour <= 44
ORDER BY meteor_shower_hour ASC
),
per_hour_address as (
SELECT
meteor_shower_hour,
wallet_address,
SUM(UST_donated) as total_UST_donated,
COUNT(tx_id) as number_tx_done_per_shower
FROM meteor_hour
GROUP BY meteor_shower_hour, wallet_address
),
hall_of_fame as (
SELECT
wallet_address,
COUNT(meteor_shower_hour) as meteor_showers_involved,
SUM(total_UST_donated) as all_shower_UST_donated,
SUM(number_tx_done_per_shower) as total_tx_done
FROM per_hour_address
Run a query to Download Data