ID User Color priority
1 1 blue 1
2 1 black 2
3 1 blue 3
4 2 blue 2
5 2 blue 3
6 3 blue 3
7 4 Orange 3
8 4 Orange 4
9 5 Red 4
10 5 Orange 5
Data set is like:
User like certain color but there is a priority associated with the liking. 1 is lowest priority.
User can like same color with different priority
Target query : For each color tell the user count. for a user and color combination row with highest priority should be counted
For above data , result should be
blue -> 3
Black -> 1
Orange -> 2
Red -> 1
Rows should include in result
for blue color
1 blue 3 // consider only one row with highest priority among the all rows for user = 1 and color = blue
2 blue 3 // consider only one row with highest priority among the all rows for user = 2 and color = blue
3 blue 3 // consider only one row with highest priority among the all rows for user = 3 and color = blue
for black color
1 black 2
for orange color
4 Orange 4 //consider only one row with highest priority among the all rows for user = 4 and color = Orange
5 Orange 5
for red color
5 Red 4
Using filter on color -> blue following rows
1 1 blue 1
3 1 blue 3
4 1 blue 2
5 2 blue 3
6 2 blue 3
This row count is 5 but target is to get row count-> 3.
Which rows should be counted :
1 1 blue 3 // consider only one row with highest priority among the all rows for user = one and color = blue
4 2 blue 3 // consider only one row with highest priority among the all rows for user = two and color = blue
6 3 blue 3 // consider only one row with highest priority among the all rows for user = three and color = blue
Is there any way to achieve that using nested filers / aggregrations or any other way ?