Comment by snthpy

I have a moderate amount of SQL experince, but I could not write that query at the top of my head. Maybe you misunderstand what the PRQL query is doing?

Here's the SQL it generates:

WITH table_0 AS ( SELECT , ROW_NUMBER() OVER ( PARTITION BY album_id ORDER BY milliseconds DESC ) AS _expr_0 FROM tracks ) SELECT FROM table_0 WHERE _expr_0 <= 1

If I understand PRQL correctly, it finds the longest song for each album? A simple concept, but not a simple MySQL query.

This "find the latest row for each <column>" query is kind of a poster-child for seemingly-simple but actually difficult to get right/performant sql.

E.g. see: https://stackoverflow.com/questions/1313120/retrieving-the-l...

I think the logic here is: SQL is hard and most people don't know it well. So PRQL is perhaps easier to learn.

The same logic is applied to TypeScript vs JavaScript. Or C vs assembly. Or Nano vs vim. Etc etc.

It's a quandary though. SQL is clearly difficult for most people to get their heads around. It does require a different way of thinking about data, and you can get by with a minimal SQL knowledge for a long time, especially nowadays with ORMs.

But like so many other things, making the investment is worthwhile and pays off in small and large ways, forever (so far).

I'm not a PRQL fun. But TBH I can't write this SQL from my head. I have thousand lines of written SQL.

You can apply this logic to choosing a high level language vs writing assembly code.

Yes the pipeline is more complex, there are more tools and more syntax to track, but the benefits are pretty clear (or we'd all be writing UI code in hand written asm)

While leaky abstractions are a huge problem, the thing about abstractions is that, if they are any good, the benefits and improvements to productivity outweigh the negatives. You just have to figure out if the gains PRQL could give you are worth the effort.

I rarely write SQL, so it's not worth it for me. But if PRQL were an actual query engine, not just a translation layer, and some database offered a native PRQL interface, I would immediately switch to it rather than to keep twisting my brain with SQL and it's inane syntax and rules.

(I was a full time DBA in one previous life, so I should be more comfortable with SQL than most.)

Eh, `DISTICT ON` is a custom PostgreSQL extension.

A standard* method would be:

    SELECT *
    FROM tracks
    QUALIFY row_number() over (partition by album_id order by milliseconds desc) = 1;

But the QUALIFY clause is so new that it doesn't work on most RDBMSs. If you're on MS SQL Server, you're still using:

    SELECT *
    FROM (
      SELECT *
        ,row_number() over (partition by album_id order by milliseconds desc) rn
      FROM tracks
      ) x
    WHERE x.rn = 1;

That said, I still don't think PRQL is particularly amazing. I can't tell if it's merely syntactic sugar for SQL, or if it's actually meant to control query execution. If it's the former, it's likely to frustrate developers because it's actually just another layer of abstraction. If it's the latter, then it requires the developer to not only understand the data model well enough to be able to write SQL queries, they need to be able to understand the RDBMS impementation details well enough to be able to write queries that best take advantage of the current database's indexes, statistics, and configuration. Even something as simple as sorting before filtering or projecting can be a significant performance issue. Nevermind the fact that relational algebra done in the wrong order can be non-deterministic or not equivalent transformations, so even if the query processor is smart enough to do rewrites whatever the developer enters might be logically different unintentionally.

Ultimately I think it's a tool that lets the developer thinking about the problem in the way they prefer, rather than thinking about the problem in the way that best suits the problem at hand. Like insisting on writing documentation in LaTeX instead of Word or Markdown.

*: I believed this was in SQL 2023, but double checking it looks like it did not have make the final standard. I would be surprised if it didn't make it in the future, however.

With the additional benefit that resources about SQL are everywhere and every question has been answered already multiple times. On top of that you are most likely to encounter SQL at a job than PRQL.

You'd also need a LIMIT or a TOP, and you might need a subquery for that depending on your SQL of choice.

QUALIFY is not part of the SQL standard.

This query would error: "Field `id` is neither in the GROUP BY nor in an aggregate function." Since `id` is a key in `tracks` and `album_id` is a foreign key and not unique in `tracks`, the RDBMS wouldn't be able to use any implicit GROUP BY determinism, either.

You could do this:

    select distinct
      album_id 
      ,first_value(id order by milliseconds desc) as longest_track_id
    from tracks

But this is unlikely to perform as well as the row_number() method because it will cause the RDBMS to generate a record for every track and then waste time sorting the intermediate results to find the unique records in the output.

your query will return just album_id and id, not other track fields

if they need ChatGPT to generate the query (costly and possibly error-prone), then it means that not everyone is familiar with the output...

Hmm, I would think that LLM helps adoption for the semantic layers such as PRQL, Malloy, and dbt since it's possible to generate/validate/iterate 5 lines of PRQL compared to 25 lines of SQL but considering none of them widely adopted yet, you might indeed be correct in a way that LLM makes it harder for the new tools to gain adoption by helping you to suffer less from the verboseness of SQL.