SELECT
datasource,
COUNT(*) FILTER (WHERE is_active = 1) AS num_segments,
COUNT(*) FILTER (WHERE is_published = 1 AND is_overshadowed = 0 AND replication_factor = 0) AS num_zero_replica_segments,
COUNT(*) FILTER (WHERE is_published = 1 AND is_overshadowed = 0 AND is_available = 0 AND replication_factor > 0) AS num_segments_to_load,
COUNT(*) FILTER (WHERE is_available = 1 AND is_active = 0) AS num_segments_to_drop,
SUM("size") FILTER (WHERE is_active = 1) AS total_data_size,
MIN("num_rows") FILTER (WHERE is_available = 1 AND is_realtime = 0) AS min_segment_rows,
AVG("num_rows") FILTER (WHERE is_available = 1 AND is_realtime = 0) AS avg_segment_rows,
MAX("num_rows") FILTER (WHERE is_available = 1 AND is_realtime = 0) AS max_segment_rows,
SUM("num_rows") FILTER (WHERE is_active = 1) AS total_rows,
CASE WHEN SUM("num_rows") FILTER (WHERE is_available = 1) <> 0 THEN (SUM("size") FILTER (WHERE is_available = 1) / SUM("num_rows") FILTER (WHERE is_available = 1)) ELSE 0 END AS avg_row_size,
SUM("size" * "num_replicas") FILTER (WHERE is_active = 1) AS replicated_size
FROM sys.segments
GROUP BY 1
ORDER BY 1