explain
with t as (select number n from numbers(1000000000)),
t1 as (select n%100 x,count(*)c from t group by x),
t2 as (select toUInt8(x/10)x1,sum(c)c from t1 group by x1)
select * from t1
union all
select * from t2
;
mysql> explain
    -> with t as (select number n from numbers(1000000000)),
    -> t1 as (select n%100 x,count(*)c from t group by x),
    -> t2 as (select toUInt8(x/10)x1,sum(c)c from t1 group by x1)
    -> select * from t1
    -> union all
    -> select * from t2
    -> ;
+-------------------------------------------------------------------------------------+
| explain                                                                             |
+-------------------------------------------------------------------------------------+
| Union                                                                               |
|   Expression ((Projection + (Before ORDER BY + (Projection + Before ORDER BY))))    |
|     Aggregating                                                                     |
|       Expression ((Before GROUP BY + (Projection + Before ORDER BY)))               |
|         SettingQuotaAndLimits (Set limits and quota after reading from storage)     |
|           ReadFromStorage (SystemNumbers)                                           |
|   Expression ((Projection + (Before ORDER BY + (Projection + Before ORDER BY))))    |
|     Aggregating                                                                     |
|       Expression ((Before GROUP BY + (Projection + Before ORDER BY)))               |
|         Aggregating                                                                 |
|           Expression ((Before GROUP BY + (Projection + Before ORDER BY)))           |
|             SettingQuotaAndLimits (Set limits and quota after reading from storage) |
|               ReadFromStorage (SystemNumbers)                                       |
+-------------------------------------------------------------------------------------+
13 rows in set (0.00 sec)
Read 13 rows, 790.00 B in 0.00228172 sec., 5697 rows/sec., 338.12 KiB/sec.


explain
with t as (select number n from numbers(1000000000)),
t1 as (select n%100 x,count(*)c from t group by x),
t2 as (select toUInt8(x/10)x1,sum(c)c from t1 group by x1)
select * from t2;

mysql> explain
    -> with t as (select number n from numbers(1000000000)),
    -> t1 as (select n%100 x,count(*)c from t group by x),
    -> t2 as (select toUInt8(x/10)x1,sum(c)c from t1 group by x1)
    -> select * from t2;
+-----------------------------------------------------------------------------------+
| explain                                                                           |
+-----------------------------------------------------------------------------------+
| Expression ((Projection + (Before ORDER BY + (Projection + Before ORDER BY))))    |
|   Aggregating                                                                     |
|     Expression ((Before GROUP BY + (Projection + Before ORDER BY)))               |
|       Aggregating                                                                 |
|         Expression ((Before GROUP BY + (Projection + Before ORDER BY)))           |
|           SettingQuotaAndLimits (Set limits and quota after reading from storage) |
|             ReadFromStorage (SystemNumbers)                                       |
+-----------------------------------------------------------------------------------+
7 rows in set (0.00 sec)
Read 7 rows, 433.00 B in 0.00177425 sec., 3945 rows/sec., 238.33 KiB/sec.



with t as (select number n from numbers(1000000000)),
t1 as (select n%100 x,count(*)c from t group by x),
t2 as (select toUInt8(x/10)x1,sum(c)c from t1 group by x1)
select * from t2;

with t as (select number n from numbers(1000000000)),
t1 as (select n%100 x,count(*)c from t group by x),
t2 as (select toUInt8(x/10)x1,sum(c)c from t1 group by x1)
select * from t1
union all
select * from t2
;


mysql> with t as (select number n from numbers(1000000000)),
    -> t1 as (select n%100 x,count(*)c from t group by x),
    -> t2 as (select toUInt8(x/10)x1,sum(c)c from t1 group by x1)
    -> select * from t2;
+------+-----------+
| x1   | c         |
+------+-----------+
|    0 | 100000000 |
|    1 | 100000000 |
|    2 | 100000000 |
|    3 | 100000000 |
|    4 | 100000000 |
|    5 | 100000000 |
|    6 | 100000000 |
|    7 | 100000000 |
|    8 | 100000000 |
|    9 | 100000000 |
+------+-----------+
10 rows in set (3.28 sec)
Read 1000064835 rows, 7.45 GiB in 3.27126856 sec., 305711627 rows/sec., 2.28 GiB/sec.



mysql> with t as (select number n from numbers(1000000000)),
    -> t1 as (select n%100 x,count(*)c from t group by x),
    -> t2 as (select toUInt8(x/10)x1,sum(c)c from t1 group by x1)
    -> select * from t1
    -> union all
    -> select * from t2
    -> ;
+------+-----------+
| x    | c         |
+------+-----------+
|    0 | 100000000 |
|    1 | 100000000 |
|    2 | 100000000 |
|    3 | 100000000 |
|    4 | 100000000 |
|    5 | 100000000 |
|    6 | 100000000 |
|    7 | 100000000 |
|    8 | 100000000 |
|    9 | 100000000 |
|    0 |  10000000 |
|    1 |  10000000 |
... 
... 
|   93 |  10000000 |
|   94 |  10000000 |
|   95 |  10000000 |
|   96 |  10000000 |
|   97 |  10000000 |
|   98 |  10000000 |
|   99 |  10000000 |
+------+-----------+
110 rows in set (3.33 sec)
Read 2000129670 rows, 14.90 GiB in 3.32391943 sec., 601738312 rows/sec., 4.48 GiB/sec.

mysql>