Hello everyone. I am having some problems coming up with a way to get a result from an aggregate of historical data. I understand N1QL does not support inner joins with sub selects since it joins on keys only, which makes my job harder.
I was hoping that by explaining what I want to achieve someone could help me come up with a solution - even if it involves multiple queries.
I store some data periodically for users, and I want, for each of this users, get the most recent document that the user has (User A might have a document from September 10th, User B might have a document for September 9th, etc.). After I have this set of documents, I want to calculate the average of a value that is contained in this documents.
I was able to get the most recent document for each user, but I am not able to perform the average on that result.
SELECT DISTINCT b.userId, b FROM bucket b WHERE b.docType = "Record" ORDER BY b.date DESC
Let me know if you need more information. Thanks!