Some call it Subquery Factoring. Others call it Common Table Expression.
In its simplest form, this feature is a kind of "boosted derived table".
Assume that a table T1 has three columns:
Now I want to know the sales trend (increase/decrease), year after year:
CREATE TABLE T1(
YEAR INT, # 2000, 2001, 2002 ...
MONTH INT, # January, February, ...
SALES INT # how much we sold on that month of that year
SELECT D1.YEAR, (CASE WHEN