create pivot table with 2 dimensions

Question

I have 2 tables:

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');

It is necessary to calculate by years the number of contracts for loans in the context of the serial number of the contract and sex.

Output should be:

sex	1 contract, 2022	2 contract, 2022	3 contract, 2022
male	2	2	1
female	4	1	1

1 contract, 2 contract - Its client contract serial number.

Probably need to apply crosstab here, but it does not apply to CTE.

I would like to have auto-completion of the serial number and year in the columns, because the period includes several years and a large number of contracts

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 
        clientc on l.client_id = c.client_id
)
select 
    gender,
    year,
    serial_number_contact,
    count(*) as count_loan
from
    cte
group by 
    gender, year, serial_number_contact
order by 
    serial_number_contact, year

Answer 1

;with cte as(
select gender,
row_number() over (partition by l.client_id order by l.client_id,l.loan_date) serial
from loans l 
    inner join clients c
        on c.client_id=l.client_id)

        select gender
            ,sum(case when serial=1 then 1 else 0 end) as "1 Contract 2022"
            ,sum(case when serial=2 then 1 else 0 end) as "2 Contract 2022"
            ,sum(case when serial=3 then 1 else 0 end) as "3 Contract 2022"
        from cte group by gender

Test

http://sqlfiddle.com/#!17/0d0b93/3