CryptoIcicleAlgo-Planetwatch Rewards - Daily Metrics
Updated 2022-01-27
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
›
⌄
-- PlanetWatch is an environmental monitoring company that pays users in Planets to carry air
--quality sensors
--(Tokens: https://support.planetwatch.io/hc/en-gb/articles/4407510456721-Daily-rewards-for-the-Type-4-sensor).
-- How many wallets have been rewarded for participating in the PlanetWatch Air monitoring program?
-- Show us how the number of wallets being rewarded daily changes over time.
-- Show us how the number of daily rewards has changed over time
-- What day were the most rewards issued? Was there an event correlated with this?
-- Who are the top 10 earners of Planets?
-- Rewards Wallet Address: ZW3ISEHZUHPO7OZGMKLKIIMKVICOUDRCERI454I3DB2BH52HGLSO67W754
-- Asset ID: 27165954
-- Payout 38.46 ALGO
-- Grand Prize 115.38 ALGO
-- Difficulty Beginner
with reward_txns as (
select
b.block_timestamp,
tx_message:txn:arcv as wallet,
tx_message:txn:aamt/pow(10,6) as reward_amount
from algorand.transactions t
join algorand.block b on t.block_id = b.block_id
where sender ='ZW3ISEHZUHPO7OZGMKLKIIMKVICOUDRCERI454I3DB2BH52HGLSO67W754'
and asset_id = '27165954'
and reward_amount > 0
)
select
date_trunc('day',block_timestamp) as date,
count(distinct wallet) as n_wallet_daily,
(select count(distinct wallet) from reward_txns) as n_wallets_total,
sum(reward_amount) as reward_amount_daily
from reward_txns
group by date
Run a query to Download Data