We Can Do Better Than SQL

> SELECT extract(day from timestamp '2001-02-16 20:38:40');

SQL is messy because all the syntax was decided on before there was a community that really understood what good syntax is. The 'from' in that extract does nothing and I can't easily identify if extract is a function or some sort of crazy parsing construct - what are the arguments? Is "day from timestamp '2001-02-16 20:38:40'" the argument? Are the arguments "day", "timestamp" and "'2001-02-16 20:38:40'"?

Are these annoyances crippling? Yes. Yes they are crippling. It should be possible for an amateur to quickly write an SQL validator as a starting project; relational algebra is not complicated. Any fool can write a validator for lisp. Relational algebra isn't that much more complicated - we don't have loops or flow control to contend with here.

Tidyverse's dplyr [0] implements the relational model for real dirty data and, as might be expected for something implemented this century, does a much better job than SQL. Not because the operations are that much different (although gather() & spread() are welcome additions) but through ingenious innovations like, as mentioned, functions having arguments instead of I-don't-even-know-what-that-is.

And the pipe operator which is legitimately ingenious. Great operator for data.

[0] https://dplyr.tidyverse.org/reference/index.html

ORM can really get in the way when you want to express groupings, aggregates, and all kind of joins sprinkled with let's say stored procedures.

But this has nothing to do with the ORM itself. It's just a fact that many people don't understand/consider the tradeoffs before jump into acting on something.

  select sum( case when prev_cust.cust_id is null then 1 else 0 end) / sum( april_cust_count ) as pc_new_cust
  from (
    /* get unique customers in April */
    select distinct cust_id, 
        1 as april_cust_count
    from orders
    where order_date between date '2020-04-01' and date '2020-04-30'
  ) as april_cust
  left join
  (
    /* get customers with a transaction prior to April */
    select distinct cust_id
    from orders
    where order_date