I can't decide if it would be better or worse if it stuck more closely to SQL keywords. You use "from" and "select", but not "where", "order by", "group by". There's some danger of it being in an uncanny valley of SQLish, but I'm pretty sure I'd prefer just using those terms verbatim (including the space in "order by"... that style is less common in modern languages but it's not really that much harder to parse).

I'd like to see more examples of joins and composing SQL. Does this language make it easier to make more general SQL queries? Can I take two queries and squash them together in a reliable way? I feel like I end up with a lot of theme and variation in my queries, often involving optional filters.

I might even like a notion of encapsulation that could help this query language when it's embedded in other languages. Like if I could say, in the language itself, that a query has certain unbound variables (and not just ? or other placeholders). This language seems like it would be better for generating than SQL, and sometimes generation is just necessary (like in any application that supports data exploration), but for most common cases I'd hope to avoid that. Defining inputs and then making whole filter sections or other statements conditional on those inputs would help here.

Specific things I'd like to see.

How do you handle column ambiguity. In the examples, they show a join of positions to employee on employee_id == id. But what happens when you have 2 columns with the same name that you are joining on? (like employee_id to employee_id in some mapping table).

Subqueries are pretty important in what I do, so what do those look like (perhaps covered by the "thinking about CTEs section").

How about opportunities for optimization hints? In T-SQL you can hint at which index the optimizer should prefer to a specific query.

Common SQL patterns would also be interesting. Like, how would you do keyset pagination?

Edit: Also, I'd like a discussion about null. SQL null handling rules are terrible. I understand them, I work with them, but at the same time, they are so different from other languages concept of "null" that they are easy to trip over.

  > SQL null handling rules are terrible. I understand them, I work with them, but at
  > the same time, they are so different from other languages concept of "null" that
  > they are easy to trip over.

Thanks.

Additionally "unknown value" concept is not used consistently. Things like DISTINCT, or UNIQUE indexes (in some databases) treat NULL as single "no value".

  > Existence of such value introduces three value logic because expression "NULL = <anything>" is neither true nor false.

  > select if(null=null, "Yes", "No")
  > +----------------------------+
  > | No                         |
  > +----------------------------+

With Postgres:

  postgres=# \pset null <null>
  postgres=# select null, null=null, null=42;
   ?column? | ?column? | ?column?
  ----------+----------+----------
   <null>   | <null>   | <null>
  (1 row)

  > mysql> select 42=42,42=314,42=null,null=null;
  > +-------+--------+---------+-----------+
  > | 42=42 | 42=314 | 42=null | null=null |
  > +-------+--------+---------+-----------+
  > |     1 |      0 |    NULL |      NULL |
  > +-------+--------+---------+-----------+

Instead, I think other languages away from the database are being referred to - in many of those NULL is treated like any other value², for instance in Javascript¹ null==null is true and null!=null is false, and due to type coercion null on its own is “falsey”. Personally I have no problem with SQLs handling of NULL with one exception, and find other languages treating it as a single value rather than an unknown feels odd.

The one thing that I have occasionally tripped over with NULL in SQL is the effect of “<val-or-var> NOT IN (<set>)” when NULL is one of the entries in <set> - it makes sense when you think about it because the IN operator can only return true or false and it can't definitively say the searched for value isn't equal to the unknown one(s)³ but this doesn't seem intuitive.

Some SQL dialects do handle NULL a little differently, more like languages like JS. MS SQL Server can be made to with SET ANSI_NULLS OFF to force its ancient not-standards-compliant behaviour⁴.

[1] quick & easy to test in your browser's console

[2] well, technically in JS I think null is specifically a null object reference, that being one of the differences between null and undefined

[3] more concretely, “var NOT IN (1, 2, NULL)” being equivalent to “var<>1 AND var<>2 AND var<>NULL” which becomes “true AND true AND NULL” which is NULL as any logical operator against NULL returns NULL.

[4] though note that this option is officially deprecated, as of at least 2016, and might be removed or just ignored in future versions

  > The one thing that I have occasionally tripped over...this doesn't seem intuitive.

  > select 'true' from dual where 3 not in (1, 2, null)
  > Empty set (0.00 sec)

Similarly (except the result is a boolean),

  $ deno eval --print 'NaN == NaN'
  false

I just fleshed out composing CTEs, which is a small step towards the broader goal of making composition easier: https://github.com/max-sixty/prql/commit/dc68fcaaceef26cc078...

Let me know if you have a good case of the sort of composition you find difficult in SQL (either here or in an issue). Thank you!

Another problem that I have with composing SQL is that large queries quickly become unreadable, and error messages are also often not terribly helpful. I think having a more expressive type system would help with the error messages. Do you have any plans on adding a type system to PRQL?

That's essentially what SQL views do. Each view is a query and then you can treat it like a table and filter/join on it.

Of course, then the problem becomes whether or not the query planner can see through the view to the underlying tables to optimize correctly.

I agree 100% here. As a SQL veteran, it would make the transition a lot easier if you used common SQL keywords like group by, order by, limit, etc. e.g.

    from employees
    where country = "USA"
    derive [
      gross_salary: salary + payroll_tax,
      gross_cost:   gross_salary + benefits_cost
    ]           
    where gross_cost > 0
    group by:[title, country] [
        average salary,
        sum     salary,
        average gross_salary,
        sum     gross_salary,
        average gross_cost,
        sum_gross_cost: sum gross_cost,
        count,
    ]
    order by:sum_gross_cost
    where count > 200
    limit 20

My main suggestion would be to be a bit less terse and introduce a bit more firm formatting. I'm not a huge fan of the term "split" and feel like jazzing that up to "split over" or even just reviving "group by" would improve readability. Additionally the aliasing could use work, I'd suggest reversing the assignment to be something closer to `use salary + payroll_tax as gross salary`. In terms of firm formatting, unless I'm missing something there isn't any reason to allow a filter statement before any aliases - so you can force two fixed positions for filter clauses which would make it always legal to reference aliases in filters.

On the brief topic of form vs. flexibility. SQL is a thing that, when complex, is written by many people over the course of its lifetime - removing the ability to make bad decisions is better than enabling the ability to write simple things even simpler - those silly do nothing queries like "SELECT * FROM customer WHERE deleted='f'` are written once[1] in a moments time and never inspected again. The complex queries are what you want to optimize for.

1. If they even are - with ORMs available a lot of those dead simple queries just end up being done through an ORM.

Hallelujah! But, to your footnote, this is a major reason why I despise ORMs. In my mind they make writing simple code slightly easier, but they make complicated SQL statements, especially when you get some weird issue under load and you're trying to debug why your DB is falling over, a ton more difficult and you spend so much time just battling your ORM.

Like fetching a record by id, or a single record and all of its related properties. Or a list of all the record in a table matching a simple filter.

That’s 98% of what we do against the DB, and I’m all for having it basically invisible.

Then let’s just bypass the ORM altogether the minute we think about joining or grouping things together. There are libs in most language that help just sanitize queries, so it’s no difficult really.

A common pattern seems to be over engineering these simple scenarios though. Someone decides that embedded sql is evil and needs to be extracted out of normal code, often to stored procs. Then these simple queries have enough friction that an ORM starts to look good, then you end up with an ORM generating simple queries dynamically in the same place that used to have a simple embedded string.

There can be part of an app where a very bad query here and there is not important, but more often than not it creeps up in key parts of the user experience, and it becomes very hard to untangle when it becomes something important enough to thoroughly optimize, but also complex enough that the existent tests only cover a tiny portion of the important use cases (if you're reusing a bunch of query bits, you're probably working with a wide combination of input/outputs). I've seen literally weeks spent on trying to optimize ORM chained subqueries.

Of course, I've not seen every query in existence so it's more than possible you've seen bad SQL from an ORM, but the untangling part would again fall to those skilled in the language of the ORM - unless the ORM can't produce efficient SQL in a particular case. And just as it would if the query was originally written in SQL, you'd need someone skilled in SQL to untangle that.

What would that case (where an ORM cannot produce efficient SQL) look like?

In general ORM queries become ugly at three to four levels of joins and/or excluding under non trivial conditions (e.g. finding users that have not participated to a specific set of events). They will spit out something that works, but will take a few orders of magnitude more than an optimized query.

As you say, there is the option to play jenga with the ORM code to hit the right combination that produces a better output. But that feels like teaching a toddler to solve a puzzle that you already solved and are keeping the cheat sheet in your pocket. I personally don't see the beauty of it and would prefer to directly use the right SQL and call it a day.

On people skilled in SQL, you should have a few onboard anyway if you're doing more than basic CRUD on the DB, and it's easier to find than ORM gurus IMHO.

I think that's fair enough, there are enough ways to do things now that it should be possible to accommodate both.

> On people skilled in SQL, you should have a few onboard anyway if you're doing more than basic CRUD on the DB, and it's easier to find than ORM gurus IMHO.

I agree but I'm not sure there are more SQL gurus than those used to ORMs nowadays. Lately I've favoured using SQL but even 15 years ago most devs I knew couldn't use it well, I can't see devs used to Rails et al having the chops for it, sadly. What was once convenient easily becomes one's master.

> I haven't got the chance yo try Sequel

If you get the chance, I think it's worth it. It's easy to drop into plain SQL without dumping the ORM, and I've never had a problem with the stuff it generates. It's a pity ActiveRecord gets all the love instead.

I work on a lot of smaller IT projects for SME's, internal tools and platforms that are made on a small budget and, thus, end up having a tight deadline in order to not go over budget.

The vast majority of these projects are versions of CRUD apps for this and that, we tend to value readable code over performant hard-to-read code as it makes code review faster.

Things go down when a query that used to take 4s now takes 30s as the product has taken off, handles 10x more data, and it’s not one user but a few hundreds having their request queued.

You wont have the luxury to not re-write that part in (probably) hard to read performant code. It can be a huge enough effort to blow away your deadlines and budget and sour pretty hard the relationship if your team struggles on something they aren’t used to do at all.

  > Isn’t it more important that the query you write with the ORM is readable than the underlying SQL it spits out?

And I agree with you on both the assignments and `split` being a bit awkward. Kusto just uses `by`, WDYT?

[1] https://github.com/max-sixty/prql/commit/dde7fcfc13daaadbdce...

IMO for such a small adjustment the benefits don't outweight the costs.

Maybe I'm just too used to non-standard extensions of our database but the SQL example could, at least for our db, be rewritten as

    SELECT TOP 20
        title,
        country,
        AVG(salary) AS average_salary,
        SUM(salary) AS sum_salary,
        AVG(gross_salary) AS average_gross_salary,
        SUM(gross_salary) AS sum_gross_salary,
        AVG(gross_cost) AS average_gross_cost,
        SUM(gross_cost) AS sum_gross_cost,
        COUNT(*) as count
    FROM (
        SELECT
            title,
            country,
            salary,
            (salary + payroll_tax) AS gross_salary,
            (salary + payroll_tax + healthcare_cost) AS gross_cost
        FROM employees
        WHERE country = 'USA'
    ) emp
    WHERE gross_cost > 0
    GROUP BY title, country
    ORDER BY sum_gross_cost
    HAVING count > 200

Still, think the PRQL looks very nice, especially with a "let" keyword as mentioned in another thread here.

[1]: https://news.ycombinator.com/item?id=30053860

  with usa_employees as (
    SELECT
            title,
            country,
            salary,
            (salary + payroll_tax)                   AS gross_salary,
            (salary + payroll_tax + healthcare_cost) AS gross_cost
    FROM  employees
    WHERE country = 'USA'
    AND   (salary + payroll_tax + healthcare_cost) > 0
  )
  select  title,
        country,
        AVG(salary)         AS average_salary,
        SUM(salary)         AS sum_salary,
        AVG(gross_salary)   AS average_gross_salary,
        SUM(gross_salary)   AS sum_gross_salary,
        AVG(gross_cost)     AS average_gross_cost,
        SUM(gross_cost)     AS sum_gross_cost,
        COUNT(*) as emp_count
  from      usa_employees
  group by  title, country
  having    count(*) > 200
  order by  sum_gross_cost
  limit 3

I just look at something like this and I immediately know what's going on. If it's nested sub queries it always takes me much longer.

For very complicated stuff, SQL does become very hard to read compared to e.g. tidyverse + targets in R.

    select
        title,
        country,
        avg(salary)         as average_salary,
        sum(salary)         as sum_salary,
        avg(gross_salary)   as average_gross_salary,
        sum(gross_salary)   as sum_gross_salary,
        avg(gross_cost)     as average_gross_cost,
        sum(gross_cost)     as sum_gross_cost,
        count(*)            as emp_count
    from
        employees,
        lateral ( select
            (salary + payroll_tax)                   as gross_salary,
            (salary + payroll_tax + healthcare_cost) as gross_cost
        ) employee_ext
    where
        country = 'usa'
        and gross_cost > 0
    group by  title, country
    having    count(*) > 200
    order by  sum_gross_cost
    limit 3;

It's also weird how people always argue for immutability and eliminating local state, when using procedural languages, but as soon as they switch to SQL, that actually works like this, they immediately want to introduce mutability and local state.

I don't think anyone is using the above examples to try invalidate PRSQL, just suggesting the baseline for comparisons should account for all constructs available in the SQL standards and common implementations there-of.

> Or what am I missing.

The statement “I can do X better than <SQL example> with <something else>” does not properly show the benefit of <something else> if “I can do X better than <SQL example> with <another SQL example>” is also true (assuming <another SQL example> is actually agreed to be better, not for instance convoluted/confusing/long-winded/other so just replacing some problems with others).

There's the slight issue of shadowing of table column names, which they resolve by preferring columns to aliases if both are named the same. So sometimes my aliases end up prefixed with underscores, but that's not a big deal.

Favoring aliases over columns instead has the potential to introduce irresolvable ambiguities as you can’t “qualify” a column alias with a SELECT list or subquery ID the way you can qualify a column by its table/view alias.

The DB we use supports those, I just learned about them too late so keep forgetting they exist :(

> It would really help in SQL if you could refer to column aliases so you don't have to repeat the expression.

The DB we use supports that, so in your CTE you could write

   AND   gross_cost > 0

https://paquier.xyz/postgresql-2/postgres-12-with-materializ...

Today is a great day to have been wrong on the Internet. :)

> (salary + payroll_tax + healthcare_cost) AS gross_cost

> AND (salary + payroll_tax + healthcare_cost) > 0

And his is a simple example.

    select top(20)
        title,
        country,
        ...
        avg(gross_salary) as average_gross_salary,
        ...
    from employees
    cross apply ( select
        gross_salary = employees.salary + employees.payroll_tax, -- or "as .."
        gross_cost = ...
    ) v -- some name required but don't need to use it if column names are unique
    where ...

1. Did you consider using a keyword like `let` for column declarations, e.g. `let gross_salary = salary + payroll_tax` instead of just `gross_salary = salary + payroll_tax`? It's nice to be able to scan for keywords along the left side of the window, even if it's a bit more verbose.

2. How does it handle the pattern where you create two moderately complex CTEs or subqueries (maybe aggregated to different levels of granularity) and then join them to each other? I always found that pattern awkward to deal with in dplyr - you have to either assign one of the "subquery" results to a separate dataframe or parenthesize that logic in the middle of a bigger pipeline. Maybe table-returning functions would be a clean way to handle this?

> Did you consider using a keyword like `let` for column declarations

Yeah, the current design for that is not nice. Good point re the keyword scanning. I actually listed `let` as an option in the notes section. Kusto uses `extend`; dplyr uses `mutate`; pandas uses `assign`.

I opened an issue here: https://github.com/max-sixty/prql/issues/2

Another suggestion around `let`: consider splitting it into two operations, for creating a new column and for modifying an existing one. E.g. called `let` and `set`. Those are in effect pretty different operations: you need to know which one is happening to know how many columns the table will have, and renaming a table column can with your current system change which operation is happening.

Splitting them into separate operations would make things easier on the reader: they can tell what's happening without having to know all the column names of the table. And it shouldn't really be harder for the writer, who ought to already know which they're doing.

I encountered something like this at my previous job. We had a DSL with an operation that could either create or modify a value. This made the code harder to read, because you had to have extra state in your head to know what the code was doing. When I rewrote the DSL (the rewrite was sorely needed for other reasons), I split the operation in two. I was worried people would have been too used to the old language, but in practice everyone was happy with it.

This could _mostly_ be enforced by PRQL. There's a case where we transpile to:

  select *, x+1 as x_plus_one

I opened an issue here: https://github.com/max-sixty/prql/issues/6

Couldn’t we just not allow modifying an existing column? Ie. we would not allow

  count = count + 1

  new_count = count + 1

I don't have an example on the Readme, but I was thinking of something like (toy example):

  table newest_employees = (
    from employees
    sort tenure
    take 50
  )
  
  from newest_employees
  join salary [id]
  select [name, salary]

Edit: formatting

There, each variable can be referenced by downstream steps. Generally, the prior step is referenced. Without table variables, your language implicitly pipes the most recent one. With table references, you can explicitly pipe any prior one. That way, you can reference multiple prior steps for a join step.

I haven't thought through that fully, so there may be gotchas in compiling such an approach down to SQL, but you can already do something similar in SQL CTEs anyway, so it should probably work.

  from employees
  sort tenure
  take 50
  as newest_employees
  
  from newest_employees
  join salary [id]
  select [name, salary]

Let me know any feedback — as you can see it's still at the proposal stage. If it gains some traction I'll write an implementation.

I guess the biggest difference between FunSQL (and similarly dbplyr) and PRQL is that the former needs a Julia (or R) runtime to run.

I really respect the library and keen to see how it develops.

I have re-implemented parts of FunSQL in Python and OCaml (the one I have ended up using) and have added a concrete syntax similar to what you have in PRQL.

    from employees
    define
      salary + payroll_tax as gross_salary,
      gross_salary + benefits_cost as gross_cost
    where gross_cost > 0 and country = 'usa'
    group by title, country
    select
      title,
      country,
      avg(salary) as average_salary,
      sum(salary) as sum_salary,
      avg(gross_salary) as average_gross_salary,
      sum(gross_salary) as sum_gross_salary,
      avg(gross_cost) as average_gross_cost,
      sum(gross_cost) as sum_gross_cost,
      count() as count
    order by sum_gross_cost
    where count > 200
    limit 20

    from users as u
    join (from comments group by user_id) as c on c.user_id = u.id
    select
      u.username,
      c.count() as comment_count,
      c.max(created_date) as comment_last_created_date

I think this approach is very powerful as it allows you to build reusable query fragments in isolation but then combine them into a single query which fully specifies what's being selected.

I found the "# `|` can be used rather than newlines." a bit odd. So when using let, you're only transforming one column? I think the example would look weird with returns instead of |.

Depending on your intended target, it might help adoption if you stay closer to the naming conventions of that target. If you're targeting mainstream Java/Python/C#/Javascript etc. then functions need parentheses, "take 20" may be worse than slice, etc.

I think annotating microversions would get tiresome fast. I think the right way to think of this is that you put in a single version number like 1, and then only ever change that if you need to do backwards-compatible changes that cannot be handled by clever hacks in the runtime.

Also I think you should try writing one or more native wrappers in your intended target languages to make sure it's easy to interface between the two, even if it means you'd have to use dots in that language.

I could imagine an end game where the ergonomics were so good that a database like Postgres ends up with a native PRQL frontend. Not sure you're there yet, though. IMHO SQL as a query language suffers from a) sometimes really bad ergonomics, b) it's hard to wrap in another programming language (also ergonomics), c) it has far too many concepts - it's not orthogonal.

