Условие

Есть две таблицы:

CLIENTS_TABLE(CLIENT_ID, CLIENT_NAME, BIRTHDAY, GENDER, ...)
LOANS_TABLE(LOAN_ID, CLIENT_ID, LOAN_DATE, LOAN_AMOUNT, ...)

Каждый клиент может иметь несколько договоров. Договор, оформленный клиентом впервые, — «первый», следующий — «второй» и т.д.

Нужно построить SQL-запрос для сводной таблицы:

Год оформления	1-й договор	2-й договор	3-й договор	...
Мужчины	...	...	...
Женщины	...	...	...

То есть: количество N-х по счёту договоров, оформленных в 2020, в разрезе пола клиента. Диалект SQL — любой.

Тестовые данные:

INSERT INTO CLIENTS_TABLE VALUES
  (1, 'bob',   '20200115', 'male'),
  (2, 'rocky', '20200215', 'female'),
  (3, 'like',  '20200215', 'female'),
  (4, 'ricky', '20200215', 'male');
 
INSERT INTO LOANS_TABLE VALUES
  (1,  1, '20200115', 10000), (2,  2, '20200215', 20000),
  (3,  3, '20200315', 30000), (4,  4, '20200415', 40000),
  (5,  1, '20200116', 15000), (6,  2, '20200315', 35000),
  (7,  3, '20200315',  5000), (8,  1, '20200115',  1500),
  (9,  2, '20200115',   500), (10, 1, '20200115',  1500);

Решение

Подход

Алгоритм в три шага:

Пронумеровать договоры каждого клиента по дате (это и есть «номер N-го договора»).
Отфильтровать только договоры, оформленные в 2020.
Сделать pivot: одна колонка на каждый номер договора, отдельная разбивка по полу.

Нумерация — это window-функция ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY loan_date, loan_id). loan_id в ORDER BY — тай-брейкер для договоров с одинаковой датой.

Реализация

WITH ordered AS (
  SELECT
    l.LOAN_ID,
    l.CLIENT_ID,
    l.LOAN_DATE,
    EXTRACT(YEAR FROM l.LOAN_DATE) AS loan_year,
    c.GENDER,
    ROW_NUMBER() OVER (
      PARTITION BY l.CLIENT_ID
      ORDER BY l.LOAN_DATE, l.LOAN_ID
    ) AS contract_num
  FROM LOANS_TABLE l
  JOIN CLIENTS_TABLE c ON c.CLIENT_ID = l.CLIENT_ID
)
SELECT
  GENDER,
  COUNT(*) FILTER (WHERE contract_num = 1) AS contract_1,
  COUNT(*) FILTER (WHERE contract_num = 2) AS contract_2,
  COUNT(*) FILTER (WHERE contract_num = 3) AS contract_3,
  COUNT(*) FILTER (WHERE contract_num = 4) AS contract_4
  -- ... продолжать сколько нужно
FROM ordered
WHERE loan_year = 2020
GROUP BY GENDER;

Что считать «первым» договором

Два сценария — выбор зависит от бизнес-определения:

Вариант A — нумерация по всей истории клиента, фильтр на 2020 после нумерации.

Если клиент оформил договор в 2018, потом ещё в 2020, то «договор в 2020» считается вторым, и в колонку contract_2 за 2020 он попадёт.

ROW_NUMBER() OVER (PARTITION BY CLIENT_ID ORDER BY LOAN_DATE)
... WHERE EXTRACT(YEAR FROM LOAN_DATE) = 2020

Это и есть код выше.

Вариант B — «первый в году», нумерация только среди договоров 2020 года.

Здесь договор в 2018 не учитывается, и для каждого клиента счёт начинается заново.

WITH ordered AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY CLIENT_ID ORDER BY LOAN_DATE) AS contract_num
  FROM LOANS_TABLE
  WHERE EXTRACT(YEAR FROM LOAN_DATE) = 2020
)
...

В тестовых данных все договоры — за 2020, поэтому варианты совпадают. На реальных данных это разные ответы — обязательно уточнять у заказчика.

Динамический pivot

Если число номеров договоров заранее не известно (а в реальности у топ-клиентов их могут быть десятки), статический pivot неудобен. Подходы:

Вернуть «длинный» формат (gender, contract_num, count), а pivot делать в BI / Pandas:

SELECT gender, contract_num, COUNT(*) AS cnt
FROM ordered
WHERE loan_year = 2020
GROUP BY gender, contract_num
ORDER BY gender, contract_num;

В Postgres использовать crosstab из расширения tablefunc.
В MS SQL — оператор PIVOT.
В ClickHouse — массив агрегатов: groupArray(contract_num) + arrayCount.

В большинстве реальных кейсов лучше вернуть длинный формат: SQL не должен решать задачу визуализации.

Альтернативная нумерация

Если несколько договоров оформлены в один день, ROW_NUMBER присвоит им разные номера в произвольном порядке (определяется тай-брейкером). Альтернативы:

RANK() — одинаковая дата → одинаковый ранг (но «первого» с двух сторон становится двое — обычно не то, что нужно).
DENSE_RANK() — то же, но без пропусков.

Если по бизнесу «договор» уникален (даже если в один день) — ROW_NUMBER с LOAN_ID в тай-брейкере правильно.

Подводные камни

Тип LOAN_DATE. В тестовых данных '20200115' хранится как строка/число — обязательно приводите к DATE. Иначе сортировка по строке будет неверной для других форматов.
Договоры в один день. Без LOAN_ID в ORDER BY нумерация недетерминирована.
Что считать «годом договора». В задаче сказано «в 2020» — значит, фильтр по EXTRACT(YEAR FROM LOAN_DATE) = 2020 после нумерации. Если бы стояло «клиенты, у которых в 2020 был N-й договор» — это совсем другой запрос (фильтрация клиентов).
COUNT(*) FILTER (WHERE ...) vs SUM(CASE WHEN ... THEN 1 END). В Postgres работают оба, в MySQL — только SUM(CASE). Для портабельности используйте SUM(CASE WHEN contract_num = 1 THEN 1 ELSE 0 END).
NULL в GENDER. Если у клиента не указан пол, он попадёт в отдельную группу NULL. Решите — фильтровать или показывать «unknown».
Аномалии в порядковости. Если есть отменённые договоры, нужно их исключить из нумерации (или явно отдельную ветку «отменённый первый» / «вторая попытка после отмены»).

Эталонный ответ

WITH ordered AS (
  SELECT l.*, c.GENDER,
         ROW_NUMBER() OVER (PARTITION BY l.CLIENT_ID ORDER BY l.LOAN_DATE, l.LOAN_ID) AS n
  FROM LOANS_TABLE l JOIN CLIENTS_TABLE c USING (CLIENT_ID)
)
SELECT GENDER,
       SUM(CASE WHEN n = 1 THEN 1 ELSE 0 END) AS contract_1,
       SUM(CASE WHEN n = 2 THEN 1 ELSE 0 END) AS contract_2,
       SUM(CASE WHEN n = 3 THEN 1 ELSE 0 END) AS contract_3,
       SUM(CASE WHEN n = 4 THEN 1 ELSE 0 END) AS contract_4
FROM ordered
WHERE EXTRACT(YEAR FROM LOAN_DATE) = 2020
GROUP BY GENDER;

На тестовых данных:

GENDER	1	2	3	4
male	2	2	1	1
female	2	2	1	0

(точные числа зависят от тай-брейкера — главное, что подход правильный).

SQL — сводная таблица «N-й по счёту договор за 2020» по полу клиента