3 ms·

Can you model this in EdgeQL? https://developer.mongodb.com/community/forums/t/is-this-query-possible-of-orders-in-april-2020-that-were-from-new-customers/4462/

by querylangs2020 6y ago

Can you model this in EdgeQL? https://developer.mongodb.com/community/forums/t/is-this-query-possible-of-orders-in-april-2020-that-were-from-new-customers/4462/5 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.

RedCrowbar 6y ago
> 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_; }
- querylangs2020 6y ago
  Thanks. I'm going to dig in a bit more. I've been sold by the above and the homepage.. :)
gotski 6y ago
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.

RedCrowbar 6y ago

> 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_; }

querylangs2020 6y ago
Thanks. I'm going to dig in a bit more. I've been sold by the above and the homepage.. :)

gotski 6y ago

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.