Thanks good idea, I just changed this to remove the microversions. If we use SemVer, then before `1`, we'd hold versions compatible to the 0.X, and then to the X.

Also want "OFFSET 20" from PG.

Some programmers like you see that SQL ordering is backwards to human thinking, except in the simplest cases. But many people with practice and sunk costs in their SQL expertise will be resistant. The resistance usually wins the day.

But sometimes, a useful tool gets created by one person, and a rift is created in that resistance. Think John Resig creating jQuery, leading to LINQ and many other similar patterns. You could be that person for database query languages, but how do you ensure that?

Maybe imagine what made jQuery easy to adopt and indispensable for programmers: easy availability as a simple .js download; solved the problem of DOM differences between browsers. Good luck to you, and thanks for sharing.

And then dump the queries via https://stackoverflow.com/questions/1412863/how-do-i-view-th... or https://www.linqpad.net/ ?

My only gripe is the 'auto-generated' column names for aggregates. This seems like a recipe for disaster - what if there is already (as there almost certainly will be) named "sum_gross_cost"? The behavior also just seems rather unexpected and implicit. My suggestion would be simple syntax that lets you optionally give a name to a particular aggregate column:

    ...
    filter gross_cost > 0
    aggregate by:[title, country] [
        average salary,
        sum gross_salary,
        average gross_cost,
        let sum_gc = sum gross_cost,
        count,
    ]
    sort sum_gc

