Filter and aggregation case


(Mohit Nagpal) #1

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 ?


(system) #2