# MEV Monitoring - Operator

## Common dashboard settings
Replace query of the variable `Operator` in the dashboard settings with:

select name as __text, id as __value from node_operator where (${module} = -1 or staking_module_id = ${module})

Add new variable `Module` for specifying staking module:

select 'all' as __text, -1 as __value union select name as __text, id as __value from staking_module

## Payload source
### Payload source

select
  w.url as metric,
  count(*) as value
from
  slot_storage as s
inner join
  node_operator_version as r on r.version_id = s.operator_version_id
left join
  allowed_relay_storage as w on w.pubkey = s.relay_pubkey
where
  r.operator_id = ${operator}
  and s.block_number > 0
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}
group by
  w.url

### Blocks proposed via relays / total (1d MA)

with blocks as (
  select
    floor((${genesis} + s.slot_number * ${slot_seconds}) / ($__interval_ms / 1000)) * ($__interval_ms / 1000) as time,
    count(case when s.relay_pubkey is not null then 1 else null end)::float as mev_boosted,
    count(*)::float as total
  from
    slot_storage as s
  inner join
    node_operator_version as r on r.version_id = s.operator_version_id
  where
    s.is_block_indexed = true
    and s.is_operator_indexed = true
    and s.is_payload_indexed = true
    and s.propose_status = 2
    and s.slot_number >= ${from_slot}
    and s.slot_number <= ${to_slot}
    and r.id = ${operator}
  group by
    time
  order by
    time asc
)
select
  time,
  avg(mev_boosted / total) over (
    order by time asc
    rows between (24 * 60 * 60 / ${slot_seconds}) preceding and current row
  ) as value
from blocks
order by
  time asc

## Proposals
### Proposals

select
  floor((${genesis} + s.slot_number * ${slot_seconds}) / ($__interval_ms / 1000)) * ($__interval_ms / 1000) as time,
  (case when s.propose_status = 2 then 'Proposed' else 'Missed' end) as metric,
  count(*) as value
from
  slot_storage as s
inner join
  node_operator_version as r on r.version_id = s.operator_version_id
where
  s.is_block_indexed = true
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}
  and r.operator_id = ${operator}
group by
  time, metric
order by
  time asc

### Missed proposals (limit of 100 slots)

select
  s.slot_number as slot,
  r.name as operator_name,
  smv.name as module_name,
  w.url as payload_source,
  signed_bid_value / 1e18 as value
from
  slot_storage as s
inner join
  node_operator_version as r on r.version_id = s.operator_version_id
inner join
  staking_module_version as smv on smv.version_id = r.staking_module_version_id
left join
  allowed_relay_storage as w on w.pubkey = s.relay_pubkey
where
  r.operator_id = ${operator}
  and s.is_block_indexed = true
  and s.propose_status != 2
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}
order by
  s.slot_number desc
limit
  ${slots_limit}

### Proposed blocks (limit of 100 slots)

select
  (case
    when (
      ((f.type = 2 and s.delta_balance > 0) or (i.type = 2 and s.indirect_payment_value > 0))
      and w.url is not null
    ) then 'good' 
    when (
      f.type = 2 and s.delta_balance > 0
    ) then 'warning' 
    else
      'bad'
    end
  ) as check,
  s.slot_number as slot,
  s.block_number as block,
  w.url as payload_source,
  s.fee_recipient,
  s.delta_balance / 1e18 as delta_balance,
  s.indirect_fee_recipient,
  s.indirect_payment_value / 1e18 as indirect_payment
from
  slot_storage as s
inner join
  node_operator_version as r on r.version_id = s.operator_version_id
left join
  fee_recipient_storage as f on f.address = s.fee_recipient
left join
  fee_recipient_storage as i on i.address = s.indirect_fee_recipient
left join
  allowed_relay_storage as w on w.pubkey = s.relay_pubkey
where
  s.spec != 'phase0'
  and s.spec != 'altair'
  and s.block_number > 0
  and is_payload_indexed = true
  and is_value_indexed = true
  and is_payment_indexed = true
  and r.operator_id = ${operator}
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}
order by
  s.slot_number desc
limit
  ${slots_limit}

## Bids
### Bids