For what it's worth, a similar problem already exists with SQL. Something simple like

  select count(*) from my_table;

In practice, I don't think this is a major problem.

I'm not sure whether we should force naming? When I'm writing a query often I'm fine with something auto-generated when starting out.

I want to understand what exactly the query returns, not the implementation detail of the source of this data (that can later be changed).

Literally first example from page - I have no idea what is being returned:

    from employees
     filter country = "USA"                           # Each line transforms the previous result.
     let gross_salary = salary + payroll_tax          # This _adds_ a column / variable.
     let gross_cost   = gross_salary + benefits_cost  # Variables can use other variables.
     filter gross_cost > 0
     aggregate by:[title, country] [                  # `by` are the columns to group by.
          average salary,                              # These are the calcs to run on the groups.
          sum     salary,
          average gross_salary,
          sum     gross_salary,
          average gross_cost,
          sum     gross_cost,
          count,
 ]
     sort sum_gross_cost                              # Uses the auto-generated column name.
     filter count > 200
     take 20

    SELECT id, name, author

And the auto complete story is backwards. Often I know what columns I want, but I'm not clear what table I need to get them from. Such that, if you make a smarter suggest in the from to only include tables that have the columns, I'd be much happier.

I further assert that if your database is filled with "Id" and "name" columns, instead of "department_name" and similar, you are probably as likely to mess up a join as any benefit you get from the name being short. (And really, what advantage is there in short names nowadays?)

