Oracle 18c

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

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name (CP, ROK, DOPIS_C) AS
SELECT 6059150790, 2014, 'C'  FROM DUAL UNION ALL
SELECT 6059150790, 2015, 'C'  FROM DUAL UNION ALL
SELECT 6059150790, 2016, 'C'  FROM DUAL UNION ALL
SELECT 6059150790, 2017, NULL FROM DUAL UNION ALL
SELECT 6059150790, 2018, 'C'  FROM DUAL UNION ALL
SELECT 6059150790, 2019, NULL FROM DUAL UNION ALL
SELECT 6059150790, 2020, 'C'  FROM DUAL UNION ALL
SELECT 6059150790, 2021, 'C'  FROM DUAL UNION ALL
SELECT 6059150790, 2022, 'C'  FROM DUAL UNION ALL
SELECT 6059150790, 2023, NULL FROM DUAL UNION ALL
SELECT 6059150790, 2024, NULL FROM DUAL;
11 rows affected

SELECT cp,
       rok,
       dopis_c,
       CASE dopis_c WHEN 'C' THEN posilani END AS posilani
FROM   table_name
       MATCH_RECOGNIZE(
         PARTITION BY cp
         ORDER BY rok
         MEASURES
           COUNT(dopis_c) AS posilani
         ALL ROWS PER MATCH
         PATTERN ( C+ | NOT_C )
         DEFINE C AS dopis_c = 'C'
       );


CP
ROK
DOPIS_C
POSILANI


6059150790
2014
C
1

6059150790
2015
C
2

6059150790
2016
C
3

6059150790
2017
null
null

6059150790
2018
C
1

6059150790
2019
null
null

6059150790
2020
C
1

6059150790
2021
C
2

6059150790
2022
C
3

6059150790
2023
null
null

6059150790
2024
null
null

CP	ROK	DOPIS_C	POSILANI
6059150790	2014	C	1
6059150790	2015	C	2
6059150790	2016	C	3
6059150790	2017	null	null
6059150790	2018	C	1
6059150790	2019	null	null
6059150790	2020	C	1
6059150790	2021	C	2
6059150790	2022	C	3
6059150790	2023	null	null
6059150790	2024	null	null