I have a use case for writing an aggregation which if written in SQL can be achieved using correlated queries.
I have a index called listings where the properties/columns are ListDate, ListPrice, SoldDate, SoldPrice, OffMarketDate.
ListDate is not nullable, but SoldDate,SoldPrice, OffMarketDate can be nullable.
I want to aggregate stats from the above index based on the following requirement.
- I want to have monthly stats, which I see can be achieved by DateHistogramAggregation
- For each month from the DateHistogramAggregation, I want to find the listings as follows:
Example: For Jan 2019, get all the listings where (ListDate< Feb 1st, 2019) and (SoldDateis null or SoldDate<Jan 1st, 2019) and (OffMarketDateis null or OffMarketDate< Jan 1st, 2019)
Then run the aggregation function for those lists each month.
I appreciate any suggestions to implement this use case. Thanks in advance for the help.