TSQL CASE with if comparison in SELECT statement

Please select the same in the outer select. You can't access the alias name in the same query.

SELECT *, (CASE        WHEN articleNumber < 2 THEN 'Ama'        WHEN articleNumber < 5 THEN 'SemiAma'         WHEN articleNumber < 7 THEN 'Good'          WHEN articleNumber < 9 THEN 'Better'         WHEN articleNumber < 12 THEN 'Best'        ELSE 'Outstanding'        END) AS ranking FROM(    SELECT registrationDate, (SELECT COUNT(*) FROM Articles WHERE Articles.userId = Users.userId) as articleNumber,     hobbies, etc...    FROM USERS)x

Should be:

SELECT registrationDate,        (SELECT CASE        WHEN COUNT(*)< 2 THEN 'Ama'        WHEN COUNT(*)< 5 THEN 'SemiAma'         WHEN COUNT(*)< 7 THEN 'Good'          WHEN COUNT(*)< 9 THEN 'Better'         WHEN COUNT(*)< 12 THEN 'Best'        ELSE 'Outstanding'        END as a FROM Articles         WHERE Articles.userId = Users.userId) as ranking,        (SELECT COUNT(*)         FROM Articles         WHERE userId = Users.userId) as articleNumber,hobbies, etc...FROM USERS

You can try with this:

WITH CTE_A As (SELECT COUNT(*) as articleNumber,A.UserID  as UserID FROM Articles A           Inner Join Users U           on  A.userId = U.userId            Group By A.userId , U.userId   ),B as (Select us.registrationDate,      CASE         WHEN CTE_A.articleNumber < 2 THEN 'Ama'         WHEN CTE_A.articleNumber < 5 THEN 'SemiAma'          WHEN CTE_A.articleNumber < 7 THEN 'Good'           WHEN CTE_A.articleNumber < 9 THEN 'Better'          WHEN CTE_A.articleNumber < 12 THEN 'Best'         ELSE 'Outstanding'         END as Ranking,         us.hobbies, etc...         FROM USERS Us Inner Join CTE_A          on CTE_A.UserID=us.UserID)Select * from B