Query Examples

1. Most common user paths

Returns the most common paths by message type. Will help you understand what users do after claiming rewards, delegations or other specific actions.
with transactions as (
select
tx_id,
msg_index,
block_timestamp,
message_type as action,
sender as sender,
rank() over(partition by sender order by block_timestamp asc, tx_id asc, msg_index asc) as sequence
from `numia-data.evmos.evmos_message_types_transactions`
where block_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
order by sender asc, 6 asc, msg_index asc)
select
t1.tx_id,
t1.sender,
t1.action as action_1,
t2.action as action_2,
t3.action as action_3
from transactions t1
inner join transactions t2 on t1.sender = t2.sender and t2.sequence = t1.sequence + 1 and t1.action <> t2.action
inner join transactions t3 on t1.sender = t3.sender and t3.sequence = t1.sequence + 2 and t1.action <> t2.action
order by sender, t1.sequence asc)
select
action_1 || ' -> ' || action_2 || ' -> ' || action_3 as path,
count(*) as frequency
from transactions
group by 1l
order by 2 desc

2. Monthly Retention

with active_users as (
SELECT
attribute_value as user,
date(b.block_timestamp) as activity_date
FROM `numia-data.evmos.evmos_event_attributes` ea
INNER JOIN `numia-data.evmos.evmos_blocks` b on cast(b.block_height as int64) = ea.block_height
WHERE attribute_key = 'sender'
and attribute_value like 'evmos%'),
user_cohorts as (
SELECT
user as user,
MIN(DATE_TRUNC(date(activity_date), month)) as cohortMonth
FROM active_users
GROUP BY 1),
order_month as (
SELECT
uc.user as user,
DATE_DIFF(DATE_TRUNC(date(activity_date), month), cohortMonth, month) as month_number,
count(distinct(uc.user)) as users
FROM active_users au
LEFT JOIN user_cohorts uc on uc.user = au.user
GROUP BY 1,2),
cohort_size as (
SELECT
count(distinct(user)) as users,
cohortMonth
FROM user_cohorts uc
GROUP BY 2
ORDER BY 1),
retention_table as(
SELECT
c.cohortMonth,
o.month_number,
sum(users) as users
FROM order_month o
LEFT JOIN user_cohorts c on c.user = o.user
group by 1,2)
SELECT
r.cohortMonth,
s.users as totalUsers,
r.month_number,
r.users / s.users as percentage
FROM retention_table r
LEFT JOIN cohort_size s on s.cohortMonth = r.cohortMonth
WHERE r.cohortMonth IS NOT NULL
ORDER BY 1, 3;sq

3. Active Users

select
date(block_timestamp) as day,
message_type as action,
count(distinct(sender))
from
`numia-data.evmos.evmos_message_types_transactions`
group by
order by
day, action

4. Delegations Over Time by validator

SELECT
date(block_timestamp) as day,
validator,
sum(osmo_amount) as delegated_amount,
count(*) as n_delegations
FROM
`numia-data.evmos.evmos_delegate`
group by
day, validator
order by
day, validator
  1. 5.
    Build a Report on Developer Revenue
Last modified 17d ago