That all said. I worded my take too strongly. My point should have been that auto suggest should not be confined in either direction.

When I'm trying to query stuff, the first question is "which service's database is that in?", so I can guess "user_service" (or whatever I think it is called), but I have no idea what they call anything in their schema, but now that the autocomplete system knows what table I'm interested in, it can help me figure that out.

That said, I want to be clear that I think both methods are valid and work.

I can think back to projects / companies where this may have been different, basically just with fewer different distinct schemas, but it just isn't my recent experience.

(I appreciate your magnanimity in these later comments by the way.)

> The table is the context: you can't change that without having to change everything else.

Except even in the provided single-table example this isn't true - you're getting subselected/CTEd results. No functional joins are demonstrated unfortunately.

For example:

   from employees
   left_join positions [id=employee_id]

   ...is equivalent to...

   SELECT * FROM employees LEFT JOIN positions ON id = employee_id

    FROM articles

You're arguing that:

    FROM articles
    SELECT id, name, author

    SELECT id, name, author
    FROM articles

I think what this boils down to is what you (any reader, not you specifically) individually expect to need knowledge of when you're writing SQL. In most cases when sitting down to write a brand new piece of code to pull some data from the database, putting the list of tables involved in the query first matters most to some, whereas putting the list of fields to expect in the resultset matters most to others (I put myself in this camp).

For what it's worth, as I've stated elsewhere while I don't prefer it and would find it annoying to debug personally, I do recognize that the idea of SQL that allows you to list FROM / JOIN / etc. first is very appealing to some. What I think is completely off is the near-obfuscation of the examples in the linked article.

Of course not, because we're talking about a fundamental change in syntax that affects more than just two-line queries. PRQL puts the SELECT clause practically at the end of the query, after every join, filter, and aggregate. If you only care about the output, that's cool too: just look at the end. But if I want to understand a query in SQL, I have to read the entire thing backwards, clause by clause! By contrast, if the SELECT is at the end, that's not much of a problem.

Now you'll come back and say "but it's the same if you care about context: just look at the end!" And that's where we differ. I care more about writing, debugging, and understanding queries, whereas you think it's more important that the column names are up front, even if it makes understanding a nontrivial query much, much harder.

If you want an interesting example of how a query language built for developer experience and autocompletions looks definitely check it out!.

