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.
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;
type Order_ {
property date -> datetime;
}
type Customer {
multi link orders -> Order_;
} 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