theericstoneselect-chains-score historical 0/4+ compare copy
Updated 2024-12-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
35
36
›
⌄
-- forked from charliemarketplace / select-chains-score historical 0/4+ compare @ https://flipsidecrypto.xyz/charliemarketplace/q/NOZr-hhX7kZr/select-chains-score-historical-0-4-compare
-- forked from select-chains-score historical 0/1/4+ @ https://flipsidecrypto.xyz/studio/queries/a5b7796c-9f8e-4528-93ac-4acb095a6e87
-- forked from select-chains-score historical @ https://flipsidecrypto.xyz/studio/queries/f73bc347-2acd-49e1-ba64-d88e5db21ded
with daily_score_tiers AS (
select
chain,
score_date,
sum (case when score >= 0 then count_with_score else 0 end) as n_scored,
sum (case when score >= 4 then count_with_score else 0 end) as n_4plus
from
datascience_public_misc.score_analytics.chain_score_distribution
where chain IN (
SELECT value
FROM TABLE(SPLIT_TO_TABLE('{{Chains}}', ' '))
)
group by chain, score_date
)
select
chain,
date_trunc('week', score_date) as week_,
median(n_scored) as "# Active Addresses",
median(n_4plus) as "# Score 4+"
from daily_score_tiers
group by chain, week_
order by week_ asc