Teradata SQL - WITH - Use a reference of a CTE to another CTE

In Teradata, Common Table Expression (CTE) is supported as other databases. You can create recursive CTE or use a reference of a CTE to another CTE. However there is a slight differences compared with other databases - The referenced CTE must be present after the referencing CTE.

For example, CTE A referencing CTE B, the SQL statements looks like the following in Teradata:

WITH CTEA AS 
(SELECT * FROM CTEB)
, CTEB AS
(SELECT * FROM TABLEB)
SEL * FROM CTEA

In other databases like SQL Server, the sequence of CTEs are reverse:

WITH CTEB AS
(SELECT * FROM TABLEB)
,CTEA AS 
(SELECT * FROM CTEB)
SEL * FROM CTEA

Prerequisites

The tables I'm using are from the following page:

Querying Teradata and SQL Server - Tutorial 0: Preparation

The database schema looks like the following:

Code snippet

WITH 
CTE_DEP as
(
select	dep.*,CTE_AGG.EmployeeCount  
from	TD_MS_SAMPLE_DB.Department dep
left join CTE_AGG
	on CTE_AGG.DepartmentID = dep.DepartmentID
)
,CTE_AGG as
(
select	DepartmentID,count(distinct EmployeeID) as EmployeeCount 
from	TD_MS_SAMPLE_DB.Employee
group by DepartmentID
)
select	* 
from	CTE_DEP;

The output looks like the following screenshot: