DATE | DAYS | N_TXS | N_USERS | N_TXS_PER_USER | TX_FEE_USD | CUM_TX_FEE_USD | AVG_TX_FEE_USD | N_TXS_CLAIMER | N_USER_CLAIMER | N_TXS_PER_CLAIMER | TX_FEE_USD_CLAIMER | CUM_TX_FEE_USD_CLAIMER | AVG_TX_FEE_USD_CLAIMER | RATE_OF_TXS | RATE_OF_USER | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | 2024-01-29 00:00:00.000 | else | 2357043 | 10358 | 227.557733 | 1221.9466987 | 846961.093124563 | 0.0005184235921 | 1378 | 547 | 2.519196 | 47.7715667 | 32726.882250795 | 0.03466731981 | 0.058463 | 5.280942 |
2 | 2024-01-28 00:00:00.000 | else | 3617408 | 16912 | 213.895932 | 2919.513189996 | 845739.146425863 | 0.0008070732386 | 2696 | 931 | 2.895811 | 114.684112339 | 32679.110684095 | 0.04253861734 | 0.074529 | 5.504967 |
3 | 2024-01-27 00:00:00.000 | else | 3652970 | 22540 | 162.066105 | 4667.170492626 | 842819.633235867 | 0.001277637236 | 4280 | 1227 | 3.488183 | 192.178595106 | 32564.426571756 | 0.04490154091 | 0.117165 | 5.443656 |
4 | 2024-01-26 00:00:00.000 | else | 3857983 | 19422 | 198.639841 | 2997.884746568 | 838152.462743241 | 0.0007770601235 | 3552 | 1158 | 3.067358 | 116.666567676 | 32372.24797665 | 0.03284531748 | 0.092069 | 5.962311 |
5 | 2024-01-25 00:00:00.000 | else | 3981660 | 32145 | 123.865609 | 4661.12086303 | 835154.577996673 | 0.001170647635 | 5694 | 1528 | 3.72644 | 213.808971652 | 32255.581408974 | 0.03754987209 | 0.143006 | 4.753461 |
6 | 2024-01-24 00:00:00.000 | else | 4117437 | 23390 | 176.034074 | 4051.122917828 | 830493.457133643 | 0.0009838943299 | 6091 | 1547 | 3.937298 | 187.435907624 | 32041.772437322 | 0.03077260017 | 0.147932 | 6.613938 |
7 | 2024-01-23 00:00:00.000 | else | 4117096 | 24895 | 165.378429 | 4071.819880992 | 826442.334215815 | 0.0009890028994 | 5349 | 1453 | 3.681349 | 146.592257148 | 31854.336529698 | 0.02740554443 | 0.129922 | 5.836513 |
8 | 2024-01-22 00:00:00.000 | else | 4086867 | 26356 | 155.064008 | 4384.93983527 | 822370.514334823 | 0.001072934313 | 4148 | 1535 | 2.70228 | 132.54703009 | 31707.74427255 | 0.03195444313 | 0.101496 | 5.824101 |
9 | 2024-01-21 00:00:00.000 | else | 4014282 | 17929 | 223.898823 | 3206.905000425 | 817985.574499553 | 0.000798873871 | 4464 | 1471 | 3.03467 | 153.9877608 | 31575.19724246 | 0.03449546613 | 0.111203 | 8.204585 |
10 | 2024-01-20 00:00:00.000 | else | 3982255 | 32854 | 121.210659 | 5028.275455482 | 814778.669499128 | 0.001262670385 | 8264 | 2728 | 3.029326 | 258.047691759 | 31421.20948166 | 0.03122551933 | 0.207521 | 8.303403 |
11 | 2024-01-19 00:00:00.000 | else | 3921301 | 18205 | 215.396924 | 3570.765484755 | 809750.394043646 | 0.000910607343 | 4018 | 1489 | 2.698455 | 139.02428715 | 31163.161789901 | 0.03460037012 | 0.102466 | 8.179072 |
12 | 2024-01-18 00:00:00.000 | else | 4011500 | 28806 | 139.259182 | 6962.66854389 | 806179.628558891 | 0.001735677064 | 4704 | 1730 | 2.719075 | 182.385879268 | 31024.137502751 | 0.03877250835 | 0.117263 | 6.005693 |
13 | 2024-01-17 00:00:00.000 | else | 4054094 | 33316 | 121.686097 | 7472.286777042 | 799216.960015001 | 0.001843145911 | 11597 | 2250 | 5.154222 | 273.93607305 | 30841.751623483 | 0.02362128766 | 0.286057 | 6.753512 |
14 | 2024-01-16 00:00:00.000 | else | 4034871 | 25682 | 157.108909 | 5662.1597824 | 791744.673237959 | 0.001403306272 | 7038 | 2131 | 3.302675 | 198.8096704 | 30567.815550433 | 0.02824803501 | 0.174429 | 8.29764 |
15 | 2024-01-15 00:00:00.000 | else | 4130702 | 39311 | 105.07751 | 7184.926783716 | 786082.513455559 | 0.00173939606 | 12514 | 3011 | 4.156094 | 265.131951336 | 30369.005880033 | 0.02118682686 | 0.302951 | 7.659434 |
16 | 2024-01-14 00:00:00.000 | else | 4046343 | 32171 | 125.776103 | 6590.640343206 | 778897.586671843 | 0.00162878934 | 10366 | 2182 | 4.750687 | 328.469292402 | 30103.873928697 | 0.03168717851 | 0.256182 | 6.782506 |
17 | 2024-01-13 00:00:00.000 | else | 4133507 | 35919 | 115.078566 | 7030.410458883 | 772306.946328637 | 0.001700834294 | 8508 | 2845 | 2.99051 | 237.706510454 | 29775.404636295 | 0.02793917612 | 0.20583 | 7.920599 |
18 | 2024-01-12 00:00:00.000 | else | 4180893 | 44113 | 94.776891 | 20631.246291492 | 765276.535869754 | 0.004934650634 | 13112 | 3222 | 4.069522 | 391.127386686 | 29537.698125841 | 0.02982972748 | 0.313617 | 7.303969 |
19 | 2024-01-11 00:00:00.000 | else | 4123632 | 30269 | 136.232845 | 10090.675785993 | 744645.289578262 | 0.002447035959 | 11624 | 2672 | 4.350299 | 343.706504162 | 29146.570739155 | 0.02956869444 | 0.281887 | 8.827513 |
20 | 2024-01-10 00:00:00.000 | else | 4241820 | 29886 | 141.933347 | 20607.806349984 | 734554.613792269 | 0.004858246307 | 9389 | 2523 | 3.721363 | 261.914505888 | 28802.864234993 | 0.02789588943 | 0.221344 | 8.44208 |
SocioCryptoairdrop claimer activity comparison
Updated 2024-01-29
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 airdrop_claimer as (
SELECT
receiver as user
FROM sei.core.fact_transfers
WHERE sender IN (
'sei1agd6xc09ydnrucxpjy9c47thunrzzhe5sc75gphjnh4nqkdup8wsexh044',
'sei1mm640rda0zmkfwyprmchk2gg54wj7mn8maqzycgzpul0pml448yswjxvy2'
)
AND block_timestamp::date >='2023-08-16'
GROUP by 1
)
,
aridroper as (
SELECT
date_trunc('d', block_timestamp) as date_claim,
count(DISTINCT tx_id) as n_txs_claimer,
count(DISTINCT tx_from) as n_user_claimer,
n_txs_claimer / n_user_claimer as n_txs_per_claimer,
sum((split(fee, 'usei')[0])/1e6) as tx_fee_sei_claimer,
avg((split(fee, 'usei')[0])/1e6) as avg_tx_fee_sei_claimer,
sum(((split(fee, 'usei')[0])/1e6) * b.sei_price) as tx_fee_usd_claimer,
avg(((split(fee, 'usei')[0])/1e6) * b.sei_price) as avg_tx_fee_usd_claimer
FROM sei.core.fact_transactions a
LEFT JOIN (SELECT TO_TIMESTAMP(value[0]::string) as date,
value[1] as sei_price
FROM (
SELECT livequery.live.udf_api(
'https://api.coingecko.com/api/v3/coins/sei-network/market_chart?vs_currency=usd&days=90&interval=daily&precision=3') as response
),LATERAL FLATTEN (input => response:data:prices)
) b ON date_trunc('day', a.block_timestamp) = b.date
WHERE date >= '2023-08-16'
AND tx_from IN (SELECT user FROM airdrop_claimer)
GROUP BY 1
),
total as
(
Last run: over 1 year agoAuto-refreshes every 24 hours
90
16KB
418s