OLAP specification

Examples

1. Display the ranking of employees, in order by surname, according to their total salary (based on salary plus bonus) that have a total salary more than $30,000.

      SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY,
          RANK() OVER (ORDER BY SALARY+BONUS DESC) AS RANK_SALARY
        FROM EMPLOYEE WHERE SALARY+BONUS > 30000
        ORDER BY LASTNAME

   Note that if the result is to be ordered by the ranking, then replace ORDER BY LASTNAME with:

      ORDER BY RANK_SALARY

   or

      ORDER BY RANK() OVER (ORDER BY SALARY+BONUS DESC)

2. Rank the departments according to their average total salary.

      SELECT WORKDEPT, AVG(SALARY+BONUS) AS AVG_TOTAL_SALARY,
          RANK() OVER (ORDER BY AVG(SALARY+BONUS) DESC) AS RANK_AVG_SAL
        FROM EMPLOYEE 
        GROUP BY WORKDEPT
        ORDER BY RANK_AVG_SAL

3. Rank the employees within a department according to their education level. Having multiple employees with the same rank in the department should not increase the next ranking value.

      SELECT WORKDEPT, EMPNO, LASTNAME, FIRSTNME, EDLEVEL,
          DENSE_RANK() OVER
            (PARTITION BY WORKDEPT ORDER BY EDLEVEL DESC) AS RANK_EDLEVEL
        FROM EMPLOYEE
        ORDER BY WORKDEPT, LASTNAME

4. Provide row numbers in the result of a query.

      SELECT ROW_NUMBER() OVER (ORDER BY WORKDEPT, LASTNAME) AS NUMBER,
          LASTNAME, SALARY
        FROM EMPLOYEE
        ORDER BY WORKDEPT, LASTNAME

5. List the top five wage earners.

      SELECT EMPNO, LASTNAME, FIRSTNME, TOTAL_SALARY, RANK_SALARY 
        FROM (SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY,
          RANK() OVER (ORDER BY SALARY+BONUS DESC) AS RANK_SALARY
          FROM EMPLOYEE) AS RANKED_EMPLOYEE 
        WHERE RANK_SALARY < 6 
        ORDER BY RANK_SALARY

   Note that a nested table expression was used to first compute the result, including the rankings, before the rank could be used in the WHERE clause. A common table expression could also have been used.
6. For each department, list employee salaries and show how much less each person makes compared to the employee in that department with the next highest salary.

      SELECT EMPNO, WORKDEPT, LASTNAME, FIRSTNME, JOB, SALARY,
          LEAD(SALARY, 1) OVER (PARTITION BY WORKDEPT
            ORDER BY SALARY) - SALARY AS DELTA_SALARY
        FROM EMPLOYEE
        ORDER BY WORKDEPT, SALARY
   

7. Calculate an employee's salary relative to the salary of the employee who was first hired for the same type of job.

      SELECT JOB, HIREDATE, EMPNO, LASTNAME, FIRSTNME, SALARY,
          FIRST_VALUE(SALARY) OVER (PARTITION BY JOB
            ORDER BY HIREDATE) AS FIRST_SALARY,
          SALARY - FIRST_VALUE(SALARY) OVER (PARTITION BY JOB
            ORDER BY HIREDATE) AS DELTA_SALARY
        FROM EMPLOYEE
        ORDER BY JOB, HIREDATE

8. Calculate the average close price for stock XYZ during the month of January, 2006. If a stock doesn't trade on a given day, its close price in the DAILYSTOCKDATA table is the null value. Instead of returning the null value for days that a stock doesn't trade, use the COALESCE function and LAG function to return the close price for the most recent day the stock was traded. Limit the search for a previous not-null close value to one month before January 1st, 2006.

      WITH V1(SYMBOL, TRADINGDATE, CLOSEPRICE) AS
      (
      SELECT SYMBOL, TRADINGDATE,
        COALESCE(CLOSEPRICE,
          LAG(CLOSEPRICE,
            1,
            CAST(NULL AS DECIMAL(8,2)),
            'IGNORE NULLS')
          OVER (PARTITION BY SYMBOL
          ORDER BY TRADINGDATE)
      )
      FROM DAILYSTOCKDATA
      WHERE SYMBOL = 'XYZ' AND
        TRADINGDATE BETWEEN '2005-12-01' AND '2006-01-31'
      )
      SELECT SYMBOL, AVG(CLOSEPRICE) AS AVG
        FROM V1
        WHERE TRADINGDATE BETWEEN '2006-01-01' AND '2006-01-31'
        GROUP BY SYMBOL
   