select
  floor((${genesis} + s.slot_number * ${slot_seconds}) / ($__interval_ms / 1000)) * ($__interval_ms / 1000) as time,
  'max' as metric,
  avg(max_bid_value) / 1e18 as value
from
  slot_storage as s
inner join
  node_operator_version as r on r.version_id = s.operator_version_id
where
  s.is_block_indexed = true
  and s.is_bids_range_indexed = true
  and s.is_operator_indexed = true
  and s.propose_status = 2
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}
  and r.operator_id = ${operator}
group by
  time
order by
  time asc

# MEV Monitoring DB
## Common dashboard settings
Add new variable `Module` for specifying staking module:

select 'all' as __text, -1 as __value union select name as __text, id as __value from staking_module

## Summary
### Unknown FR

select
  count(*)
from
  slot_storage as s
left join
  fee_recipient_storage as f on f.address = s.fee_recipient
left join
  fee_recipient_storage as i on i.address = s.indirect_fee_recipient
where
  s.operator_version_id is not null
  and is_payload_indexed = true
  and s.block_number > 0
  and (f.type != 2 or f.type is null)
  and (i.type != 2 or i.type is null)
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}

### No payment

select
  count(*)
from
  slot_storage as s
left join
  fee_recipient_storage as f on f.address = s.fee_recipient
left join
  fee_recipient_storage as i on i.address = s.indirect_fee_recipient
where
  s.is_block_indexed = true
  and s.is_payload_indexed = true
  and s.is_payment_indexed = true
  and s.block_number > 0
  and s.propose_status = 2
  and (
    -- no direct payment
    (f.type = 2 and s.delta_balance = 0)
    or
    -- no indirect payment
    (i.type = 2 and (f.type = 2 or f.type is null) and (s.indirect_payment_value = 0 or s.indirect_payment_value is null))
  )
  and s.operator_version_id is not null
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}

### Unknown PS

select
  count(*)
from
  slot_storage as s
left join
  allowed_relay_storage as w
on
  w.pubkey = s.relay_pubkey
where
  s.is_block_indexed = true
  and s.is_payload_indexed = true
  and s.block_number > 0
  and w.pubkey is null
  and s.propose_status = 2
  and s.operator_version_id is not null
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}

### Missed slots

select
  count(*)
from
  slot_storage
where
  is_block_indexed = true
  and propose_status != 2
  and operator_version_id is not null
  and slot_number >= ${from_slot}
  and slot_number <= ${to_slot}

### Free money

select
  sum(delta_balance) / 1e18 as free_money
from
  slot_storage as s
left join
  fee_recipient_storage as f on f.address = s.fee_recipient
left join
  fee_recipient_storage as i on i.address = s.indirect_fee_recipient
where
  s.is_operator_indexed = true
  and s.is_value_indexed = true
  and s.operator_version_id is null
  and s.block_number > 0
  and (f.type = 2 or i.type = 2)
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}

## Summary. Recent issues
### Unknown fee recipient (limit of 100 slots)

select
  s.slot_number as slot,
  s.block_number as block,
  r.name as operator_name,
  smv.name as module_name,
  s.fee_recipient,
  s.indirect_fee_recipient
from
  slot_storage as s
inner join
  node_operator_version as r on r.version_id = s.operator_version_id
inner join
  staking_module_version as smv on smv.version_id = r.staking_module_version_id
left join
  fee_recipient_storage as f on f.address = s.fee_recipient
left join
  fee_recipient_storage as i on i.address = s.indirect_fee_recipient
where
  s.is_payload_indexed = true
  and (${module} = -1 or smv.staking_module_id = ${module})
  and s.block_number > 0
  and (f.type != 2 or f.type is null)
  and (i.type != 2 or i.type is null)
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}
order by
  s.slot_number desc
limit
  ${slots_limit}

### No payment (limit of 100 slots)

select
  s.slot_number as slot,
  s.block_number as block,
  r.name as operator_name,
  smv.name as module_name,
  s.delta_balance / 1e18 as delta_balance,
  s.indirect_payment_value as indirect_payment
from
  slot_storage as s
inner join
  node_operator_version as r on r.version_id = s.operator_version_id
inner join
  staking_module_version as smv on smv.version_id = r.staking_module_version_id
left join
  fee_recipient_storage as f on f.address = s.fee_recipient
