It is necessary to build a summary table based on data about the customer and their payments, where the columns will be the sequential number of the contract (contact_number
) and the year (year
) grouped by gender
. The main condition is that contact_number
and year
should be dynamically generated.
Test data:
CREATE TABLE loans
(
loan_id int,
client_id int,
loan_date date
);
CREATE TABLE clients
(
client_id int,
client_name varchar(20),
gender varchar(20)
);
INSERT INTO CLIENTS
VALUES (1, arnold, 'male'),
(2, lilly, 'female'),
(3, betty, 'female'),
(4, tom, 'male'),
(5, jim, 'male');
INSERT INTO loans
VALUES (1, 1, '20220522'),
(2, 2, '20220522'),
(3, 3, '20220525'),
(4, 4, '20220525'),
(5, 1, '20220527'),
(6, 2, '20220527'),
(7, 3, '20220601'),
(8, 1, '20220603'),
(9, 2, '20220603'),
(10, 1, '20220603');
Formation of columns can be done using the case when
construct, but this option is not suitable due to the need to constantly add new lines in the query when adding data.
My code:
with cte as
(
select
l.client_id,
loan_date,
extract(year from loan_date) as year,
client_name,
gender,
row_number() over (partition by l.client_id order by loan_date asc) as serial_number_contact
from
loans l
inner join
client c on l.client_id = c.client_id
)
select
gender
, year
, contract_number
, count(*)
from cte
group by gender, year, contract_number
order by year, contract_number
expected Output :
sex | 1 contract, 2022 | 2 contract, 2022 | 3 contract, 2022 |
---|---|---|---|
male | 2 | 2 | 1 |
female | 4 | 1 | 1 |
RDMBS - postgres