the main reason i dont like sql is the way it splits your query into parts that run in a different order and you can only have one of each. thats why you need things like ctes. if it was a more "functional" language with features like let bindings it would be easier to understand (and maybe to optimize):

  from customers as c
  let orders := all(orders where customer_id = c.id)
  select c.name, count(orders), avg(orders.price)