USER_FREQUENCY | UNIQUE_USERS | TOTAL_TRANSACTIONS | AVG_TRANSACTIONS_PER_USER | TOTAL_DEPOSIT_AMOUNT | AVG_DEPOSIT_AMOUNT_PER_USER | |
|---|---|---|---|---|---|---|
1 | One-time | 5555 | 5782 | 1.040864 | 188027.559683775 | 33.848345578 |
2 | Two-time | 365 | 831 | 2.276712 | 51366.426628485 | 140.729935968 |
3 | 3-5 times | 125 | 563 | 4.504 | 109921.519983215 | 879.372159866 |
4 | More than 5 times | 30 | 714 | 23.8 | 20730.838602759 | 691.027953425 |
gigiokobamsol users
Updated 2025-03-02
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 user_activity AS (
SELECT
provider_address,
DATE_TRUNC('day', block_timestamp) as activity_date,
COUNT(*) as daily_actions,
SUM(deposit_amount) as daily_deposit_amount
FROM solana.marinade.ez_liquid_staking_actions
WHERE block_timestamp >= DATEADD(day, -30, CURRENT_DATE)
AND action_type IN ('deposit','depositstakeaccount')
GROUP BY 1, 2
),
user_metrics AS (
SELECT
provider_address,
COUNT(DISTINCT activity_date) as days_active,
SUM(daily_actions) as total_actions,
SUM(daily_deposit_amount) as total_deposit_amount
FROM user_activity
GROUP BY 1
)
SELECT
CASE
WHEN days_active = 1 THEN 'One-time'
WHEN days_active = 2 THEN 'Two-time'
WHEN days_active >= 3 AND days_active <= 5 THEN '3-5 times'
ELSE 'More than 5 times'
END as user_frequency,
COUNT(DISTINCT provider_address) as unique_users,
SUM(total_actions) as total_transactions,
AVG(total_actions) as avg_transactions_per_user,
SUM(total_deposit_amount) as total_deposit_amount,
AVG(total_deposit_amount) as avg_deposit_amount_per_user
FROM user_metrics
GROUP BY 1
ORDER BY
Last run: 13 days ago
4
246B
1s