Why is the SQL syntax so unnecessarily convoluted? SQL is already an operator language, just an overly constrained one due to historical baggage. If you're going to allow new syntax at all, you can just do

  from customer
  left join orders on c_custkey = o_custkey and o_comment not like '%unusual%'
  group by c_custkey
  alias count(o_orderkey) as count_of_orders
  group by count_of_orders
  alias count(*) as count_of_customers
  order by count_of_customers desc
  select count_of_customers, count_of_orders;
  

I'm using 'alias' here as a strawman keyword for what the slide deck calls a free-standing 'as' operator because you can't reuse that keyword, it makes the grammar a mess.

The aliases aren't really necessary, you could just write the last line as 'select count(count(*)) ncust, count(*) nord' if you aren't afraid of nested aggregations, and if you are you'll never understand window functions, soo...

The |> syntax adds visual noise without expressive power, and the novelty 'aggregate'/'call' operators are weird special-case syntax for something that isn't that complex in the first place.

The implicit projection is unnecessary too, for the same reason any decent SQL linter will flag an ambiguous 'select *'