active_parts_per_partition.sql

WITH
    'default' AS db_name,
    'upclick_metrics' AS table_name,
    10 AS interval_seconds,
    (SELECT uuid FROM system.tables WHERE database = db_name and name = table_name) AS table_id,
    (SELECT min(event_time) FROM system.part_log WHERE table_uuid = table_id) AS start_time
SELECT
    toStartOfInterval(event_time, toIntervalSecond(interval_seconds)) AS t,
    dateDiff('second', toStartOfInterval(start_time, toIntervalSecond(interval_seconds)), t) AS seconds,
    max(value) as parts
FROM clusterAllReplicas(default, system.asynchronous_metric_log)
WHERE event_time >= start_time
AND metric = 'MaxPartCountForPartition'
GROUP BY t
ORDER BY t DESC
SETTINGS skip_unavailable_shards = 1;