left join
  fee_recipient_storage as i on i.address = s.indirect_fee_recipient
left join
  allowed_relay_storage as w on w.pubkey = s.relay_pubkey
where
  s.is_block_indexed = true
  and (${module} = -1 or smv.staking_module_id = ${module})
  and s.is_payload_indexed = true
  and s.is_payment_indexed = true
  and s.block_number > 0
  and s.propose_status = 2
  and (
    -- no direct payment
    (f.type = 2 and s.delta_balance = 0)
    or
    -- no indirect payment
    (i.type = 2 and (f.type = 2 or f.type is null) and (s.indirect_payment_value = 0 or s.indirect_payment_value is null))
  )
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}
order by
  s.slot_number desc
limit
  ${slots_limit}

### Unknown payload source (limit of 100 slots)

select
  s.slot_number as slot,
  s.block_number as block,
  r.name as operator_name,
  smv.name as module_name,
  s.fee_recipient,
  s.delta_balance / 1e18 as delta_balance,
  s.indirect_payment_value / 1e18 as indirect_payment
from
  slot_storage as s
inner join
  node_operator_version as r on r.version_id = s.operator_version_id
inner join
  staking_module_version as smv on smv.version_id = r.staking_module_version_id
left join
  fee_recipient_storage as f on f.address = s.fee_recipient
left join
  fee_recipient_storage as i on i.address = s.indirect_fee_recipient
left join
  allowed_relay_storage as w on w.pubkey = s.relay_pubkey
where
  s.block_number > 0
  and (${module} = -1 or smv.staking_module_id = ${module})
  and is_payload_indexed = true
  and is_value_indexed = true
  and is_payment_indexed = true
  and w.pubkey is null
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}
order by
  s.slot_number desc
limit
  ${slots_limit}

### Missed proposals (limit of 100 slots)

select
  s.slot_number as slot,
  r.name as operator_name,
  smv.name as module_name,
  w.url as payload_source,
  signed_bid_value / 1e18 as value
from
  slot_storage as s
inner join
  node_operator_version as r on r.version_id = s.operator_version_id
inner join
  staking_module_version as smv on smv.version_id = r.staking_module_version_id
left join
  allowed_relay_storage as w on w.pubkey = s.relay_pubkey
where
  s.is_block_indexed = true
  and (${module} = -1 or smv.staking_module_id = ${module})
  and s.propose_status != 2
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}
order by
  s.slot_number desc
limit
  ${slots_limit}

## Proposals. Lido blocks
### Summary

select
  count(case when operator_version_id is not null then 1 end) as Lido,
  count(case when operator_version_id is null then 1 end) as Others
from
  slot_storage
where
  is_operator_indexed = true
  and propose_status = 2
  and block_number > 0
  and slot_number >= ${from_slot}
  and slot_number <= ${to_slot}

### Proposed blocks by Lido validators (limit of 100 slots)

select
  (case
    when (
      ((f.type = 2 and s.delta_balance > 0) or (i.type = 2 and s.indirect_payment_value > 0))
      and w.url is not null
    ) then 'good' 
    when (
      f.type = 2 and s.delta_balance > 0
    ) then 'warning' 
    else
      'bad'
    end
  ) as check,
  s.slot_number as slot,
  s.block_number as block,
  r.name as operator_name,
  smv.name as module_name,
  w.url as payload_source,
  s.fee_recipient,
  s.delta_balance / 1e18 as delta_balance,
  s.indirect_fee_recipient,
  s.indirect_payment_value / 1e18 as indirect_payment
from
  slot_storage as s
inner join
  node_operator_version as r on r.version_id = s.operator_version_id
inner join
  staking_module_version as smv on smv.version_id = r.staking_module_version_id
left join
  fee_recipient_storage as f on f.address = s.fee_recipient
left join
  fee_recipient_storage as i on i.address = s.indirect_fee_recipient
left join
  allowed_relay_storage as w on w.pubkey = s.relay_pubkey
where
  s.spec != 'phase0'
  and s.spec != 'altair'
  and (${module} = -1 or smv.staking_module_id = ${module})
  and s.block_number > 0
  and is_payload_indexed = true
  and is_value_indexed = true
  and is_payment_indexed = true
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}
order by
  s.slot_number desc
limit
  ${slots_limit}

