LR7NGXSB

1drop table if exists task3_loan;
2
3create table task3_loan(
4   id integer,
5   requestdate timestamp,
6   request_amount numeric,
7   givedate timestamp
8);
9
10insert into task3_loan
11values(1, now(), 5000, now());
12
13select * from task3_loan;
14
15drop table if exists task3_income;
16
17create table task3_income(
18   loanid integer,
19   incomedate timestamp,
20   paidamount numeric,
21   paidcomission numeric,
22   extra_comission numeric,
23   paidfine numeric,
24   extra_fine numeric,
25   paiddeposit numeric,
26   substatus integer
27);
28
29
30select * from task3_income;
31
32
33insert into task3_income
34values(1, now(), 1000, 10, 5, 1, 0, 999, 200)
35
36
37
38select extract(month from givedate) as giveout_month,
39	   sum(request_amount) as giveout_amount,
40	   round((select -- surp0
41	   	sum(paidamount)+
42	   	sum(paidcomission)+
43	   	sum(extra_comission)+
44	   	sum(paidfine)+
45	   	sum(extra_fine)
46	   from task3_income 
47	   inner join task3_loan
48	   on task3_loan.id = task3_income.loanid
49	   where substatus = 200 -- only successful pays
50        	 and extract(month from givedate) = extract(month from incomedate)
51       group by extract(month from givedate)) / sum(request_amount), 2) as surp0,
52	   round((select -- surp1
53	   	sum(paidamount)+
54	   	sum(paidcomission)+
55	   	sum(extra_comission)+
56	   	sum(paidfine)+
57	   	sum(extra_fine)
58	   from task3_income 
59	   inner join task3_loan
60	   on task3_loan.id = task3_income.loanid
61	   where substatus = 200 -- only successful pays
62        	 and (extract(
63        	   month from (givedate + interval '1' month)
64        	   ) = extract(
65        	   month from (incomedate)
66        	   ) or 
67        	   extract(month from givedate) = extract(month from incomedate))
68       group by extract(month from givedate)) / sum(request_amount), 2) as surp1,
69	   round((select -- surp1
70	   	sum(paidamount)+
71	   	sum(paidcomission)+
72	   	sum(extra_comission)+
73	   	sum(paidfine)+
74	   	sum(extra_fine)
75	   from task3_income 
76	   inner join task3_loan
77	   on task3_loan.id = task3_income.loanid
78	   where substatus = 200 -- only successful pays
79        	 and (extract(
80        	   month from (givedate + interval '2' month)
81        	   ) = extract(
82        	   month from (incomedate)
83        	   ) or 
84        	   extract(
85        	   month from (givedate + interval '1' month)
86        	   ) = extract(
87        	   month from (incomedate)
88        	   ) or 
89        	   extract(month from givedate) = extract(month from incomedate))
90       group by extract(month from givedate)) / sum(request_amount), 2) as surp2
91
92from task3_income
93inner join task3_loan
94on task3_loan.id = task3_income.loanid
95where givedate is not null
96      and substatus = 200 -- only successful pays
97group by extract(month from givedate)
98
99
100