SQL-Banking-analysis/task8.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
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140

use financial16_25;


SELECT
    count(di.client_id) AS nr_of_customers,
    ac.district_id AS region_id,
    dis.A2 AS region

    FROM disp di
    INNER JOIN account ac ON di.account_id = ac.account_id
    INNER JOIN district dis ON ac.district_id = dis.district_id
    WHERE di.client_id IN (select client_id
from disp
where type = 'OWNER')
GROUP BY 2,3
ORDER BY 1 desc

SELECT
   count(l.account_id) AS number_of_loans,
    ac.district_id as region_id
FROM loan l
INNER JOIN account ac ON l.account_id = ac.account_id
WHERE l.status IN ('A','C') AND ac.account_id IN (select account_id
from disp
where type = 'OWNER')
GROUP BY 2
ORDER BY 1 DESC

-----------------

SELECT
    c.gender,
    2021 - extract(year from birth_date) as age,

    -- agregaty
    sum(l.amount) as loans_amount,
    count(l.amount) as loans_count -- na późniejsze potrzeby
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, 2
;


SELECT
    -- agregaty
    sum(l.amount) as loans_amount,
    count(l.amount) as loans_count -- na późniejsze potrzeby
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'
;


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
;


DROP TABLE IF EXISTS tmp_district_analytics;
CREATE TEMPORARY TABLE tmp_district_analytics 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 tmp_district_analytics
ORDER BY customer_amount DESC
LIMIT 1


SELECT *
FROM tmp_district_analytics
ORDER BY loans_given_amount DESC
LIMIT 1


SELECT *
FROM tmp_district_analytics
ORDER BY loans_given_count DESC
LIMIT 1