Postgres 14

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

create table total_accounts (id serial primary key, source text, redirect text, result_for_the_day numeric, date date);
insert into total_accounts(id, source, redirect, result_for_the_day, date) VALUES ('1','zlat1','Alex', '150', '2022-01-01');
insert into total_accounts(id, source, redirect, result_for_the_day, date) VALUES ('2','zlat2','Alex', '350', '2022-01-01');
insert into total_accounts(id, source, redirect, result_for_the_day, date) VALUES ('3','zlat1','Leo', '110', '2022-01-01');
insert into total_accounts(id, source, redirect, result_for_the_day, date) VALUES ('4','zlat2','Leo', '11230', '2022-01-01');
insert into total_accounts(id, source, redirect, result_for_the_day, date) VALUES ('5','zlat1','Jon', '1120', '2022-01-01');
insert into total_accounts(id, source, redirect, result_for_the_day, date) VALUES ('6','zlat2','Jon', '158760', '2022-01-01');
insert into total_accounts(id, source, redirect, result_for_the_day, date) VALUES ('7','zlat1','Jacob', '15330', '2022-01-01');
insert into total_accounts(id, source, redirect, result_for_the_day, date) VALUES ('8','zlat1','Daniel', '150960', '2022-01-01');
insert into total_accounts(id, source, redirect, result_for_the_day, date) VALUES ('9','zlat2','Ноа', '134550', '2022-01-01');
insert into total_accounts(id, source, redirect, result_for_the_day, date) VALUES ('10','zlat2','William', '76150', '2022-01-01');
insert into total_accounts(id, source, redirect, result_for_the_day, date) VALUES ('11','zlat1','Alex', '1509', '2022-01-02');
insert into total_accounts(id, source, redirect, result_for_the_day, date) VALUES ('12','zlat2','Alex', '3508', '2022-01-02');
insert into total_accounts(id, source, redirect, result_for_the_day, date) VALUES ('13','zlat1','Leo', '11210', '2022-01-02');
insert into total_accounts(id, source, redirect, result_for_the_day, date) VALUES ('14','zlat2','Leo', '16530', '2022-01-02');
insert into total_accounts(id, source, redirect, result_for_the_day, date) VALUES ('15','zlat1','Jon', '11210', '2022-01-02');
insert into total_accounts(id, source, redirect, result_for_the_day, date) VALUES ('16','zlat2','Jon', '1512360', '2022-01-02');
insert into total_accounts(id, source, redirect, result_for_the_day, date) VALUES ('17','zlat2','Jon', '1512360', '2022-02-01');
insert into total_accounts(id, source, redirect, result_for_the_day, date) VALUES ('18','zlat2','Jon', '1512360', '2022-09-01');

select * from total_accounts ;
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected



id
source
redirect
result_for_the_day
date


1
zlat1
Alex
150
2022-01-01

2
zlat2
Alex
350
2022-01-01

3
zlat1
Leo
110
2022-01-01

4
zlat2
Leo
11230
2022-01-01

5
zlat1
Jon
1120
2022-01-01

6
zlat2
Jon
158760
2022-01-01

7
zlat1
Jacob
15330
2022-01-01

8
zlat1
Daniel
150960
2022-01-01

9
zlat2
Ноа
134550
2022-01-01

10
zlat2
William
76150
2022-01-01

11
zlat1
Alex
1509
2022-01-02

12
zlat2
Alex
3508
2022-01-02

13
zlat1
Leo
11210
2022-01-02

14
zlat2
Leo
16530
2022-01-02

15
zlat1
Jon
11210
2022-01-02

16
zlat2
Jon
1512360
2022-01-02

17
zlat2
Jon
1512360
2022-02-01

18
zlat2
Jon
1512360
2022-09-01


select TA.redirect, 
  count(distinct case when TA.source='zlat1' then extract(month from TA.date) else 0 end) number_of_accounts_zlat1,  
  count(distinct case when TA.source='zlat2' then extract(month from TA.date) else 0 end) number_of_accounts_zlat2,  
  sum(TA.result_for_the_day) accrued
from total_accounts TA
group by TA.redirect


redirect
number_of_accounts_zlat1
number_of_accounts_zlat2
accrued


Alex
2
2
5517

Daniel
1
1
150960

Jacob
1
1
15330

Jon
2
4
4708170

Leo
2
2
39080

William
1
1
76150

Ноа
1
1
134550


create table result (id serial primary key, redirect text, number_of_accounts_zlat1 int, number_of_accounts_zlat2 int, accrued numeric);
insert into result(id, redirect, number_of_accounts_zlat1, number_of_accounts_zlat2, accrued) VALUES ('1','Alex','1', '1', '5517');
insert into result(id, redirect, number_of_accounts_zlat1, number_of_accounts_zlat2, accrued) VALUES ('2','Daniel','1', '0', '150960');
insert into result(id, redirect, number_of_accounts_zlat1, number_of_accounts_zlat2, accrued) VALUES ('3','Jacob','1', '0', '15330');
insert into result(id, redirect, number_of_accounts_zlat1, number_of_accounts_zlat2, accrued) VALUES ('4','Jon','1', '3', '4708170');
insert into result(id, redirect, number_of_accounts_zlat1, number_of_accounts_zlat2, accrued) VALUES ('5','Leo','1', '1', '39080');
insert into result(id, redirect, number_of_accounts_zlat1, number_of_accounts_zlat2, accrued) VALUES ('6','William','0', '1', '76150');
insert into result(id, redirect, number_of_accounts_zlat1, number_of_accounts_zlat2, accrued) VALUES ('7','Hoa','0', '1', '134550');

select * from result ;
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected



id
redirect
number_of_accounts_zlat1
number_of_accounts_zlat2
accrued


1
Alex
1
1
5517

2
Daniel
1
0
150960

3
Jacob
1
0
15330

4
Jon
1
3
4708170

5
Leo
1
1
39080

6
William
0
1
76150

7
Hoa
0
1
134550

id	source	redirect	result_for_the_day	date
1	zlat1	Alex	150	2022-01-01
2	zlat2	Alex	350	2022-01-01
3	zlat1	Leo	110	2022-01-01
4	zlat2	Leo	11230	2022-01-01
5	zlat1	Jon	1120	2022-01-01
6	zlat2	Jon	158760	2022-01-01
7	zlat1	Jacob	15330	2022-01-01
8	zlat1	Daniel	150960	2022-01-01
9	zlat2	Ноа	134550	2022-01-01
10	zlat2	William	76150	2022-01-01
11	zlat1	Alex	1509	2022-01-02
12	zlat2	Alex	3508	2022-01-02
13	zlat1	Leo	11210	2022-01-02
14	zlat2	Leo	16530	2022-01-02
15	zlat1	Jon	11210	2022-01-02
16	zlat2	Jon	1512360	2022-01-02
17	zlat2	Jon	1512360	2022-02-01
18	zlat2	Jon	1512360	2022-09-01

redirect	number_of_accounts_zlat1	number_of_accounts_zlat2	accrued
Alex	2	2	5517
Daniel	1	1	150960
Jacob	1	1	15330
Jon	2	4	4708170
Leo	2	2	39080
William	1	1	76150
Ноа	1	1	134550