SQL-Banking-analysis/task9.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
48
49
50
51
52
53
54
55
56
57
use financial16_25;

WITH cte AS (
    SELECT
        d2.district_id,

        count(distinct c.client_id) as customer_amount,
        sum(l.amount) as loans_given_amount,
        count(l.amount) as loans_given_count
    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)
        INNER JOIN
            district as d2 on
                c.district_id = d2.district_id
    WHERE True
        AND l.status IN ('A', 'C')
        AND d.type = 'OWNER'
    GROUP BY d2.district_id
    ;
)
SELECT *
FROM cte


WITH cte AS (
    SELECT d2.district_id,

           count(distinct c.client_id) as customer_amount,
           sum(l.amount)               as loans_given_amount,
           count(l.amount)             as loans_given_count
    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)
        INNER JOIN
           district as d2 on
                c.district_id = d2.district_id
    WHERE True
      AND l.status IN ('A', 'C')
      AND d.type = 'OWNER'
    GROUP BY d2.district_id
)
SELECT
    *,
    loans_given_amount / SUM(loans_given_amount) OVER () AS share
FROM cte
ORDER BY share DESC