9. Calculate the 30-day moving average for stocks ABC and XYZ during the year 2005.

      WITH V1(SYMBOL, TRADINGDATE, MOVINGAVG30DAY) AS
      (
      SELECT SYMBOL, TRADINGDATE,
        AVG(CLOSEPRICE) OVER (PARTITION BY SYMBOL
          ORDER BY TRADINGDATE
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)
      FROM DAILYSTOCKDATA
      WHERE SYMBOL IN ('ABC', 'XYZ')
        AND TRADINGDATE BETWEEN DATE('2005-01-01') - 2 MONTHS
          AND '2005-12-31'
      )
      SELECT SYMBOL, TRADINGDATE, MOVINGAVG30DAY
        FROM V1
        WHERE TRADINGDATE BETWEEN '2005-01-01' AND '2005-12-31'
        ORDER BY SYMBOL, TRADINGDATE

10. Use an expression to define the cursor position and query a sliding window of 50 rows before that position.

       SELECT DATE, FIRST_VALUE(CLOSEPRICE + 100) OVER
           (PARTITION BY SYMBOL
           ORDER BY DATE
           ROWS BETWEEN 50 PRECEDING AND 1 PRECEDING) AS FV
         FROM DAILYSTOCKDATA
         ORDER BY DATE

11. For each employee, calculate the average salary for the set of employees that includes those employees in the same department who have an education level 1 lower and 1 higher than the employee.

       SELECT WORKDEPT, EDLEVEL, SALARY, AVG(SALARY)
             OVER (PARTITION BY WORKDEPT 
             ORDER BY EDLEVEL
             RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) 
          FROM EMPLOYEE 
          ORDER BY WORKDEPT, EDLEVEL

12. Calculate which quartile (4-quantiles) each employee's salary is in.

       SELECT EMPNO, SALARY, NTILE(4) OVER 
             (ORDER BY SALARY) AS QUARTILE
          FROM EMPLOYEE 
          ORDER BY SALARY

    The result set is:

    EMPNO  SALARY      QUARTILE
    ------ ----------- -----------
    200340    31840.00           1
    000290    35340.00           1
    200330    35370.00           1
    000310    35900.00           1
    200310    35900.00           1
    000280    36250.00           1
    000270    37380.00           1
    000300    37750.00           1
    200240    37760.00           1
    200120    39250.00           1
    000320    39950.00           1
    000230    42180.00           2
    000340    43840.00           2
    000170    44680.00           2
    000330    45370.00           2
    200280    46250.00           2
    200010    46500.00           2
    000260    47250.00           2
    000240    48760.00           2
    000250    49180.00           2
    000120    49250.00           2
    000220    49840.00           2
    000190    50450.00           3
    000180    51340.00           3
    000150    55280.00           3
    000200    57740.00           3
    000160    62250.00           3
    200170    64680.00           3
    000110    66500.00           3
    000210    68270.00           3
    000140    68420.00           3
    200140    68420.00           3
    200220    69840.00           4
    000060    72250.00           4
    000130    73800.00           4
    000050    80175.00           4
    000100    86150.00           4
    000090    89750.00           4
    000020    94250.00           4
    000070    96170.00           4
    000030    98250.00           4
    000010   152750.00           4
    
      42 record(s) selected.   

13. The query in the following example divides the rows into 3 buckets, grouping them by maximum salary. The maximum salary is included to show what values go into each bucket:

       SELECT NTILE(3) OVER (ORDER BY MAX_SALARY) AS Bucket, 
    MAX_SALARY FROM GOSALESDW.EMP_POSITION_DIM;

    A portion of the output from the query is in the following table:

    Table 1. Example output

    BUCKET	MAX_SALARY
    1	0.00
    ...	...
    1	35000.00
    2	5000.00
    ...	...
    2	12000.00
    3	13000.00
    ...	...
    3	301500.00

