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

5. Build a Report on Developer Revenue