### Proposals

select
  floor((${genesis} + s.slot_number * ${slot_seconds}) / ($__interval_ms / 1000)) * ($__interval_ms / 1000) as time,
  r.name || ' (' || smv.name || ')' as metric,
  count(*) as value
from
  slot_storage as s
inner join
  node_operator_version as r on r.version_id = s.operator_version_id
inner join
  staking_module_version as smv on smv.version_id = r.staking_module_version_id
where
  s.is_block_indexed = true
  and (${module} = -1 or smv.staking_module_id = ${module})
  and s.propose_status = 2
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}
group by
  time, metric
order by
  time asc

## Proposals. Missed by Lido validators
### Lido proposing ratio

select
  100 * (count(*) filter (where operator_version_id is not null and propose_status = 2))
  / (greatest(1, count(*) filter (where operator_version_id is not null))) as lido
from
  slot_storage
where
  is_block_indexed = true
  and is_operator_indexed = true
  and slot_number >= ${from_slot}
  and slot_number <= ${to_slot}

### Other proposing ratio

select
  100 * (count(*) filter (where operator_version_id is null and propose_status = 2))
  / (greatest(1, count(*) filter (where operator_version_id is null))) as other
from
  slot_storage
where
  is_block_indexed = true
  and is_operator_indexed = true
  and slot_number >= ${from_slot}
  and slot_number <= ${to_slot}

### List of missed proposals (limit of 100 slots)

select
  s.slot_number as slot,
  r.name as operator_name,
  smv.name as module_name
from
  slot_storage as s
inner join
  node_operator_version as r on r.version_id = s.operator_version_id
inner join
  staking_module_version as smv on smv.version_id = r.staking_module_version_id
left join
  allowed_relay_storage as w on w.pubkey = s.relay_pubkey
where
  s.is_block_indexed = true
  and (${module} = -1 or smv.staking_module_id = ${module})
  and s.propose_status != 2
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}
order by
  s.slot_number desc
limit
  ${slots_limit}

### Missed proposals

select
  floor((${genesis} + s.slot_number * ${slot_seconds}) / ($__interval_ms / 1000)) * ($__interval_ms / 1000) as time,
  r.name || ' (' || smv.name || ')' as metric,
  count(*) as value
from
  slot_storage as s
inner join
  node_operator_version as r on r.version_id = s.operator_version_id
inner join
  staking_module_version as smv on smv.version_id = r.staking_module_version_id
where
  s.is_block_indexed = true
  and s.propose_status != 2
  and (${module} = -1 or smv.staking_module_id = ${module})
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}
group by
  time, metric
order by
  time asc

## Fee recipients. Lido blocks
### Summary

select
  count(*) filter (where f.type = 2 or i.type = 2) as lido,
  count(*) filter (where (f.type != 2 or f.type is null) and (i.type != 2 or i.type is null)) as unknown
from
  slot_storage as s
left join
  fee_recipient_storage as f on f.address = s.fee_recipient
left join
  fee_recipient_storage as i on i.address = s.indirect_fee_recipient
where 
  s.spec != 'phase0'
  and s.operator_version_id is not null
  and s.spec != 'altair'
  and s.block_number > 0
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}

### Fee recipients in proposed blocks by operators

select
  r.index as id,
  r.name as operator_name,
  smv.name as module_name,
  count(*) filter (where f.type = 2) as direct,
  count(*) filter (where i.type = 2) as indirect,
  count(*) filter (where (f.type != 2 or f.type is null) and (i.type != 2 or i.type is null)) as unknown
from
  slot_storage as s
inner join
  node_operator_version as r on r.version_id = s.operator_version_id
inner join
  staking_module_version as smv on smv.version_id = r.staking_module_version_id
left join
  fee_recipient_storage as f on f.address = s.fee_recipient
left join
  fee_recipient_storage as i on i.address = s.indirect_fee_recipient
where
  s.block_number > 0
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}
  and (${module} = -1 or smv.staking_module_id = ${module})
group by
  r.index,
  r.name,
  smv.name
order by
  count(*) filter (where (f.type != 2 or f.type is null) and (i.type != 2 or i.type is null)) > 0 desc,
  count(*) filter (where f.type = 2) = 0 desc,
  count(*) filter (where i.type = 2) = 0 desc,
  r.index asc

