jackguycheet val 2
Updated 2022-12-10
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
›
⌄
with tab1 as (
select
parse_json(
'{"vales":[{"identity":"GQc311RbYBC87Xgx3swcbUjg6oqJ9e1tz2QkY55UxEMA","from":0,"to":99,"epoch":378,"epoch_slot":417454},{"identity":"jbg9bvhhDVDNDsnBihFjKLTK1zfgzXRgZcWvf9Mu33D","from":10,"to":95,"epoch":378,"epoch_slot":417980},{"identity":"EFmzi26rQh48CT2ibnSa9GQ8LgAiveb7K3kNdfLoDTZp","from":10,"to":95,"epoch":378,"epoch_slot":417980},{"identity":"DLiBQUytXnuXTuhhN8Mkom7rHbA9L49yiLchVBr2qDUp","from":10,"to":95,"epoch":378,"epoch_slot":417980},{"identity":"J13KqqnF1vuKXR91gw7zm52aW6p8FEPhFWW8dLcVrsQn","from":10,"to":95,"epoch":378,"epoch_slot":418118},{"identity":"AbbiKTxf264yE98gDrkn6C7rUztmTYASUfpB5NcCEsRq","from":10,"to":99,"epoch":378,"epoch_slot":421550},{"identity":"8wNZxetjMdSQMt121xH7NNntQd8nBVi9MMMJtM8BcSJ9","from":10,"to":99,"epoch":378,"epoch_slot":421550},{"identity":"31Juo681u4knpRW2Yzrm3mcGFk3E2D5uqufe6oyLJfn9","from":10,"to":99,"epoch":378,"epoch_slot":422356},{"identity":"9E9UqyuqHTXqz2WbwgJescaxYXaidAR3gNEQsepdDzjg","from":7,"to":97,"epoch":378,"epoch_slot":423223},{"identity":"5PhmeTciR5gbbknyzhBJsN3Rdkge1piTQvRdYzSfXs23","from":9,"to":99,"epoch":378,"epoch_slot":424386},{"identity":"DAJ2cGaR9SunRkRDwCzueaQ2imKanBDxXzVD7GPFd2M","from":10,"to":98,"epoch":378,"epoch_slot":425838},{"identity":"77fHHpiSzS9vaPfi4DxEBgCQWGjsoH9dWTFb5aud4RMg","from":10,"to":99,"epoch":378,"epoch_slot":426660},{"identity":"7H1ELp9kBTimprsKyJeCMLKtMdp4vR45qNsd9tTWz5dV","from":10,"to":99,"epoch":378,"epoch_slot":427037},{"identity":"F26NYjNDpKEbSYp8rdkXvKNUgom5HCfqdbdvCkyg7iKB","from":10,"to":99,"epoch":378,"epoch_slot":427715},{"identity":"HMfs2d6kkMgoKGzdqyiMggCFEUBUxuT8y4X67aPq1ywv","from":10,"to":99,"epoch":378,"epoch_slot":427715},{"identity":"z3GSbCbeJnXynZNC5NwoYPveC3pfubCGi5iw4yhNhkf","from":0,"to":1,"epoch":378,"epoch_slot":430745},{"identity":"GPV1cu3RbVg13FbD1sXZikAZrGtAtB5vt2uA4Bv1Uczn","from":10,"to":100,"epoch":378,"epoch_slot":431576},{"identity":"BxC5LKFwpMEr32gNEFAvtmk7Xw4RJwJV4GdnMARp7A8W","from":10,"to":100,"epoch":378,"epoch_slot":431576},{"identity":"2TK3RFC5yeWe42pqG495kusEx62y6UdYPzC8BbvaTE9z","from":10,"to":100,"epoch":378,"epoch_slot":431576},{"identity":"z3GSbCbeJnXynZNC5NwoYPveC3pfubCGi5iw4yhNhkf","from":1,"to":100,"epoch":37![]()
![]()
), tab2 as (
SELECT
VALUE as val
from tab1, table(flatten(data1:vales)) f
), tab3 as (
SELECT
val['identity'] as address,
val['to'] - val['from'] as fee_percent_change
FROM tab2 LEFT outer JOIN solana.core.fact_blocks
ON block_id = (val['epoch'] * 432000) + val['epoch_slot']
)
SELECT
address,
count(case WHEN fee_percent_change > 80 THEN 1 END) as over_80_percent_increase,
count(case WHEN fee_percent_change < -80 THEN 1 END) as over_80_precent_decrease
FROM tab3
--WHERE fee_percent_change > 80
GROUP BY 1
ORDER by 2 DESC
--GROUP BY 1,2