Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE account(
   acc_id   INTEGER  NOT NULL PRIMARY KEY 
  ,cdate     DATE  NOT NULL
  ,acc_type VARCHAR(12) NOT NULL
);
INSERT INTO account VALUES (1,TO_DATE('11-07-2018', 'dd-mm-yyyy'),'customer');
1 rows affected

INSERT INTO account VALUES (2,TO_DATE('01-11-2018', 'dd-mm-yyyy'),'customer');
1 rows affected

INSERT INTO account VALUES (3,TO_DATE('02-09-2018', 'dd-mm-yyyy'),'employee');
1 rows affected

INSERT INTO account VALUES (4,TO_DATE('01-09-2018', 'dd-mm-yyyy'),'customer');
1 rows affected

CREATE TABLE credit_request(
   credit_id     INTEGER  NOT NULL PRIMARY KEY 
  ,cdate          DATE  NOT NULL
  ,credit_type   VARCHAR(13) NOT NULL
  ,acc_id        INTEGER  NOT NULL
  ,credit_amount INTEGER  NOT NULL
);
INSERT INTO credit_request VALUES (1112,TO_DATE('01-08-2018', 'dd-mm-yyyy'),'failed',1,2200);
1 rows affected

INSERT INTO credit_request VALUES (1214,TO_DATE('02-12-2018', 'dd-mm-yyyy'),'success',2,1500);
1 rows affected

INSERT INTO credit_request VALUES (1312,TO_DATE('03-11-2018', 'dd-mm-yyyy'),'success',4,8750);
1 rows affected

INSERT INTO credit_request VALUES (1468,TO_DATE('01-12-2018', 'dd-mm-yyyy'),'failed',2,3500)
1 rows affected

    	SELECT
    		a.*, c.*, ROW_NUMBER() OVER(PARTITION BY c.acc_id, c.credit_type ORDER BY c.cdate DESC) rn
    	FROM 
    		account a
    		LEFT JOIN credit_request c ON c.acc_id = a.acc_id 


ACC_ID
CDATE
ACC_TYPE
CREDIT_ID
CDATE
CREDIT_TYPE
ACC_ID
CREDIT_AMOUNT
RN


1
11-JUL-18
customer
1112
01-AUG-18
failed
1
2200
1

2
01-NOV-18
customer
1468
01-DEC-18
failed
2
3500
1

2
01-NOV-18
customer
1214
02-DEC-18
success
2
1500
1

4
01-SEP-18
customer
1312
03-NOV-18
success
4
8750
1

3
02-SEP-18
employee
null
null
null
null
null
1


SELECT
    acc_id,
    MAX(CASE WHEN credit_type = 'success' AND rn = 1 THEN credit_id     END) as last_successfull_credit_id,
    MAX(CASE WHEN credit_type = 'success' AND rn = 1 THEN cdate         END) as last_successfull_credit_date,
    MAX(CASE WHEN credit_type = 'success' AND rn = 1 THEN credit_amount END) as last_successfull_credit_amount,
    SUM(CASE WHEN credit_type = 'failed' THEN credit_amount ELSE 0 END) total_amount_of_failed_credit,
    SUM(CASE WHEN credit_type = 'failed' THEN 1 ELSE 0 END) / COUNT(*) ratio_success_request
FROM (
    SELECT
        a.acc_id,
	a.cdate adate,
	a.acc_type,
	c.credit_id, 
	c.cdate,
	c.credit_type,
	c.credit_amount,
	ROW_NUMBER() OVER(PARTITION BY c.acc_id, c.credit_type ORDER BY c.cdate DESC) rn
    FROM 
        account a
        LEFT JOIN credit_request c ON c.acc_id = a.acc_id       
) x
GROUP BY acc_id
ORDER BY acc_id


ACC_ID
LAST_SUCCESSFULL_CREDIT_ID
LAST_SUCCESSFULL_CREDIT_DATE
LAST_SUCCESSFULL_CREDIT_AMOUNT
TOTAL_AMOUNT_OF_FAILED_CREDIT
RATIO_SUCCESS_REQUEST


1
null
null
null
2200
1

2
1214
02-DEC-18
1500
3500
.5

3
null
null
null
0
0

4
1312
03-NOV-18
8750
0
0

ACC_ID	CDATE	ACC_TYPE	CREDIT_ID	CDATE	CREDIT_TYPE	ACC_ID	CREDIT_AMOUNT	RN
1	11-JUL-18	customer	1112	01-AUG-18	failed	1	2200	1
2	01-NOV-18	customer	1468	01-DEC-18	failed	2	3500	1
2	01-NOV-18	customer	1214	02-DEC-18	success	2	1500	1
4	01-SEP-18	customer	1312	03-NOV-18	success	4	8750	1
3	02-SEP-18	employee	null	null	null	null	null	1