### Fee recipients in proposed blocks by operators

select
  r.index as id,
  r.name as operator_name,
  smv.name as module_name,
  count(*) filter (where f.type = 2) as direct,
  count(*) filter (where i.type = 2) as indirect,
  count(*) filter (where (f.type != 2 or f.type is null) and (i.type != 2 or i.type is null)) as unknown
from
  slot_storage as s
inner join
  node_operator_version as r on r.version_id = s.operator_version_id
inner join
  staking_module_version as smv on smv.version_id = r.staking_module_version_id
left join
  fee_recipient_storage as f on f.address = s.fee_recipient
left join
  fee_recipient_storage as i on i.address = s.indirect_fee_recipient
where
  s.block_number > 0
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}
  and (${module} = -1 or smv.staking_module_id = ${module})
group by
  r.index,
  r.name,
  smv.name
order by
  count(*) filter (where (f.type != 2 or f.type is null) and (i.type != 2 or i.type is null)) > 0 desc,
  count(*) filter (where f.type = 2) = 0 desc,
  count(*) filter (where i.type = 2) = 0 desc,
  r.index asc

### Blocks with unknown fee recipient (limit of 100 slots)

select
  s.slot_number as slot,
  s.block_number as block,
  r.name as operator_name,
  smv.name as module_name,
  s.fee_recipient,
  s.indirect_fee_recipient
from
  slot_storage as s
inner join
  node_operator_version as r on r.version_id = s.operator_version_id
inner join
  staking_module_version as smv on smv.version_id = r.staking_module_version_id
left join
  fee_recipient_storage as f on f.address = s.fee_recipient
left join
  fee_recipient_storage as i on i.address = s.indirect_fee_recipient
where
  is_payload_indexed = true
  and (${module} = -1 or smv.staking_module_id = ${module})
  and s.block_number > 0
  and (f.type != 2 or f.type is null)
  and (i.type != 2 or i.type is null)
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}
order by
  s.slot_number desc
limit
  ${slots_limit}

## Fee recipients. Lido FR is used by unknown validators
### Total blocks

select
  count(*)
from
  slot_storage as s
left join
  fee_recipient_storage as f on f.address = s.fee_recipient
left join
  fee_recipient_storage as i on i.address = s.indirect_fee_recipient
where
  s.is_operator_indexed = true
  and s.operator_version_id is not null
  and s.block_number > 0
  and (f.type = 2 or i.type = 2)
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}

### Free money, ETH

select
  sum(delta_balance) / 1e18 as free_money
from
  slot_storage as s
left join
  fee_recipient_storage as f on f.address = s.fee_recipient
left join
  fee_recipient_storage as i on i.address = s.indirect_fee_recipient
where
  s.is_operator_indexed = true
  and s.operator_version_id is not null
  and s.is_value_indexed = true
  and s.block_number > 0
  and (f.type = 2 or i.type = 2)
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}

### Blocks from unknown validator (limit of 100 slots)

select
  s.slot_number as slot,
  s.block_number as block,
  s.validator_index,
  s.pubkey as validator,
  s.delta_balance / 1e18 as delta_balance,
  s.indirect_payment_value / 1e18 as indirect_payment
from
  slot_storage as s
left join
  fee_recipient_storage as f on f.address = s.fee_recipient
left join
  fee_recipient_storage as i on i.address = s.indirect_fee_recipient
where
  s.is_operator_indexed = true
  and s.operator_version_id is not null
  and s.block_number > 0
  and (f.type = 2 or i.type = 2)
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}
order by
  s.slot_number desc
limit
  ${slots_limit}

## Fee recipients. Top
### Top 10

with top as (
  select
    case when indirect_payment_value is not null then indirect_fee_recipient else fee_recipient end as metric,
    sum(case when indirect_payment_value is not null then indirect_payment_value else delta_balance end) / 1e18 as value
  from
    slot_storage
  where
    fee_recipient is not null
    and (indirect_payment_value is not null or delta_balance >= 0)
    and slot_number >= ${from_slot}
    and slot_number <= ${to_slot}
  group by
    metric
  order by
    value desc
  limit 10
)

select * from top union all

select
  'Other' as metric,
  sum(case when indirect_payment_value is not null then indirect_payment_value else delta_balance end) / 1e18 as value
