Reporting

Question 1

Cumulative Aggregate

Answer 1

TO_CHAR(sum(sum(xxx))OVER(order by yyy rows unbounded preceding), ‘999999999’) AS CUMREV

Question 2

Cumulative Aggregate & Single Partition

Answer 2

TO_CHAR(sum(sum(xxx)))OVER(partition by yyy order by yyy rows unbounded preceding),’999999999’) AS CUMREV

Question 3

Cumulative Aggregate & Multiple Partition

Answer 3

TO_CHAR(sum(sum(xxx)))OVER(partition by yyy order by yyy rows unbounded preceding),’999999999’)AS CUMREV1 (x2)

Question 4

Moving Aggregate

Answer 4

TO_CHAR(avg(sum(xxx))OVER(order by yyy rows 2 preceding), ‘999999999’) AS MOV3MONTHSAVG

Question 5

Cumulative - 3 key points

Answer 5

SUM, Rows Unbounded Preceding, Cumulative_Rev

Question 6

Moving - 3 Points

Answer 6

AVG, Rows X Preceding, MOV_(x+1)_AVG

Question 7

Rank Over

Answer 7

RANK()OVER(order by sum(xxx) desc) AS RANK

Question 8

Rank Over With Partition

Answer 8

RANK()OVER(partition by yyy order by sum(xxx) desc) AS RANK_BY_PARTITION

Question 9

Row Number

Answer 9

ROW_NUMBER()OVER(order by sum(xxx) desc) AS ROW_NUM

Question 10

Dense Rank

Answer 10

DENSE_RANK()OVER(order by sum(xxx) desc) AS DENSE_RANK

Question 11

Rank Function

Answer 11

Select *
From (
 Select AAA,  BBB,  
 SUM(xxx) AS TOTAL,
 RANK()OVER(...) AS RANK
 FROM(...)
 WHERE(...)
 GROUP BY AAA,  BBB) 
WHERE RANK <= x

Question 12

Percent Rank Function

Answer 12

Select dw.time_id, total, percent_rank
FROM (
 Select time_id, sum(xxx) AS total, 
 Percent()rank over(...) AS perc_rank
 FROM fact
 GROUP BY time_id) t,  dw
WHERE t.time_id = dw.time_id
AND percent_rank >= 0.9
ORDER BY percent_rank desc

Question 13

Grouping and Cube

Answer 13

SELECT aaa, bbb,  ccc, sum(xxx),
GROUPING (aaa)  AS aaaGroup, 
GROUPING (bbb)  AS bbbGroup, 
GROUPING (ccc)  AS cccGroup
FROM (...)
WHERE (...)
GROUP BY CUBE (aaa, bbb, ccc) 
ORDEE BY aaa

Question 14

Cube

Answer 14

SELECT aaa, bbb,  ccc,  sum(xxx)
FROM (...)
WHERE (...)
GROUP BY CUBE (aaa, bbb, ccc) 
ORDER BY aaa

Question 15

Partial Cube

Answer 15

SELECT aaa, bbb,  ccc,  sum(xxx)
FROM (...)
WHERE (...)
GROUP BY CUBE (aaa, bbb),ccc 
ORDER BY aaa

Question 16

Rollup

Answer 16

SELECT aaa, bbb,  ccc,  sum(xxx)
FROM (...)
WHERE (...)
GROUP BY CUBE (aaa, bbb),ccc 
ORDER BY aaa

Question 17

Partial Rollup

Answer 17

SELECT aaa, bbb,  ccc,  sum(xxx)
FROM (...)
WHERE (...)
GROUP BY ROLLUP (aaa, bbb), ccc
ORDER BY aaa

Question 18

About Cube (3 Points)

Answer 18

cross tabular reports, generates subtotals for all combos, n-columns (2^n subtotal combos)

Question 19

About Rollup

Answer 19

Grand Subtotals and grand total, n-columns (n+1 subtotals) , maintain hierarchy

Question 20

Partial Rollup and Grouping and Decoding

Answer 20

SELECT
DECODE(grouping(aaa), 1, ‘all aaa’, aaa) as aaa,
DECODE(grouping(bbb), 1, ‘all bbb’, bbb) as bbb,
DECODE(grouping(ccc), 1, ‘all ccc’, ccc) as ccc,
Sum(xxx)
From (…)
Where (…)
Group by rollup (aaa, bbb), ccc
Ordee by aaa

Question 21

Order by multiple descending

Answer 21

Order By AAA desc, BBB desc