What column is this alias referring to?
Ok, but the column "item_cnt" does not exist in the tables, then what column is it serving as an alias?


The column item_cnt may not exist in the base tables (we believe you, we didn't go and search the linked documents.)

But this part of the code:

FROM 
     (SELECT product_id, COUNT (*) item_cnt
      FROM order_items
      GROUP BY product_id) i


provides a derived table. The column exists during the time that the query runs, in the derived table i. The (SELECT product_id, ... item_cnt ...) AS i defines a derived table, it names it i and this derived table has two columns, product_id and item_cnt (and item_cnt is an alias for the expression COUNT(*). There is no count(*) column either in any of the tables!)

So, later in the code, you can use them. The i.product_id is used in the ON condition and the i.item_cnt in the SELECT list. 

(And it's a bit confusing that it seems to appear earlier and not later. That's because the SELECT part of a query is logically executed after the FROM part.)

FROM (...) i RIGHT OUTER JOIN products p ON ... 


For the query execution, the two tables (aliased i and p) have no difference. It doesn't matter that one is a base table and the other a derived table. They can be used the same way. (and as far as we know, the products may not be a base table either. It can very well be a viewed table, usually referred as a view.)