from
  slot_storage
where
  fee_recipient is not null
  and (indirect_payment_value is not null or delta_balance >= 0)
  and slot_number >= ${from_slot}
  and slot_number <= ${to_slot}
  and fee_recipient not in (select metric from top)

### Top 10

select
  case when indirect_payment_value is not null then indirect_fee_recipient else fee_recipient end as recipient,
  sum(case when indirect_payment_value is not null then indirect_payment_value else delta_balance end) / 1e18 as total_rewards,
  count(*) as blocks
from
  slot_storage
where 
  fee_recipient is not null
  and (indirect_payment_value is not null or delta_balance >= 0)
  and slot_number >= ${from_slot}
  and slot_number <= ${to_slot}
group by
  recipient
order by
  total_rewards desc nulls last
limit 10

## Payload source. Overall statistic
### Lido

select
  case when url is null then 'unknown' else url end as metric,
  count(*) as value
from
  slot_storage as s
left join
  allowed_relay_storage as w on w.pubkey = s.relay_pubkey
where
  s.is_payload_indexed = true
  s.operator_version_id is not null
  and s.block_number > 0
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}
group by
  w.pubkey,
  w.url

### Other

select
  case when url is null then 'unknown' else url end as metric,
  count(*) as value
from
  slot_storage as s
left join
  allowed_relay_storage as w on w.pubkey = s.relay_pubkey
where
  s.operator_version_id is null
  and s.is_payload_indexed = true
  and s.block_number > 0
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}
group by
  w.pubkey,
  w.url

### Payload sources by operators

select
  r.index as id,
  r.name as operator_name,
  smv.name as module_name,
  count(*) filter (where w.pubkey is not null) as known,
  count(*) filter (where w.pubkey is null) as unknown
from
  slot_storage as s
inner join
  node_operator_version as r on r.version_id = s.operator_version_id
inner join
  staking_module_version as smv on smv.version_id = r.staking_module_version_id
left join
  allowed_relay_storage as w on w.pubkey = s.relay_pubkey
where
  s.block_number > 0
  and (${module} = -1 or smv.staking_module_id = ${module})
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}
group by
  r.index,
  r.name
order by
  count(*) filter (where w.pubkey is null) > 0 desc,
  count(*) filter (where w.pubkey is not null) = 0 desc,
  r.index asc

## Payload source. Relay usage
### Blocks proposed via relays / total (1d MA)

with blocks as (
  select
    floor((${genesis} + s.slot_number * ${slot_seconds}) / ($__interval_ms / 1000)) * ($__interval_ms / 1000) as time,
    (case when s.operator_version_id is not null then 'lido' else 'other' end) as metric,
    
    count(case when s.relay_pubkey is not null then 1 else null end)::float as mev_boosted,
    count(*)::float as total
  from
    slot_storage as s
  where
    s.is_block_indexed = true
    and s.is_operator_indexed = true
    and s.is_payload_indexed = true
    and s.propose_status = 2
    and s.slot_number >= ${from_slot}
    and s.slot_number <= ${to_slot}
  group by
    time, metric
  order by
    time asc
)
select
  time,
  metric,
  avg(mev_boosted / total) over (
    partition by metric
    order by time asc
    rows between (24 * 60 * 60 / ${slot_seconds}) preceding and current row
  ) as value
from blocks
order by
  time asc

## Payload source. By operator
### Payload sources by operators

select
  r.index as id,
  r.name as operator_name,
  smv.name as module_name,
  w.url as payload_source,
  count(*) as blocks
from
  slot_storage as s
inner join
  node_operator_version as r on r.version_id = s.operator_version_id
inner join
  staking_module_version as smv on smv.version_id = r.staking_module_version_id
left join
  allowed_relay_storage as w on w.pubkey = s.relay_pubkey
where
  delta_balance is not null
  and (${module} = -1 or smv.staking_module_id = ${module})
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}
group by
  r.index,
  r.name,
  w.url
order by
  r.index asc

## Payload source. Payment mismatch
### Builder payment mismatch

select
  slot_number as slot,
  s.block_number as block,
  r.name as operator_name,
  smv.name as module_name,
  w.url as payload_source,
  delta_balance / 1e18 as delta_balance,
  indirect_payment_value / 1e18 as indirect_payment,
  s.signed_bid_value / 1e18 as bid_value
