Hi @vsr1,
I already apply the indexes you advice me to create and it works great. The performance of the query with 145K documents was optimized. Also, the query that you provided works well. Thank you
I have a little bit problem with the current query I have:
SELECT
meta(p).id,
p.name,
p.rank,
pp AS purchased,
ps AS average
FROM `bucket` AS p
LEFT JOIN `bucket` AS pp
ON META(p).id = pp.product
AND pp.location = “location::6099247"
AND pp.type = "product-purchased"
LEFT JOIN `bucket` AS ps
ON META(p).id = ps.product
AND ps.periodType = "month"
AND ps.type = "product-sales-average"
AND ps.entityId = "location::6099247"
WHERE p.type = "product" AND p.name IS NOT NULL
ORDER BY purchased.hasPurchased ASC, p.rank.level ASC, p.rank.position ASC
LIMIT 1
So, my problem right now, is how can I add another JOIN to my current query. with an Array of entityId:
[
'territory::11234',
'territory::0045',
'territory::0036'
]
This is the query I’ve been trying to add:
LEFT JOIN `bucket` AS territory
ON META(p).id = territory.product
AND territory.periodType = "month"
AND territory.type = "product-sales-average"
AND ANY v IN [ 'territory::11234', 'territory::0045', 'territory::0036'] SATISFIES v = territory.entityId END
This query seems to work, but it doesn’t form an array of territory
instead, it creates another objects adding to product lists 
// so the expected result I want to achieve it this:
[
{
"id": product::0793",
"name": “product01”,
“purchased”: {
"product": "product::0793",
"location": "location::6099247",
"period": 30,
"type": "product-purchased",
"hasPurchased": 1
},
“average”: {
"product": "product::0793",
"entityId": "location::6099247",
"periodType": “month,
"averageSales": 1.73,
"averageQtyShipped": 0.25,
"type": "product-sales-average",
},
"territory": [
{
"product": "product::0793",
"entityId": "territory::11234",
"periodType": “month,
"averageSales": 1.73,
"averageQtyShipped": 0.25,
"type": "product-sales-average",
},
{
"product": "product::0793",
"entityId": "territory::0045'",
"periodType": “month,
"averageSales": 11.73,
"averageQtyShipped": 10.25,
"type": "product-sales-average",
},
]
},
{
"id": "product::0018",
"name": "product03”,
},
{
"id": "product::0020",
"name": "product04”
},
]
Another problem with this result is I can’t do GROUP BY, because I want to get AVG() for averageSales and averageQtyShipped GROUP BY product. So, it would be like this:
"territory": [
{
"averageSales": 6.73,
"averageQtyShipped": 5.25,
}
]
I really need help, 
Also, I was trying use LET instead of JOIN but I couldn’t get result when I do
USE KEYS META(p).id