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.. :)
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.
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.