Imagine a simple collection where each record has four primitive attributes - OrderId, CustomerEmail, OrderDate, ProductOrdered.
A business owner asks the question:
What percentage of orders in April 2020 were from new customers?
Important to note:
Same customer for this purpose is where the email is the same.
If your solution uses count <=1 be aware that a customer that orders twice in April is also a a new customer.
For stage 1 the output can be something like this:
New customers April: 13,343
Repeat customers (has at least 1 order prior to 1st april): 3,423
I guess converting a percentage isn’t technically necessary at this stage… but bonus point if done…
I can do this in SQL using CTEs, but we’re looking at ways of allowing stakeholders to model their own queries and sql builders based around string concatatention aren’t appealing, I’m actually looking at query DSLs and how appriorate they are to modelling these types of questions - with the possible view of sticking some UX report builder on top one day.
So back on point, can the Mongo query language model the above question?