h4wkVelodrome Reward 7 redux
Updated 2022-11-13
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
›
⌄
with total_claimed as (select origin_from_address,
sum(claimed_amount_usd) as total_claim
from optimism.velodrome.ez_claimed_rewards where claimed_amount_usd is not null
group by 1
)
select
-- https://count.co/sql-resources/snowflake/histograms
CASE
WHEN total_claim <= 10 THEN 'size ≤ 10 USD'
WHEN total_claim <= 100 THEN '10 < size ≤ 100 USD'
WHEN total_claim <= 1000 THEN '100 < size ≤ 1k USD'
WHEN total_claim <= 10000 THEN '1k < size ≤ 10k USD'
WHEN total_claim <= 100000 THEN '10k < size ≤ 100k USD'
ELSE '100k USD < size' END as type,
count(distinct origin_from_address) as user_count,
floor(total_claim / 10) * 10 as bin_start,
floor(total_claim / 10) * 10 + 10 as bin_end,
concat(
cast(floor(total_claim / 10) * 10 as STRING),
' - ',
cast(floor(total_claim / 10) * 10 + 10 as STRING)
) as bin_range
from
total_claimed
group by
bin_start,
bin_end,
bin_range,
type
-- having bin_start < 10000
order by
bin_start
-- limit 250
Run a query to Download Data