26Z10c3

CREATE MATERIALIZED VIEW mv_prefix_summary
COMMENT "S3 Prefix Level Summary MV"
DISTRIBUTED BY HASH(bucket, prefix)
REFRESH ASYNC EVERY (INTERVAL 5 MINUTE)
AS
WITH recursive_prefix AS (
    SELECT 
        bucket,
        unnest(
            transform(
                generate_series(1, array_length(split(prefix, '/'))),
                i -> array_to_string(slice(split(prefix, '/'), 1, i), '/')
            )
        ) AS prefix,
        size_bytes
    FROM object_current_state
    WHERE __deleted = 0 AND prefix != ''
)
SELECT 
    bucket,
    prefix,
    COUNT(1) AS total_object_count,
    SUM(size_bytes) AS total_size_bytes
FROM recursive_prefix
GROUP BY bucket, prefix;