owned this note
owned this note
Published
Linked with GitHub
# Sentinel SQL queries
###### tags: `filecoin-pl` `Resources`
:::info
Updated by February 2021
:::
*Authors: Danilo Lessa Bernardineli (@danlessa), ZX Zhang (@zxzhang)*
### Stats for sectors with less than 400d lifetime before the upgrade
Created on
: 24feb2021
```sql
/* Get the last state of the sectors */
/* Get the last state of the sectors */
with sector_states as (
select
msi.*,
max(msi.height) over (partition by msi.sector_id, msi.miner_id) as max_height
from miner_sector_infos msi
where msi.activation_epoch > 0
and (msi.expiration_epoch - msi.activation_epoch) < 1152000 /* Get sectors with less than 400d lifetime */
and msi.activation_epoch < 265200 /* Get sectors activated before the update */
and msi.expiration_epoch > msi.height /* Get only active sectors */
order by max_height
)
select
count(*) as sector_count,
sum(ss.initial_pledge::numeric) / 1e18 as initial_pledge_sum_in_fil,
count(*) * 32 as network_power_in_gb,
date_trunc('WEEK', to_timestamp(height_to_unix(ss.activation_epoch))) as activation_date,
date_trunc('WEEK', to_timestamp(height_to_unix(ss.expiration_epoch))) as expiration_date
from sector_states as ss
where ss.max_height = ss.height /* get the last state of the info */
group by activation_date, expiration_date
order by activation_date, expiration_date
```
## General
### Height to timestamp
```sql
select
to_timestamp(height_to_unix(mse.height))
from miner_sector_events mse
```
## Sector State
### Renewal Gap
#### Renewal events
Created on
: 29jan2021
Updated on
: 12feb2021
```sql
select
mse.miner_id,
mse.sector_id,
mse.height
from miner_sector_events mse
where mse."event" = 'SECTOR_EXTENDED'
```
####
### Renewal count per epoch
Created on
: 12feb21
```sql
select
to_timestamp(height_to_unix(mse.height)) as timestamp,
count(*) renewal_count
from miner_sector_events mse
where mse."event" = 'SECTOR_EXTENDED'
group by mse.height
```
### Early Termination events
#### Count per epoch
```sql
select
COUNT(*) as terminate_sectors_count,
to_timestamp(height_to_unix(pm.height)) as timestamp
from parsed_messages pm
where pm."method" = 'TerminateSectors'
group by pm.height
```
### Declare Fault events
#### Count per epoch
```sql
select
COUNT(*) as declare_fault_count,
to_timestamp(height_to_unix(pm.height)) as timestamp
from parsed_messages pm
where pm."method" = 'DeclareFaults'
group by pm.height
```
#### Weekly count
Updated on
: 12feb2021
```sql
select
COUNT(*) as declare_fault_count,
date_trunc('week', to_timestamp(height_to_unix(pm.height))) as timestamp
from parsed_messages pm
where pm."method" = 'DeclareFaults'
group by timestamp
order by timestamp
```
#### Count per miner
```
select
pm.to as miner_id,
COUNT(*) as declare_fault_count
from parsed_messages pm
where pm."method" = 'DeclareFaults'
group by pm.to
```
### Faulty sectors count over time
#### Miner
```sql
select
COUNT(*) filter (where mse."event" = 'SECTOR_FAULTED') as faulty_sectors,
COUNT(*) as total_sectors,
to_timestamp(height_to_unix(mse.height)) as timestamp,
miner_id
from miner_sector_events mse
group by mse.height, mse.miner_id
```
#### Network
```sql
select
COUNT(*) filter (where mse."event" = 'SECTOR_FAULTED') as faulty_sectors,
COUNT(*) as total_sectors,
to_timestamp(height_to_unix(mse.height)) as timestamp
from miner_sector_events mse
group by mse.height
```
### List faulty sector infos
```sql=
with faulty_sectors as (
select
distinct(mse.sector_id)
from miner_sector_events mse
where mse."event" = 'SECTOR_FAULTED'
)
select
mse.sector_id,
mse.height,
mpc.pre_commit_epoch,
mpc.expiration_epoch,
mpc.deal_weight,
mpc.verified_deal_weight
from faulty_sectors fse
join miner_sector_events mse
on mse.sector_id = fse.sector_id
join miner_pre_commit_infos mpc
on mpc.sector_id = fse.sector_id
where mse.height = mpc.height
```
### List sectors which faulted at some time
- Created on ??nov21
- Updated on ??jan21
```sql
select
distinct(mse.sector_id)
from miner_sector_events mse
where mse."event" = 'SECTOR_FAULTED'
```
### Upcoming sectors to expire information, filtered by a date
```sql
SELECT
distinct on (msi.sector_id) msi.sector_id::NUMERIC,
to_timestamp(height_to_unix(msi.activation_epoch)) AS activation_timestamp,
to_timestamp(height_to_unix(msi.expiration_epoch)) AS expiration_timestamp,
to_timestamp(height_to_unix(bh.height)) AS height_timestamp,
msi.*
FROM miner_sector_infos AS msi
JOIN block_headers bh
ON msi.state_root = bh.parent_state_root
WHERE msi.activation_epoch > 0
AND to_timestamp(height_to_unix(msi.expiration_epoch)) < '2021-06-01'
order BY msi.sector_id, bh.height DESC
```
### Latest sectors state
- Created on ??nov21
- Updated on 05feb21
```sql
with sector_states as (
select
msi.*,
max(msi.height) over (partition by msi.sector_id, msi.miner_id) as max_height
from miner_sector_infos msi
where msi.activation_epoch > 0
and msi.expiration_epoch > msi.height
order by max_height
)
select
count(*)
from ss
where ss.max_height = ss.height
```
### Count of sector expiration per epoch and miner_id (last state)
- Created on ??dec20
- Updated on 05feb21
```sql
with sector_states as (
select
msi.*,
max(msi.height) over (partition by msi.sector_id, msi.miner_id) as max_height
from miner_sector_infos msi
where msi.activation_epoch > 0
and msi.expiration_epoch > msi.height
order by max_height
)
select
miner_id,
count(*) as sector_count,
date_trunc('WEEK', to_timestamp(height_to_unix(ss.expiration_epoch))) as expiration_epoch
from sector_states ss
where ss.max_height = ss.height
group by expiration_epoch, miner_id
```
### Sector activation \ expiration per week (last state)
- Created on 17feb21
```sql
with sector_states as (
select
msi.*,
max(msi.height) over (partition by msi.sector_id, msi.miner_id) as max_height
from miner_sector_infos msi
where msi.activation_epoch > 0
and msi.expiration_epoch > msi.height
order by max_height
)
select
count(*) as sector_count,
date_trunc('WEEK', to_timestamp(height_to_unix(ss.activation_epoch))) as activation_week,
date_trunc('WEEK', to_timestamp(height_to_unix(ss.expiration_epoch))) as expiration_week
from sector_states ss
where ss.max_height = ss.height
group by activation_week, expiration_week
order by activation_week, expiration_week
```
### Sector activation per week (last state)
- Created on 17feb21
```sql
with sector_states as (
select
msi.*,
max(msi.height) over (partition by msi.sector_id, msi.miner_id) as max_height
from miner_sector_infos msi
where msi.activation_epoch > 0
and msi.expiration_epoch > msi.height
order by max_height
)
select
count(*) as sector_count,
date_trunc('WEEK', to_timestamp(height_to_unix(ss.activation_epoch))) as activation_week
from sector_states ss
where ss.max_height = ss.height
group by activation_week
order by activation_week
```
### Sector expiration per week (last state)
- Created on 17feb21
```sql
with sector_states as (
select
msi.*,
max(msi.height) over (partition by msi.sector_id, msi.miner_id) as max_height
from miner_sector_infos msi
where msi.activation_epoch > 0
and msi.expiration_epoch > msi.height
order by max_height
)
select
count(*) as sector_count,
date_trunc('WEEK', to_timestamp(height_to_unix(ss.expiration_epoch))) as expiration_week
from sector_states ss
where ss.max_height = ss.height
group by expiration_week
order by expiration_week
```
### Count of sector expiration per epoch (last state)
- Created on ??dec20
- Updated on 05feb21
```sql
with sector_states as (
select
msi.*,
max(msi.height) over (partition by msi.sector_id, msi.miner_id) as max_height
from miner_sector_infos msi
where msi.activation_epoch > 0
and msi.expiration_epoch > msi.height
order by max_height
)
select
count(*) as sector_count,
date_trunc('WEEK', to_timestamp(height_to_unix(ss.expiration_epoch))) as expiration_epoch
from sector_states ss
where ss.max_height = ss.height
group by expiration_epoch
```
```
SELECT
```
## Chain Economics
### Count of unique sectors over the network lifetime
```sql
select
count(*)
from (
select
1 as dummy
from miner_sector_infos msi
group by msi.miner_id, sector_id
) dt
```
### approximated vested amount over specified period
Thanks ZX (20nov2020)
```sql
SELECT
AVG((sa.state ->> 'LockedFunds')::NUMERIC/10e18)
FROM actors a
JOIN actor_states sa
ON sa.head = a.head
WHERE a.id = $1
AND sa.height > $2
AND sa.height < $3`, [minerOfInterest, epochStart, epochEnd])
```
### 25% immediately available reward
Thanks ZX (20nov2020)
```sql
SELECT
0.25*SUM(b.win_count::NUMERIC * cr.new_reward::NUMERIC)
FROM block_headers b
JOIN chain_rewards cr
ON b.parent_state_root = cr.state_root
WHERE b.miner = $1
AND b.height > $2
AND b.height < $3
AND b.height >= 170000`, [minerOfInterest, epochStart, epochEnd]
```
Google Drive
Gist
Clipboard
Sign in with Wallet
