By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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;
3 rows affected
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;
5 rows affected
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
| FIRST_NAME | LAST_NAME | MARCH_PAY | AVG_PREVIOUS_PAY |
|---|---|---|---|
| Marcos | Bisset | null | 12000 |
| Kate | Perez | 1500 | 2800 |
| Carlos | Casco | 2024 | null |
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)