Sentinel SQL queries

/* 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

select
to_timestamp(height_to_unix(mse.height))
from miner_sector_events mse

select 
mse.miner_id,
mse.sector_id,
mse.height
from miner_sector_events mse 
where mse."event" = 'SECTOR_EXTENDED'

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

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

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

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

select 
pm.to as miner_id,
COUNT(*) as declare_fault_count
from parsed_messages pm 
where pm."method" = 'DeclareFaults'
group by pm.to

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

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 

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 

select 
distinct(mse.sector_id)
from miner_sector_events mse 
where mse."event" = 'SECTOR_FAULTED'

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

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

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

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

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

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

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

select 
count(*)
from ( 
	select 
	1 as dummy
	from miner_sector_infos msi 
	group by msi.miner_id, sector_id
) dt

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])

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]