Good blog post! I am happy to read this! voodoo to make someone obsessed with you

Q1: Select D1,D2,D3,Sum(x) From DataPoints Group By D1,D2,D3

Q2: Select D1,D2,D3,Sum(x) From DataPoints Group By D1,D2,D3 WITH CUBE

Q3: Select D1,D2,D3,Sum(x) From DataPoints Group By D1,D2,D3 WITH ROLLUP

My view : The below will be my notes as I solved. Hope it will help!!

Q1 : Normal Group by which will produce a * b * c .

In our case

(2, 2, 2, 6, 18, 8) => a=2 b=2 c=2 => 2*2*2 =8 which is not satisfied with d in this case. We can ignore this (2, 2, 2, 8, 64, 15) => 8 satisfied with dth place (5, 10, 10, 500, 1000, 550) => a=5 b=10 c=10 => 5*10*10 =500 satisfied with dth place (4, 7, 3, 84, 160, 117) => 4*7*3=84 satisfied with d th place.

Q2 : group by with CUBE

Explanation: Because of WITH CUBE, we need to consider all 3 dimensions as well and every possible cases.

so the result would be a*b*c +((a*b) +(b*c) +(c*a)) + (a + b + c) + 1

remaining options left , (2, 2, 2, 8, 64, 15) => 8 + (4 + 4 + 4 )+( 2 + 2 +2) +1 = 27 Not satisfied with eth place (5, 10, 10, 500, 1000, 550) => 500 + (50 + 100 + 50) +(5+10+10) + 1 = 726 satisfied with dth place (4, 7, 3, 84, 160, 117) => 84+ (28+21+12) + (4+7+3) +1 =160 satisfied with e th place.

Q3 : Group by with ROLL UP

Explanation : Roll up will Reduce the dimensions.

The formula will be a*b*c + (a*b) + a + 1 remaining options left , (5, 10, 10, 500, 1000, 550) => 500 + (50 ) +(5) + 1 = 556 Not satisfied with fth place (4, 7, 3, 84, 160, 117) => 84+ (28) + (4) +1 =117 satisfied with f th place.

****So the ANSWER will be (4, 7, 3, 84, 160, 117) satisfied all the three queries based on the OLAP operations.

(4, 7, 3, 84, 160, 117)

need to do trial and error for this...

if we consider a,b,c,d,e,f

then,

d = a*b*c ;;

e = (a*b*c) + (a*b + b*c + a*c) + (a+b+c) + 1 -- CUBE ;;

f = (a*b*c) + (a*b) + a + 1 --ROLLUP

Correct Answer is (4, 7, 3, 84, 160, 117).