1. What tables are being pulled from? This speaks to the potential domain of the query. 2. What data is being selected (I can now know what is or isn't being pulled from the aforementioned tables...) 3. What operations, aggregations, groupings, etc. are being performed to work on the pulle data

Of course from vs select ordering is completely arguable, but my thinking process seems to follow that of the auto complete--in other words that my cognitive load of looking at the select statement is lessened when I know from what the columns are being selected.

It also follows (at least to me) the mental process of writing the query. First look at the tables, then decide what columns, then decide what functions to apply.

I understand this is just an opinion but it's an opinion held by everyone in my org who writes both.

Theoretical correctness loses to pragmatism a lot and I'd read the KQL every day. Look at the examples at https://docs.microsoft.com/en-us/azure/data-explorer/kusto/q... - look at the examples at https://docs.microsoft.com/en-us/azure/data-explorer/write-q... and tell me they're not more readable than comparable SQL?

(I can see the result type both by hovering on the query but also by just looking at the end of it - and in SQL most of the SELECTed items in complex queries are from subqueries anyway - at least in my use case)

If you want nice autocomplete too, that's fine, but if there is a tradeoff, human understanding is the primary concern.

C#'s LINQ (really powerful tool similar to SQL) works the same way

look:

var list = new List<int>{1,2,3}

var extracted = list

.............................Where(x => x > 1)

.............................Select(x => $"my number: {x})

.............................ToList();

or

var extarcted =

........................from x in list

........................where x > 1

........................select $"my number: {x};

Toilets are absolutely designed to make the dispenser placement convenient. You just don’t think about it because 95% of toilets get it right, so it just doesn’t bother you that much that it can be wrong.

In SQL, some decisions are right about 10% of the time and are annoying and awkward the other 90%.

That’s why the order matters. Because everything else got it right.

Then, the ide could basically fill my from out for me, based on what I'm asking for. Can even suggest what join I will need, if I list columns from multiple tables.

Every table has more than one column

So there's always more columns to remember than tables and generally tables are pretty easy like user invoices blabla

I worked with systems that had like 500 tables and some of them with 20-50 columns

you really want good intellisense in such a environment

Good intelligent suggestions is, of course, helpful. And I agree that suggesting one of 500 is easier than the other. That said, neither is hard for a computer. And even asking friends what table I want will often be done with starting with the actual columns I want.

if you start query with

SELECT MiddleName then you could receive auto complete thats adds "FROM Users" and moves your cusor after MiddleName.

if you start query with

FROM Users SELECT _ and know intellisense drops list of columns

Where is the data coming from? Employees table. What's coming out? 20 rows of sum_gross_cost.

What could improve this is function signatures. It's kind of nice to have the whole abstraction up top...like an abstract.

    from @@special
    select version

    from dual
    select @@version

One idea would be along the lines of a function prototype: a declaration, up front, about the columns and types that a query is expected to return. It's a good place to put documentation, it's redundant information which should protect against mistakes but not so redundant that it would be too taxing - the author should know what the query returns. The prototype would only be used for validation of column names and types.

Another idea would be requiring the last element in a query to be a projection, a bit like the return statement in a function body: here's what I'm returning out of the grand set of symbols available (e.g. via various joins) in scope from previous operations in the flow.

Reading the comment however, it would seem that `let` adds columns which are implicitly returned in the order they are defined.

I do see benefits in this, and can imagine pitfalls. Hard to judge without kicking the tires.

Update: It's quite possible we saw different syntax!

https://news.ycombinator.com/item?id=30063266

Without the `let` I would imagine having trouble reading it as well, I'm not sure if that would go away with familiarity but my instinct is that it's a useful addition.

While it inherently feels unnatural I do agree with the others here that the context is actually easier to understand once over the initial uncomfort.

In SQL, where _from_ is placed at the end, we are essentially writing equivalent of 'property.object'; eg.: name.person, age.person

To your point - a solid IDE will show you what's being processed at each line (or returned, if the cursor is on the last line) - in an autocomplete window or a side panel.

Second, this kind of reversed SQL (filter-first, select-last) is much easier to reason about than the original and keep in mind that I prefer to code complex queries in SQL than to build or translate them in the ORM of the project I'm working on.

Maybe a transpiler is an inevitable first step but I think that any SQL replacement should be itself the target of ORMs and run directly in the database CLI tools (psql / mysql ...) or IDEs (pgAdmin, MySQLAdmin, ...). What's the long term plan of the project?

Given that SQL clauses tend to be unambiguously terminated by the start of the next clause or the end of the statement, it surprises me that no engine has gone to accepting otherwise standard(-ish, as much as real DB vendor dialects are) SQL but without a mandated order of clauses.

And then combine that with dev tools that allow easy rearrangement of clauses, perhaps based on configured preferences so that you don’t even see the original if its not your preferred order, so that “Bob likes old-school SELECT FROM WHERE GROUP BY and Alice likes FROM WHERE GROUP BY SELECT” isn’t a problem.

I agree that integrating with the DB would allow much more from a lang. But PRQL is a bet that languages which start there (e.g Kusto) get lost because it requires changing DB, which is really hard. I worry EdgeDB may hit this issue too (but I'm really hoping it works, and they have an excellent team).

As I think you're suggesting — you could imagine a language starting out as a transpiler, and then over time DBs working with it directly, cutting out some of the impediment mismatch.

Malloy [1] is another point in space — it targets existing DBs through SQL queries but can also ask for schemas etc while developing.

[1] https://github.com/looker-open-source/malloy

(I noticed this on the github readme too)

https://www.w3.org/TR/xquery-31/#id-flwor-expressions

And it has grouping, windowing functions etc. I bet you could define a subset that is specifically tailored to the same use cases as SQL - basically, get rid of everything to do with elements and attributes, and only allow scalars and sequences (and maybe maps?). But otherwise keep XDM data types and their semantics.

example: TPC-H National Market Share Query 8 http://www.qdpma.com/tpch/TPCH100_Query_plans.html what market share does supplier.nation BRAZIL have by order.year for order.customer.nation.region AMERICA and part.type STEEL?

real: select revenue avg supplier.nation=`BRAZIL by order.year from t where order.customer.nation.region=`AMERICA, part.type=`STEEL

ansi: select o_year,sum(case when nation = 'BRAZIL' then revenue else 0 end) / sum(revenue) as mkt_share from ( select extract(year from o_orderdate) as o_year, revenue, n2.n_name as nation from t,part,supplier,orders,customer,nation n1,nation n2,region where p_partkey = l_partkey and s_suppkey = l_suppkey and l_orderkey = o_orderkey and o_custkey = c_custkey and c_nationkey = n1.n_nationkey and n1.n_regionkey = r_regionkey and r_name = 'AMERICA' and s_nationkey = n2.n_nationkey and o_orderdate between date '1995-01-01' and date '1996-12-31' and p_type = 'STEEL') as all_nations group by o_year order by o_year;

Offhand i thought PRQL seemed easier to reason about, but something about EdgeQL seems better to me.. though i can't describe it.

I wrote the PRQL proposal for analytical / OLAP queries, where the pipeline of transformations are more important, and relations and typing are relatively less important.

We definitely need more collective effort put into "Better SQL", so PRQL is a welcome sight!

[1] https://github.com/edgedb/rfcs/blob/21e581a188715c6ff82944b6...

Few notes:

1) SQL also allows you to define windowing reusably, like this

    select
        sum(blah) over window_abc,
        avg(blah) over window_abc
    from table_xyz
    window window_abc as (partition by x order by y)

2) Sadly my main pain point with SQL for ETL is not possible to solve with a transpiler - SQL has exactly one target so doing things like "I want these records to go to a table A and those records go to table B" is not possible with one query.

3) It would be cool to see how this does typically annoying and repetitive cases from analytics / data warehousing world. I'm thinking like SCD1/2 implementation. But I don't even know if mutation is there yet.

4) I would recommend investing in one canonical formatter, like Go has. So that there isn't infinite number of ways the same query could be formatted for people to argue over preference.

EDIT:

5) Since this seems to be focused on analytics (by the choice of queries and Snowflake in examples), I want to highlight that someone suggested to use TPC-H (or TPC-DS) queries as a benchmark. It does sound like a good idea.

https://docs.microsoft.com/en-us/dotnet/csharp/programming-g...

var list = new List<int>{1,2,3}

var extracted = list

....................Where(x => x > 1)

....................Select(x => $"my number: {x})

....................ToList();

Not everybody

There are some SQL varieties that actually allow a hanging comma! Also, the provided examples seem comma-dependent, no?

As someone who writes a ton of analytical SQL, i think this would get super messy for long, complex queries with casting, case statements, windows functions, etc.

Most people just need to learn to write better SQL!

I’m not a big fan of the ternary operator. I think the ‘? :’ is hard to read and caters to programmers from system programming languages. A query language should cater to BI and stats people as they typically have a harder time learning syntax than a CS person has learning non-C-like syntax.

I’ve always liked Pythons ‘val if condition else other_val’. It’s easy to read for new-comers, while the ‘? :’ is a devil to google for.

In the second example I don’t understand why there are both let statements and select statements for the same columns. I also don’t understand why select uses square brackets. Maybe I’m too used to sql but why not just not have brackets?

A lot of the time you just want to transpose your result, but you have to choose an aggregate and handle null cases to force pivot to work the way you want it.

And a lot of the time you want to aggregate a window but keep the ids of the row so you avoid the having keyword altogether and go for row_number and dense_rank to get your aggregate results.

If I were to write a query language, I would discard group by and having and make it easier to apply transpose and window functions.

... then I read the PRQL one again and again. It does read more like English and more like how people think.

PRQL is much more descriptive.

I quite like it.

JK. Cool concept and hopefully it catches.

This syntax is lovely! It's more intuitively readable (and SQL is not that bad in that regard).

My feedback:

1. Lower case, underscored everything makes the terms a bit hard to differentiate. Maybe set some classes of symbols in CamelCase, or add a !@#$%& prefixes to them to make it more readable.

2. I dont like to use another language (SQL or PRSQL for that matter) to db interaction, I like to write the queries in the language that I'm using to develop in. There are ORMs in this design space, but I'm a little fed up with them. In Java there's jOOQ. Other less-OO-more-functional ORMs exist in Rust and Haskell land. These often have a code generation step, a library is generated that guarantees some type safety for a give schema version. Some are more SQL-like, some provide a different API. PRQL is much more diverted from SQL than these, and for good reasons. Maybe several languages could easily have libs like this building on top of JPQL?

3. You solution is a bit like GraphQL in some regards; where there is a tool needed to convert the query to SQL. Tools like this exist, like Hasura and the likes. Hasura does a lot more. To me GraphQL has the huge advantage of serving a schema so that clients can be generated. I can interact with GraphQL in a type safe fashion from by generating a client in, say, Elm. The generated client lib does not allow my to write syntax errors in my queries and ensures all type conversions are sound. Maybe PRQL can also be a language like GraphQL in that regard, and provide a schema too.

4. JPQL. It's close to SQL. It improves to SQL, but I never found it enough of an improvement to justify the cost. I think your proposal is better. But still I think JPQL deserves a mention as maybe one of the most widespread compile-to-SQL languages.

- https://github.com/RCHowell/Sift - https://github.com/RCHowell/Sift/blob/main/src/main/kotlin/c...

Similarly, I think joins are "tough" not because of the way SQL expresses them but because the logical possibilities of merging data from multiple tables are varied.

If you are rolling a SQL schema for a home improvement contractor, it is extraordinarily unlikely that their specific business would expect any scenarios in which carpets are sometimes known as furniture.

Having a bounded context to operate within is what makes SQL magical for me. When people don't understand the business or simply the game around how you talk about the business, things start getting messy wrt joins.

I was not proposing a SQL database of carpets, or furniture, as a thought experiment.

eg the lack of default column aliasing from joins

    SELECT 
        A.id AS A__id, 
        A.name AS A__name, 
        B.id AS B__id, 
        B.name AS B__name 
    FROM A 
    LEFT JOIN B 
        ON A.other_id = B.other_id

    SELECT 
        A.*, 
        B.* 
    FORMAT (TABLE__) 
    FROM A 
    LEFT JOIN B 
        ON A.other_id = B.other_id

I don't think there is much that could be done to address left, right, inner, outer join semantics. It's just something you have to learn if you want to do a lot of SQL (though, you are likely only ever going to use left and inner joins).

Wondering about generic use of `let` - you have let for col defns, but `func` for functions and a TODO for tables/CTEs - could/should `let` do the lot? (Like another commenter posted, this is how MS's M language, used in PowerQuery in PowerBI and Excel works). Could enable an escape from point-free for entire queries if taken to extreme generality, not sure if that's a good thing, maybe it could be?

Bikeshedding: even with some OCaml/F# experience, i find `f x y` harder to read than `f(x, y)`.

At the moment `let` is used to add a column as part of an existing pipeline. [1]

`func` is the start of new expressions / pipelines. And I just added a proposal for `table = `, which would be the same.

Does that make sense? Very open to more feedback...

[1] I just added `let` based on feedback here, it's better than it was, but not perfect, as it can be confused for a new pipeline given its use in other langs.

As someone who has written quite a few half-baked-for-general-use but fit-for-purpose SQL generator utilities over the years, I'll suggest that if you intend for a novel syntax to be a general SQL replacement then being isomorphic to SQL would massively increase usefulness and uptake:

1. novel syntax to SQL; check! Now novel syntax works with all the databases!

2. any valid SQL to novel syntax; a bit harder, but I'd start by using a SQL parser like https://github.com/pganalyze/libpg_query and translating the resulting AST into the novel syntax.

3. novel syntax to SQL back to novel syntax is idempotent; a nice side effect is a validator/formatter for "novel syntax"

4. SQL to novel syntax back to SQL is idempotent; a nice side effect is a validator/formatter for SQL, which would be awesome. (See also https://go.dev/blog/gofmt, which is where I learned this "round trip as formatter" trick.)

I don't mean for this to sound negative, and I know that 2, 3, and 4 are kind of hard. Thank you for building prql!

One thing I like about Flux is the ability to split streams and return multiple distinct aggregations. Very handy in Grafana dashboards!

It's like that meme "Nobody says 'I want to be an Excel guru when I grow up.'"

SQL is just the means to an end - to alter or retrieve data in a system.

We shouldn't be coming up with nicer ways to handle autocompletion, or recursive semantics, or windowing functions.

SQL was created to be declarative and human-friendly, to be read aloud.

The next evolution of SQL should be natural language.

It should be bounded at the schema and enriched with as much context as it can, both about the domain of the data, the data itself, and its relationships within itself and to other schemas.

I just want to see all the people in my organization who haven't submitted their timesheet this week; all the orders in my ERP that are waiting for parts from a specific vendor; how much lift I'm getting from my targeted marketing campaign. I just want to delete any contact from CRM who hasn't responded to an email in the last 45 days!

I don't want SQL; I want something that translates natural language into a logical plan, and a physical plan; and a separate tool that allows me to express many, many natural language concepts for my bounded schema ... perhaps in a SQL-like way.

Like a metrics / definition store on steroids, or just a sea of rich aliases and computed columns and subqueries that can be composed without stressing over syntax or newlines or ordering or complex join conditions.

BTW window definitions are reusable using the WINDOW clause, there's no need to define it over and over in SELECT.

    SELECT
        date,
        CASE WHEN is_valid_price
            THEN price_adjusted / LAG(price_adjusted, 1) OVER w - 1 + dividend_return
            ELSE NULL
        END AS return_total,
        CASE WHEN is_valid_price
            THEN price_adjusted_usd / LAG(price_adjusted_usd, 1) OVER w - 1 + dividend_return
            ELSE NULL
        END AS return_usd,
        CASE WHEN is_valid_price
            THEN price_adjusted / LAG(price_adjusted, 1) OVER w - 1 + dividend_return - interest_rate / 252
            ELSE NULL
        END AS return_excess,
        CASE WHEN is_valid_price
            THEN price_adjusted_usd / LAG(price_adjusted_usd, 1) OVER w - 1 + dividend_return - interest_rate / 252
            ELSE NULL
        END AS return_usd_excess
    FROM
        prices
    WINDOW
        w AS (PARTITION BY sec_id ORDER BY date)
    ;

- code/formula reutilization where we need to repeat logic over the query

- functionalities like `EXCEPT` and `REPLACE` modifiers for `SELECT *`, like in google bigquery (in most SQL databases its frustrating when you have a large number of columns and you only want to hide or replace a couple of them)

I do think SQL is all over the place, and while not perfect, it’s familiar and we got used to express our queries the SQL way. At the end, since you are generating SQL to interact with databases, you would have to understand SQL in order to optimise your queries (it might be challenging to get the perfect SQL query from PRQL as you do not know statistics about the tables or which indexes are available).

With SPyQL I am taking a different approach that tries to extend simple SQL SELECT statements so that some of these annoying features are tackled. In addition, by using Python to define expressions and conditions you solve another problem typically present on databases: extensibility. By including an IMPORT clause in your query you can import any Python module, so the sky is the limit. You also get a simple and intuitive way to work with objects and hierarchical data (like JSON).

I do find the language you are proposing very readable and flexible, bringing several advantages to SQL. If you build a parser I would love to bring it to spyql :-) The issues I have brought earlier would not be a problem to spyql since it is a tool to query files and data-streams in the command-line.

Functional, logical, and relational paradigms are the most commonly cited examples given of declarative programming.

Awesome.

I hate SQL so much, I know for personal projects this is gold. I imagine actually using it at work might draw some questions though

(I played around with this some years back, not to the point where anything came out of it worthy of publishing, but enough to be pleasantly surprised how far 'quite far' turned out to be in practice)

The ones that particularly excite me are shorthands for SQL, even though their heavy use of symbols may be a detriment. One particular use case is in easily defining static authorization policy-queries that are backed by database data plus and have request variables injected during evaluation.

I am not very excited by datalog/prolog-based languages because I think logic languages are too unnatural to ever go mainstream. But I'd be excited to be wrong or for logic languages to become more friendly.

Here are some others I'm watching.

  * https://github.com/mrumkovskis/tresql
  * https://www.htsql.org/doc/overview.html
  * https://github.com/cytosm/cytosm

The fact that you have to go through all these intermediate layers to access your data is just stupid. Data should just be "there."

(Same name, same goal, different approach, and already working)

PRQL looks very similar to Ecto, the Elixir Query DSL

https://hexdocs.pm/ecto/Ecto.Query.html

http://intelligiblebabble.com/a-better-query-language-bql-la...

It had this github repo https://github.com/lelandrichardson/BQL but never went anywhere

I hope PRQL has a better fate! unfortunately, by deviating from SQL lexical conventions (using :, using [], etc) we lose the ability to copy-paste from sql code elswhere.

I want a better SQL, but I also want some compatibility. Like typescript is for javascript.

In my own in-house SQL-like language I solve this simple issue by allowing previously defined columns to be reused:

  select
    salary + payroll_tax as _gross_salary,
    _gross_salary + benefits_cost as gross_cost,
    ...etc...

I do not believe other issues are actually issues, and am quite surprised by the volume of interest for this SQL alternative. Can't be because rust is mentioned, can it?

Design goal question - is the goal to have a language that blindly compiles to SQL, or will compilation require data schema knowledge?

Suggestion: where possible stick to the well established SQL keywords (prefer "group by" to "aggregate by")

It would really nice to have context when _writing_ the code, so we could do things like autocomplete (IIUC this is what Malloy does already).

Are there features you can think of that would be helpful if we had the schema context during transpiling?

Indeed — often SQL doesn't know the column _names_, let alone the types. So for cross-column selection (e.g. `across`), this does become necessary.

(But otherwise +1 to schema awareness during authoring.)

I know this is meant to transpile to SQL and so maybe this language is the wrong place to do this, but my biggest pet peeve with SQL is the ternary logic introduced by SQL nullability. I'm begging and pleading for this wart to go away, and I would love to see some algebraic sum types (Optional/Maybe, etc.) used in their place.

It's not declarative. It's functional.

I believe that the approach that is followed by PRQL is more practical than SQL. We've implemented a similar approach in our visual ETL tool for non-technical people (https://easymorph.com) and it works wonderfully. Other cool things you can do with this approach (and can't with SQL):

* Modify existing columns without re-selecting the whole dataset

* Loops (iterations)

* Conditional IF/THEN/ELSE branching as a workflow statement

* Exceptions & error handling

My one suggestion: use a distinct symbol for assignment and equality, either :=/==, =/==, or even :=/=. I kinda like the Go way of doing things, := assigns and initializes, == is equality (not sure you have a need for = re-assignment, but maybe). But I would definitely warn against = for context-dependent assignment or equality comparison.

You've got "let" so I guess that is kind of a syntactical difference; I guess the trippy part is seeing "=" do equality comparisons.

      from population
    select country, rollup(city), count(*)
      sort

    select country, city, count(*)
      from population
     group by country, rollup(city)
     order by country, city

This feels like it could cause compatibility issues in the future.

const employees = SQL.values([{ title: 'Developer', country: 'USA', salary: 120, payroll_tax: 20, healthcare_cost: 6 }]); employees.schemaName = 'employees'; const { groupBy, where: { gt, eq, and }, agg: { count, sum, avg } } = SQL; return employees.pdf(SQL.script('[salary]+[payroll_tax]').as('gross_salary'),SQL.script('[gross_salary]+[healthcare_cost]').as('gross_cost')) .fdf(and(gt('gross_cost',0),eq('country','USA'))) .gdf(groupBy('title','country') ,avg('salary').as('average_salary') ,sum('salary').as('sum_salary') ,avg('gross_salary').as('average_gross_salary') ,sum('gross_salary').as('sum_gross_salary') ,avg('gross_cost').as('average_gross_cost') ,sum('gross_cost').as('sum_gross_cost') ,count().as('count')) .having(gt('count',200)) .orderBy('sum_gross_cost');

I don't want a more verbose SQL I want a less verbose SQL!

A little off topic, but I have created many databases by writing and converting simple text to database scripts

Try this: https://text2db.com/

On the surface, they're always neat but when you start to dig into how you'd implement something in an RDBMS, it begins to fall apart.

Let's look at the example syntax:

    from employees
    filter country = "USA"                         # Each line transforms the previous result.
    gross_salary = salary + payroll_tax            # This _adds_ a column / variable.
    gross_cost   = gross_salary + healthcare_cost  # Variable can use other variables.
    filter gross_cost > 0
    aggregate split:[title, country] [             # Split are the columns to group by.
        average salary,                            # These are the calcs to run on the groups.
        sum     salary,
        average gross_salary,
        sum     gross_salary,
        average gross_cost,
        sum     gross_cost,
        count,
    ]
    sort sum_gross_cost                            # Uses the auto-generated column name.
    filter count > 200
    take 20

Another example that speaks to the "how do I implement this" side of the equation:

    from employees
    filter country = "USA"                         # Each line transforms the previous result.
    gross_salary = salary + payroll_tax            # This _adds_ a column / variable.
    gross_cost   = gross_salary + healthcare_cost  # Variable can use other variables.
    filter gross_cost > 0

The major tradeoff you make in most ORMs is exactly this: You lose out on being able to be explicit about how many queries are sent to the DB (and in many cases how efficient those queries are). Now this would become a language feature? What do I gain for that loss?

I'm not saying that SQL Syntax is perfect; far from it. I'm not seeing how this is an improvement.

I think if you want traction though, a proof of concept using an existing RDBMS would go a long way into providing evidence that this will work and is sufficiently thought out to deal with even the basics of what existing SQL databases have to. Query planning is hard, especially if you want it to be fast.

It's in the aggregate portion, like you said. Other example queries have a select portion. Why does it matter that it's not in the leading position like SQL?

> Does this mean that the database must scan all records of the employee table in order to return the result before moving to the next step in the query? Must I index all fields? If not, how does a query planner prepare for this scenario?

No, they are just describing how the statement is supposed to be interpreted by a human. I think you can basically just shuffle all the filter statements to the end and keep it logically equivalent.

This is a proposal for a "transpiles to SQL" language. So long as that transpliation is predictable, you cannot run into the sort of issues you are describing.

I don't mind it not being in the leading position. The author provided a very simple query and in that case it's not immediately apparent what fields to expect the resultset to contain when returned to the consumer.

This is a troubleshooting issue more than anything else. IMO placing the "selected fields" into the very centre of the query is distracting and obfuscates what is happening.

> This is a proposal for a "transpiles to SQL" language. So long as that transpliation is predictable, you cannot run into the sort of issues you are describing.

I think a good test of whether any transpiled language works well is to look at whether it could work on its own as a language. See: Typescript.

"Each line transforms the previous result." - I assume this is referring to the order that transpilation happens, so you can read it top to bottom and understand the flow easily.

One thing I would like to see is how a recursive CTE might look.

i am against "improving" sql. instead, i thing a whole rethink of the engineering behind relational engines needs to occur. for example, why can't a relational database support both SQL and other languages simultaneously, instead of being so black-boxish?

It is a language created to read like natural language, to facilitate its adoption.

Not only developers use SQL, less technical users also use it, especially in the finance industry.

Now, do you use BASIC professional software development? While some do, most don't. So why do we use SQL then?

An imperative language that compiles to SQL can be attractive, but unless the compilers come with good compiler warnings, we may end up created bloated database code.

It took a bit of getting used to but I'm coming to really enjoy Ecto and especially now that sqlite is fully supported, I'm finding myself using it in places I normally wouldn't have.

[1]: http://hexdocs.pm/ecto/Ecto.Query.html

Ah, Presto could be another popular target.

It's a huge effort but something which could have colossal payback.

This example looks so weird and it only is getting worse.

  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;

  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;

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.

[0] https://partiql.org/

If you want simple 'forward declaration' take a look at the SQL WITH clause.

[0] - https://docs.aws.amazon.com/AmazonCloudWatch/latest/logs/CWL...