News for hackers. Stuff that matters.
Re:We Can Do Better Than SQL by querylangs2020 on Monday August 10, 2020 @10:25PM (#24116600)
Can you model this in EdgeQL? https://developer.mongodb.com/community/forums/t/is-this-que...

Area of curiousity at the moment as I too agree that SQL is a poor fit, even if the better DSL inputs eventually get reduced to SQL command text and parameter arrays.

Re:We Can Do Better Than SQL by RedCrowbar on Monday August 10, 2020 @11:12PM (#24116809)
> Can you model this in EdgeQL?

Absolutely!

  WITH
    april := <datetime>'2020-04-01T00:00+00',

    NewCustomers := (
      SELECT Customer
      FILTER
        NOT EXISTS (
          SELECT .orders
          FILTER .date < april
        )
    ),

    AprilCustomers := (
      SELECT Customer
      FILTER
        datetime_truncate(.orders.date, 'months') = april
    ),

    NewAprilCustomers := (
      SELECT AprilCustomers
      FILTER AprilCustomers IN NewCustomers
    )

  SELECT
    (count(NewAprilCustomers) / count(AprilCustomers)) * 100;
This assumes the following schema:

  type Order_ {
     property date -> datetime;
  }

  type Customer {
     multi link orders -> Order_;
  }
Re:We Can Do Better Than SQL by querylangs2020 on Monday August 10, 2020 @11:34PM (#24116920)
Thanks. I'm going to dig in a bit more. I've been sold by the above and the homepage.. :)
Re:We Can Do Better Than SQL by gotski on Tuesday August 11, 2020 @04:56AM (#24118498)
This was written on my mobile, so haven't had a chance to test it, but here's my first pass at modelling it in SQL:

  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 < date '2020-04-01'
  ) as prev_cust
  on april_cust.cust_id = 
  prev_cust.cust_id
Apologies for the lack of code formatting... I find that when SQL is written with a nice formatting (e.g. Nested sub queries with tabs) it reads a whole lot better.