Comment by orthoxerox

This is what I'm most interested in. This is where SQL becomes very awkward and repetitive.

  select
    customer,
    ( select sum(revenue) from orders where customer=accounts.customer) as revenue,
    ( select count(transactionid) from orders where customer=accounts.customer) as orders
  from
    accounts;

That's a very simple example, but it can get VERY wordy and complex, and in my dreams I'd be able to write something like:

  getrevenue(cust) is select sum(revenue) from orders where customer=cust
  getorders(cust) is select count(transactionid) from orders where customer=cust

  select
    customer,
    getrevenue(customer),
    getorders(customer)
  from
    accounts;

In a way, it's just dynamic sql without hacking strings together in unmaintainable ways.

My SQL life is seemingly dominated by correlated subqueries and self-joins where the join checks another row does _not_ exist (the row R with the largest column X is the one where there is no row R1 with a larger X1).

I don't believe SQL queries like these can be improved by a metalanguage. They _can_ be meaningfully improved by indentation, whitespace, careful naming and detailed comments.