hr@XE:11g2> column deptjob format a15;

hr@XE:11g2> column ename format a22;

hr@XE:11g2> column dept format 9999;

hr@XE:11g2> column deptjob format a15;

hr@XE:11g2> column ename format a18;

hr@XE:11g2> column sprank format 999.9;

hr@XE:11g2> column tprank format 999.9;

hr@XE:11g2> column skew format 999.9;

hr@XE:11g2> column adjustment format $99999.99;

hr@XE:11g2> column dept_cum format $99999.99;

hr@XE:11g2> r

  1  WITH

  2  skewed_pay AS

  3  (

  4    SELECT

  5      department_id, job_id,

  6      department_id||job_id AS deptjob,

  7      first_name||' '||last_name AS ename,

  8      salary,

  9      100*PERCENT_RANK() OVER

 10        (PARTITION BY department_id||job_id ORDER BY salary ASC) AS sprank,

 11      sysdate - hire_date AS tenure,

 12      100*PERCENT_RANK() OVER

 13        (PARTITION BY department_id||job_id ORDER BY hire_date DESC) AS tprank

 14    FROM employees

 15    WHERE department_id||job_id IN

 16      (SELECT deptjob FROM

 17        (SELECT COUNT(*), department_id||job_id AS deptjob

 18         FROM employees

 19         GROUP BY department_id||job_id  HAVING COUNT(*) > 3 )

 20      )

 21  ),

 22  adjust_calc AS

 23  (

 24    SELECT

 25      department_id,

 26      job_id,

 27      ename,

 28      salary,

 29      tenure,

 30      tprank - sprank AS skew,

 31      (CASE

 32         WHEN tprank-sprank >= 25.0 AND tprank-sprank < 50.0 THEN .01*salary

 33         WHEN tprank-sprank >= 50.0 AND tprank-sprank < 75.0 THEN .02*salary

 34         WHEN tprank-sprank >= 75.0 THEN .025*salary

 35         ELSE 0

 36       END) adjustment

 37    FROM

 38      skewed_pay

 39    WHERE

 40      tprank - sprank >= 25.0

 41  )

 42  SELECT

 43    department_id AS dept, job_id, ename, salary, skew, adjustment,

 44    TO_CHAR(100.00*RATIO_TO_REPORT(adjustment) OVER (), '99.99') AS R_to_R,

 45    SUM(adjustment) OVER (PARTITION BY department_id ORDER BY department_id ASC

 46      ROWS UNBOUNDED PRECEDING) AS dept_cum

 47  FROM adjust_calc

 48  ORDER BY 1 ASC, 4 DESC;

Dept. JOB_ID     ENAME               SALARY   SKEW ADJUSTMENT R_TO_R   DEPT_CUM

~~~~~ ~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~ ~~~~~~~ ~~~~~~ ~~~~~~~~~~ ~~~~~~ ~~~~~~~~~~

   30 PU_CLERK   Sigal Tobias         27776   25.0    $277.76   2.79    $277.76

   50 ST_MAN     Payam Kaufling       78368   50.0   $1567.36  15.73   $1567.36

   50 ST_CLERK   James Marlow         24800   52.6    $496.00   4.98   $2063.36

   60 IT_PROG    Valli Pataballa      76186   25.0    $761.86   7.65    $761.86

   60 IT_PROG    David Austin         76186   75.0   $1904.64  19.12   $2666.50

   60 IT_PROG    Diana Lorentz        66662   25.0    $666.62   6.69   $3333.12

   80 SA_REP     Patrick Sully        94240   28.6    $942.40   9.46    $942.40

   80 SA_REP     Allan McEwen         89280   28.6    $892.80   8.96   $1835.20

   80 SA_REP     Lindsey Smith        79360   39.3    $793.60   7.96   $2628.80

   80 SA_REP     Louise Doran         74400   25.0    $744.00   7.47   $3372.80

  100 FI_ACCOUNT Ismael Sciarra       91661   25.0    $916.61   9.20    $916.61

11 rows selected.