date(closed_at) as date,

closed_at,

lead(closed_at) over (order by closed_at) as next_ledger_time

from stellar.core.fact_ledgers

date,

count(*) as daily_ledgers_produced,

avg(datediff(second, closed_at, next_ledger_time) / 60) as avg_block_time_minutes

from ledger_times

where next_ledger_time is not null

group by date

order by date desc;