14. Find the cumulative distribution and the relative percentile rank of each employee's salary within their department.

       SELECT EMPNO, WORKDEPT, SALARY, 
          CAST(CUME_DIST() OVER (PARTITION BY WORKDEPT ORDER BY SALARY) AS DECIMAL(4,3))
             AS CUME_DIST, 
          CAST(PERCENT_RANK() OVER (PARTITION BY WORKDEPT ORDER BY SALARY) 
             AS DECIMAL(4,3)) 
             AS PERCENT_RANK FROM EMP 
          ORDER BY WORKDEPT, SALARY

    The result set is:

    EMPNO  WORKDEPT SALARY      CUME_DIST PERCENT_RANK
    ------ -------- ----------- --------- ------------
    200120 A00         39250.00     0.200        0.000
    200010 A00         46500.00     0.400        0.250
    000120 A00         49250.00     0.600        0.500
    000110 A00         66500.00     0.800        0.750
    000010 A00        152750.00     1.000        1.000
    000020 B01         94250.00     1.000        0.000
    000140 C01         68420.00     0.500        0.000
    200140 C01         68420.00     0.500        0.000
    000130 C01         73800.00     0.750        0.666
    000030 C01         98250.00     1.000        1.000
    000170 D11         44680.00     0.090        0.000
    000220 D11         49840.00     0.181        0.100
    000190 D11         50450.00     0.272        0.200
    000180 D11         51340.00     0.363        0.300
    000150 D11         55280.00     0.454        0.400
    000200 D11         57740.00     0.545        0.500
    000160 D11         62250.00     0.636        0.600
    200170 D11         64680.00     0.727        0.700
    000210 D11         68270.00     0.818        0.800
    200220 D11         69840.00     0.909        0.900
    000060 D11         72250.00     1.000        1.000
    000270 D21         37380.00     0.142        0.000
    200240 D21         37760.00     0.285        0.166
    000230 D21         42180.00     0.428        0.333
    000260 D21         47250.00     0.571        0.500
    000240 D21         48760.00     0.714        0.666
    000250 D21         49180.00     0.857        0.833
    000070 D21         96170.00     1.000        1.000
    000050 E01         80175.00     1.000        0.000
    000290 E11         35340.00     0.142        0.000
    000310 E11         35900.00     0.428        0.166
    200310 E11         35900.00     0.428        0.166
    000280 E11         36250.00     0.571        0.500
    000300 E11         37750.00     0.714        0.666
    200280 E11         46250.00     0.857        0.833
    000090 E11         89750.00     1.000        1.000
    200340 E21         31840.00     0.166        0.000
    200330 E21         35370.00     0.333        0.200
    000320 E21         39950.00     0.500        0.400
    000340 E21         43840.00     0.666        0.600
    000330 E21         45370.00     0.833        0.800
    000100 E21         86150.00     1.000        1.000
    
      42 record(s) selected.

15. Compare each employee's salary to the highest salary and second highest salary in the department.

          SELECT WORKDEPT, SALARY, FIRST_VALUE(SALARY) 
          OVER (PARTITION BY WORKDEPT ORDER BY SALARY DESC) AS FIRST, 
          NTH_VALUE(SALARY, 2) OVER (PARTITION BY WORKDEPT ORDER BY SALARY DESC) AS SECOND 
             FROM EMP 
             ORDER BY WORKDEPT, SALARY

    The result set is:

    WORKDEPT SALARY      FIRST       SECOND  
    -------- ----------- ----------- --------
    A00         39250.00   152750.00 66500.00
    A00         46500.00   152750.00 66500.00
    A00         49250.00   152750.00 66500.00
    A00         66500.00   152750.00 66500.00
    A00        152750.00   152750.00 66500.00
    B01         94250.00    94250.00 -
    C01         68420.00    98250.00 73800.00
    C01         68420.00    98250.00 73800.00
    C01         73800.00    98250.00 73800.00
    C01         98250.00    98250.00 73800.00
    D11         44680.00    72250.00 69840.00
    D11         49840.00    72250.00 69840.00
    D11         50450.00    72250.00 69840.00
    D11         51340.00    72250.00 69840.00
    D11         55280.00    72250.00 69840.00
    D11         57740.00    72250.00 69840.00
    D11         62250.00    72250.00 69840.00
    D11         64680.00    72250.00 69840.00
    D11         68270.00    72250.00 69840.00
    D11         69840.00    72250.00 69840.00
    D11         72250.00    72250.00 69840.00
    D21         37380.00    96170.00 49180.00
    D21         37760.00    96170.00 49180.00
    D21         42180.00    96170.00 49180.00
    D21         47250.00    96170.00 49180.00
    D21         48760.00    96170.00 49180.00
    D21         49180.00    96170.00 49180.00
    D21         96170.00    96170.00 49180.00
    E01         80175.00    80175.00 -
    E11         35340.00    89750.00 46250.00
    E11         35900.00    89750.00 46250.00
    E11         35900.00    89750.00 46250.00
    E11         36250.00    89750.00 46250.00
    E11         37750.00    89750.00 46250.00
    E11         46250.00    89750.00 46250.00
    E11         89750.00    89750.00 46250.00
    E21         31840.00    86150.00 45370.00
    E21         35370.00    86150.00 45370.00
    E21         39950.00    86150.00 45370.00
    E21         43840.00    86150.00 45370.00
    E21         45370.00    86150.00 45370.00
    E21         86150.00    86150.00 45370.00
    
      42 record(s) selected.