select * from emp1;

select * from payment history;

Please give answer using With Clause ( I got the question from internet .. but I am unable to solve this question

CodePudding user response：

To find the salary for march and the average previous salary you can sum the salary for each employee and month and then use conditional aggregation:

SELECT MAX(e.first_name) AS first_name,
       MAX(e.last_name) AS last_name,
       SUM(CASE WHEN month = DATE '2018-03-01' THEN pay END) AS march_pay,
       AVG(CASE WHEN month < DATE '2018-03-01' THEN pay END) AS avg_previous_pay
FROM   emp1 e
       INNER JOIN (
         SELECT emp_id,
                TRUNC(pay_dated, 'MM') AS month,
                SUM(amt_paid) AS pay
         FROM   payment_history
         GROUP BY emp_id,
                TRUNC(pay_dated, 'MM')
       ) p
       ON e.emp_id = p.emp_id
GROUP BY e.emp_id

Which, for the sample data:

CREATE TABLE emp1 (EMP_ID, FIRST_NAME, LAST_NAME) AS
SELECT 10100, 'Marcos', 'Bisset' FROM DUAL UNION ALL
SELECT 10101, 'Kate',   'Perez'  FROM DUAL UNION ALL
SELECT 10102, 'Carlos', 'Casco'  FROM DUAL;

CREATE TABLE payment_history (EMP_ID, PAY_TYPE, AMT_PAID, PAY_DATED) AS
SELECT 10100, 'monthly pay', 12000, DATE '2018-02-02' FROM DUAL UNION ALL
SELECT 10101, 'monthly pay',  2800, DATE '2018-02-02' FROM DUAL UNION ALL
SELECT 10102, 'monthly pay',  1900, DATE '2018-03-02' FROM DUAL UNION ALL
SELECT 10101, 'bonus',        1500, DATE '2018-03-08' FROM DUAL UNION ALL
SELECT 10102, 'adjustment',    124, DATE '2018-03-10' FROM DUAL;

Outputs:

To then find the employee with the higher salary in march than their previous average salary, you can use a HAVING clause:

SELECT MAX(e.first_name) AS first_name,
       MAX(e.last_name) AS last_name
FROM   emp1 e
       INNER JOIN (
         SELECT emp_id,
                TRUNC(pay_dated, 'MM') AS month,
                SUM(amt_paid) AS pay
         FROM   payment_history
         GROUP BY emp_id,
                TRUNC(pay_dated, 'MM')
       ) p
       ON e.emp_id = p.emp_id
GROUP BY e.emp_id
HAVING SUM(CASE WHEN month = DATE '2018-03-01' THEN pay END)
        > AVG(CASE WHEN month < DATE '2018-03-01' THEN pay END)

Which outputs no rows as you can see from the previous data that Employees either have a higher average previous pay or they had no pay in one of the months; to get a result you need a larger data set where there is a row that actually matches the requirements.

Requiring a WITH clause is an artificial requirement that is not necessary to solve the problem; if you really must use it (you do not need to) then move the sub-query from the INNER JOIN into a sub-query factoring clause:

WITH avg_pay (emp_id, month, pay) AS (
  <sub-query>
)
SELECT ...
FROM   emp1 e
       INNER JOIN avg_pay p
       ON ...

fiddle

CodePudding user response：

Here's one option, step-by-step. Read comments within code.

with 
avg_sal as
  -- average salary per each employee
  (select emp_id, 
          avg(amt_paid) avg_sal
   from payment_history
   group by emp_id
  ),
march_sal as
  -- salary employee earned in March 2018
  (select emp_id, 
          amt_paid
   from payment_history
   where to_char(pay_dated, 'yyyymm') = '201803'
  )
select e.first_name
from emp1 e join avg_sal a on a.emp_id = e.emp_id
            join march_sal m on m.emp_id = e.emp_id
where m.amt_paid > a.avg_sal;

WHERE clause in march_sal CTE might need to be rewritten if there's an index on pay_dated column, as applying to_char function to it (the column) won't use that index. In that case, you'd use

WHERE pay_dated >= date '2018-03-01'
  AND pay_dated <  date '2018-04-01'

On the other hand, you might have a function-based index on to_char(pay_dated, 'yyyymm') - in that case, leave it as is.