SQL-Banking-analysis/task6.sql at main · devtraining84/SQL-Banking-analysis · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
use financial16_25;

SELECT
    c.gender,
    count(l.loan_id) as amount
FROM
        loan as l
    INNER JOIN
        account a using (account_id)
    INNER JOIN
        disp as d using (account_id)
    INNER JOIN
        client as c using (client_id)
WHERE l.status IN ('A', 'C')
GROUP BY c.gender


-- test
DROP TABLE IF EXISTS tmp_results;
CREATE TEMPORARY TABLE tmp_results AS
SELECT
    c.gender,
    sum(l.amount) as amount
FROM
        loan as l
    INNER JOIN
        account a using (account_id)
    INNER JOIN
        disp as d using (account_id)
    INNER JOIN
        client as c using (client_id)
WHERE True
    AND l.status IN ('A', 'C')
    AND d.type = 'OWNER'
GROUP BY c.gender;

select * from tmp_results;


WITH cte as (
    SELECT sum(amount) as amount
    FROM loan as l
    WHERE l.status IN ('A', 'C'))


SELECT (SELECT SUM(amount) FROM tmp_results) - (SELECT amount FROM cte)