$ clickhouse-local 
ClickHouse local version 22.10.1.1.

milovidov-desktop :) CREATE TABLE video_log
                     (
                         `datetime` DateTime,
                         `user_id` UInt64,
                         `device_id` UInt64,
                         `domain` LowCardinality(String),
                         `bytes` UInt64,
                         `duration` UInt64
                     )
                     ENGINE = MergeTree
                     PARTITION BY toDate(datetime)
                     ORDER BY (user_id, device_id);

CREATE TABLE video_log
(
    `datetime` DateTime,
    `user_id` UInt64,
    `device_id` UInt64,
    `domain` LowCardinality(String),
    `bytes` UInt64,
    `duration` UInt64
)
ENGINE = MergeTree
PARTITION BY toDate(datetime)
ORDER BY (user_id, device_id)

Query id: dd100967-0fce-4dea-b937-ca10e5fc1d0a

Ok.

0 rows in set. Elapsed: 0.019 sec. 

milovidov-desktop :) CREATE TABLE video_log_result__fuzz_0 (`hour` Nullable(DateTime), `sum_bytes` UInt64, `avg_duration` Float64) ENGINE = MergeTree PARTITION BY toDate(hour) ORDER BY sum_bytes SETTINGS allow_nullable_key = 1

CREATE TABLE video_log_result__fuzz_0
(
    `hour` Nullable(DateTime),
    `sum_bytes` UInt64,
    `avg_duration` Float64
)
ENGINE = MergeTree
PARTITION BY toDate(hour)
ORDER BY sum_bytes
SETTINGS allow_nullable_key = 1

Query id: fe2c04ec-566c-4704-b7af-af2e226d9dad

Ok.

0 rows in set. Elapsed: 0.014 sec. 

milovidov-desktop :) CREATE TABLE rng
                     (
                         `user_id_raw` UInt64,
                         `device_id_raw` UInt64,
                         `domain_raw` UInt64,
                         `bytes_raw` UInt64,
                         `duration_raw` UInt64
                     )
                     ENGINE = GenerateRandom(1024);

CREATE TABLE rng
(
    `user_id_raw` UInt64,
    `device_id_raw` UInt64,
    `domain_raw` UInt64,
    `bytes_raw` UInt64,
    `duration_raw` UInt64
)
ENGINE = GenerateRandom(1024)

Query id: 1b4dc5ab-8784-4ef3-a7b3-a99869a06332

Ok.

0 rows in set. Elapsed: 0.000 sec. 

milovidov-desktop :) INSERT INTO video_log SELECT
                       toUnixTimestamp('2022-07-22 01:00:00')
                       + (rowNumberInAllBlocks() / 20000),
                       user_id_raw % 100000000 AS user_id,
                       device_id_raw % 200000000 AS device_id,
                       domain_raw % 100,
                       (bytes_raw % 1024) + 128,
                       (duration_raw % 300) + 100
                     FROM rng
                     LIMIT 1728000;

INSERT INTO video_log SELECT
    toUnixTimestamp('2022-07-22 01:00:00') + (rowNumberInAllBlocks() / 20000),
    user_id_raw % 100000000 AS user_id,
    device_id_raw % 200000000 AS device_id,
    domain_raw % 100,
    (bytes_raw % 1024) + 128,
    (duration_raw % 300) + 100
FROM rng
LIMIT 1728000

Query id: 8a57c429-568d-40cb-9d13-98ccbbd7aafe

Ok.

0 rows in set. Elapsed: 0.341 sec. Processed 2.10 million rows, 83.88 MB (6.15 million rows/s., 246.14 MB/s.)

milovidov-desktop :) INSERT INTO video_log SELECT
                       toUnixTimestamp('2022-07-22 01:00:00')
                       + (rowNumberInAllBlocks() / 20000),
                       user_id_raw % 100000000 AS user_id,
                       100 AS device_id,
                       domain_raw % 100,
                       (bytes_raw % 1024) + 128,
                       (duration_raw % 300) + 100
                     FROM rng
                     LIMIT 10;
                     

INSERT INTO video_log SELECT
    toUnixTimestamp('2022-07-22 01:00:00') + (rowNumberInAllBlocks() / 20000),
    user_id_raw % 100000000 AS user_id,
    100 AS device_id,
    domain_raw % 100,
    (bytes_raw % 1024) + 128,
    (duration_raw % 300) + 100
