Malloy writes SQL you can't (I'm pretty sure of it).

Here you go. Malloy looks like a fine tool but it's an unnecessary mental burden for people who understand SQL and I think the below is more maintainable long-term.

  with
  aggregates as (
    select
        state,
        name,
        sum(sum(number)) over (partition by state, name) as state_name_births,
        sum(sum(number)) over (partition by state) as state_births,
        sum(sum(number)) over (partition by name) as name_births,
        sum(sum(number)) over () as total_births
    from bigquery-public-data.usa_names.usa_1910_current
    group by state, name
    qualify state_name_births > 1000
  ),
  
  scoring as (
    select
      state,
      name,
      (state_name_births / state_births) / (name_births / total_births) as popularity_score,
      rank() over (partition by state order by (state_name_births / state_births) / (name_births / total_births) desc) as rnk
    from aggregates
    qualify rnk <= 20
  )
  
  select
    state,
    string_agg(name order by rnk) as names
  from scoring
  group by state
  order by state

Your Twitter thread shows the SQL that is equivalent, so it clearly can be done. Just because Malloy produces SQL that is impossible to read or write doesn't mean that the problem is only solvable with impossible-to-read SQL.

That would be like saying that Dreamweaver lets you do things that HTML+CSS don't because "look at how impossible to read the HTML output is!"

My first thought was that the generated SQL looks a lot like Looker, and indeed this is from them.

Nothing in that query is remotely novel or difficult. The style isn't the most readable, but any experienced data engineer has read and written far more heinous queries.

This is obvious because someone had to write the generator for these queries, and so it's not surprising that the techniques that appear in the generated queries are recognizable.

"Malloy writes queries you wouldn't enjoy writing" is more accurate, but there's no evidence it's doing anything a human can't.

Malloy writes SQL that I wouldn't write, that's for sure. Nothing there is hard to do, let alone impossible, with commonly used SQL constructs.

SQL + recursive queries (an extension supported by everyone since the late 90s) is Turing complete. Thus every arrangement and selection of data is reachable by a query. Therefore there is nothing Malloy can do that SQL can't (I'm absolutely sure of it).

Seems like all the magic is in this mysterious "all" thing, which is perfect for this particular problem... but I can't tell if it's part of a general coherent approach or just a handy gadget that makes Malloy look good on this problem.

None of it is.

Examples of things that would be better would be syntax for reference dereferencing. E.g., in SQL-like syntax:

  SELECT user->home_server->OS_version.version AS v,
         user->home_server->OS_version.is_obsolete AS obsolete
  FROM users
  WHERE name = 'whatever';

  SELECT user->home_server->OS_version.{version, is_obsolete} AS {version, obsolete}
  FROM users
  WHERE name = 'whatever';

Where the relations are not 1-1 then an array_agg() aggregation could be inferred, or some alternative aggregation could be explicitly given:

  -- this would be an array of {name, type} values
  SELECT g.members.{name, type}
  FROM groups g
  WHERE g.name = 'whatever';

  -- ditto
  SELECT g.members.{name, type}.array_agg()
  FROM groups g
  WHERE g.name = 'whatever';


  SELECT g.members.name.count()
  FROM groups g
  WHERE g.name = 'whatever';

Tinkering with totally different syntax where the shape of the query looks just about the same as in SQL seems like a dead end to me.

There's no real reason. I suspect it would be a very small change to some SQL implementations to allow the various clauses of queries to come in any order.

The sparksql dialect does allow the from clause to come first, actually.

SQL does reusability using VIEWs and functions.