from
  slot_storage as s
left join
  node_operator_version as r on r.version_id = s.operator_version_id
left join
  staking_module_version as smv on smv.version_id = r.staking_module_version_id
    and (${module} = -1 or smv.staking_module_id = ${module})
left join
  allowed_relay_storage as w on w.pubkey = s.relay_pubkey
where
  s.is_payment_indexed = true
  and s.is_payload_indexed = true
  and (case when indirect_payment_value is not null then indirect_payment_value else delta_balance end) != s.signed_bid_value
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}
order by
  s.slot_number desc
limit
  ${slots_limit}

## Payload source. All bids
### All bids

select
  floor((${genesis} + s.slot_number * ${slot_seconds}) / ($__interval_ms / 1000)) * ($__interval_ms / 1000) as time,
  'max' as metric,
  avg(max_bid_value) / 1e18 as value
from
  slot_storage as s
where
  s.is_block_indexed = true
  and s.is_bids_range_indexed = true
  and s.is_operator_indexed = true
  and s.propose_status = 2
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}
group by
  time
order by
  time asc

### Lido

select
  floor((${genesis} + s.slot_number * ${slot_seconds}) / ($__interval_ms / 1000)) * ($__interval_ms / 1000) as time,
  'max' as metric,
  avg(max_bid_value) / 1e18 as value
from
  slot_storage as s
where
  s.is_block_indexed = true
  and s.is_bids_range_indexed = true
  and s.is_operator_indexed = true
  and s.operator_version_id is not null
  and s.propose_status = 2
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}
group by
  time
order by
  time asc

### Other

select
  floor((${genesis} + s.slot_number * ${slot_seconds}) / ($__interval_ms / 1000)) * ($__interval_ms / 1000) as time,
  'max' as metric,
  avg(max_bid_value) / 1e18 as value
from
  slot_storage as s
where
  s.is_block_indexed = true
  and s.is_bids_range_indexed = true
  and s.is_operator_indexed = true
  and s.operator_version_id is null
  and s.propose_status = 2
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}
group by
  time
order by
  time asc

## Block rewards. By operator
### Extracted rewards by node operators

select
  r.index as id,
  r.name as operator_name,
  smv.name as module_name,
  count(*) as blocks,
  avg(case when indirect_payment_value is not null then indirect_payment_value else delta_balance end) / 1e18 as average_value,
  (percentile_cont(0.5) within group(order by (case when indirect_payment_value is not null then indirect_payment_value else delta_balance end))) / 1e18 as median_value
from
  slot_storage as s
inner join
  node_operator_version as r on r.version_id = s.operator_version_id
inner join
  staking_module_version as smv on smv.version_id = r.staking_module_version_id
left join
  fee_recipient_storage as f on f.address = s.fee_recipient
where
  delta_balance is not null
  and (indirect_payment_value is not null or delta_balance >= 0)
  and (${module} = -1 or smv.staking_module_id = ${module})
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}
group by
  r.index,
  r.name
order by
  r.index asc

## Block rewards. By payload source
### Rewards by payload source

select
  w.url as payload_source,
  count(*) as blocks,
  avg(case when indirect_payment_value is not null then indirect_payment_value else delta_balance end) / 1e18 as average_value,
  (percentile_cont(0.5) within group(order by (case when indirect_payment_value is not null then indirect_payment_value else delta_balance end))) / 1e18 as median_value
from
  slot_storage as s
left join
  node_operator_version as r on r.version_id = s.operator_version_id
left join
  node_operator as nop on nop.id = r.operator_id
    and (${module} = -1 or nop.staking_module_id = ${module})
left join
  fee_recipient_storage as f on f.address = s.fee_recipient
left join
  allowed_relay_storage as w on w.pubkey = s.relay_pubkey
where
  delta_balance is not null
  and (indirect_payment_value is not null or delta_balance >= 0)
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}
group by
  payload_source
order by
  blocks desc

## Block rewards. The most valued blocks
### The most valued blocks (limit of 100 slots)

select
  s.slot_number as slot,
  s.block_number as block,
  (case when indirect_payment_value is not null then indirect_payment_value else delta_balance end) / 1e18 as value,
  r.name as operator_name,
  smv.name as module_name