FROM rng
LIMIT 10

Query id: 2e7bb653-73c7-4687-908e-4b6178c90ade

Ok.

0 rows in set. Elapsed: 0.003 sec. 

milovidov-desktop :) ALTER TABLE video_log ADD PROJECTION p_norm
                     (
                         SELECT
                             datetime,
                             device_id,
                             bytes,
                             duration
                         ORDER BY device_id
                     );
                     

ALTER TABLE video_log
    ADD PROJECTION p_norm
    (
        SELECT 
            datetime,
            device_id,
            bytes,
            duration
        ORDER BY device_id
    )

Query id: 7d970599-65ae-4b6b-af63-72671d9badd0

Ok.

0 rows in set. Elapsed: 0.002 sec. 

milovidov-desktop :) ALTER TABLE video_log MATERIALIZE PROJECTION p_norm settings mutations_sync=1;
                     

ALTER TABLE video_log
    MATERIALIZE PROJECTION p_norm
SETTINGS mutations_sync = 1

Query id: 73fdfd46-7bb8-475f-9e11-dfaa8216692f

Ok.

0 rows in set. Elapsed: 0.173 sec. 

milovidov-desktop :) ALTER TABLE video_log ADD PROJECTION p_agg
                     (
                         SELECT
                             toStartOfHour(datetime) AS hour,
                             domain,
                             sum(bytes),
                             avg(duration)
                         GROUP BY
                             hour,
                             domain
                     );
                     

ALTER TABLE video_log
    ADD PROJECTION p_agg
    (
        SELECT 
            toStartOfHour(datetime) AS hour,
            domain,
            sum(bytes),
            avg(duration)
        GROUP BY 
            hour,
            domain
    )

Query id: b90da0ab-550d-4a99-9f64-184b9eb06f37

Ok.

0 rows in set. Elapsed: 0.003 sec. 

milovidov-desktop :) ALTER TABLE video_log MATERIALIZE PROJECTION p_agg settings mutations_sync=1;

ALTER TABLE video_log
    MATERIALIZE PROJECTION p_agg
SETTINGS mutations_sync = 1

Query id: 8fb48891-5cb4-4434-9e20-88cae17afd8f

Ok.

0 rows in set. Elapsed: 0.103 sec. 

milovidov-desktop :) SELECT avg_duration1, avg_duration1 = avg_duration2 FROM (SELECT sum(bytes), hour, toStartOfHour(datetime) AS hour, avg(duration) AS avg_duration1 FROM video_log GROUP BY hour WITH ROLLUP WITH TOTALS) LEFT JOIN (SELECT hour, sum_bytes AS sum_bytes2, avg_duration AS avg_duration2 FROM video_log_result__fuzz_0) USING (hour) SETTINGS joined_subquery_requires_alias = 0

SELECT
    avg_duration1,
    avg_duration1 = avg_duration2
FROM
(
    SELECT
        sum(bytes),
        hour,
        toStartOfHour(datetime) AS hour,
        avg(duration) AS avg_duration1
    FROM video_log
    GROUP BY hour
        WITH ROLLUP
        WITH TOTALS
)
LEFT JOIN
(
    SELECT
        hour,
        sum_bytes AS sum_bytes2,
        avg_duration AS avg_duration2
    FROM video_log_result__fuzz_0
) USING (hour)
SETTINGS joined_subquery_requires_alias = 0

Query id: 44d6d293-55e0-4cce-a28a-9b08fa1b7b5a


0 rows in set. Elapsed: 0.004 sec. 

Received exception:
Code: 49. DB::Exception: Block structure mismatch in QueryPipeline stream: different number of columns:
__grouping_set UInt64 UInt64(size = 0), toStartOfHour(datetime) DateTime UInt32(size = 0), avg(duration) Float64 Float64(size = 0)
__grouping_set UInt64 UInt64(size = 0), toStartOfHour(datetime) DateTime UInt32(size = 0), sum(bytes) AggregateFunction(sum, UInt64) AggregateFunction(size = 0), avg(duration) Float64 Float64(size = 0). (LOGICAL_ERROR)