from
  slot_storage as s
left join
  node_operator_version as r on r.version_id = s.operator_version_id
left join
  node_operator as nop on nop.id = r.operator_id
    and (${module} = -1 or nop.staking_module_id = ${module})
left join
  staking_module_version as smv on smv.version_id = r.staking_module_version_id
where
  (indirect_payment_value is not null or delta_balance >= 0)
  and is_operator_indexed
  and slot_number >= ${from_slot}
  and slot_number <= ${to_slot}
order by
  value desc
limit
  ${slots_limit}

### The most valued Lido blocks (limit of 100 slots)

select
  s.slot_number as slot,
  s.block_number as block,
  (case when indirect_payment_value is not null then indirect_payment_value else delta_balance end) / 1e18 as value,
  r.name as operator_name,
  smv.name as module_name
from
  slot_storage as s
inner join
  node_operator_version as r on r.version_id = s.operator_version_id
inner join
  staking_module_version as smv on smv.version_id = r.staking_module_version_id
where
  (indirect_payment_value is not null or delta_balance >= 0)
  and is_operator_indexed = true
  and operator_version_id is not null
  and slot_number >= ${from_slot}
  and slot_number <= ${to_slot}
  and (${module} = -1 or smv.staking_module_id = ${module})
order by
  value desc
limit
  ${slots_limit}

## Block rewards. Overall statistics
### Median, ETH

select
  (percentile_cont(0.5) within group(order by (case when indirect_payment_value is not null then indirect_payment_value else delta_balance end))) / 1e18 as lido
from
  slot_storage
where
  is_value_indexed = true
  and delta_balance is not null
  and (indirect_payment_value is not null or delta_balance >= 0)
  and operator_version_id is not null
  and slot_number >= ${from_slot}
  and slot_number <= ${to_slot}

### Average, ETH

select
  avg(case when indirect_payment_value is not null then indirect_payment_value else delta_balance end) / 1e18 as lido
from
  slot_storage
where
  is_value_indexed = true
  and delta_balance is not null
  and (indirect_payment_value is not null or delta_balance >= 0)
  and operator_version_id is not null
  and slot_number >= ${from_slot}
  and slot_number <= ${to_slot}

### Block rewards

select
  floor((${genesis} + s.slot_number * ${slot_seconds}) / ($__interval_ms / 1000)) * ($__interval_ms / 1000) as time,
  (case when s.operator_version_id is not null then 'lido' else 'other' end) as metric,
  avg(case when s.indirect_payment_value is not null then s.indirect_payment_value else s.delta_balance end) / 1e18 as value
from
  slot_storage as s
left join
  node_operator_version as r on r.version_id = s.operator_version_id
left join
  node_operator as nop on nop.id = r.operator_id
    and (${module} = -1 or nop.staking_module_id = ${module})
where
  s.is_block_indexed = true
  and s.is_operator_indexed = true
  and s.is_payment_indexed = true
  and s.propose_status = 2
  and (indirect_payment_value is not null or delta_balance >= 0)
  and s.slot_number >= ${from_slot}
  and s.slot_number <= ${to_slot}
group by
  time, metric
order by
  time asc

## Registry
### Total validators

select
  count(*)
from
  registry_key
where
  used = true

### Last update time lag

select
  (extract(epoch from now()) - timestamp::bigint)
from
  registry_meta
limit 1

### Last update

select
  timestamp::bigint * 1000
from
  registry_meta
limit 1

### Validators

select
  smv.name as module_name,
  nop.index as "id",
  nop.name as operator_name,
  vals.validators
from (
	select
	  r.id as node_operator_id,
	  count(*) as validators
	from node_operator as r
	inner join node_operator_key as k 
    on r.id = k.operator_id
	inner join node_operator_key_version as okv
    on okv.key_id = k.id
	where okv.meta_version_id = (
    select version_id
    from meta_version
    where status = 1
    order by timestamp desc
    limit 1
	) and (${module} = -1 or r.staking_module_id = ${module})
	group by r.id
) as vals
  inner join node_operator as nop
    on nop.id = vals.node_operator_id
  inner join staking_module as sm
    on sm.id = nop.staking_module_id
order by nop.index asc