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, DOPIS_K) AS
SELECT 6059150790, 2014, 'C',  NULL FROM DUAL UNION ALL
SELECT 6059150790, 2015, 'C',  'K'  FROM DUAL UNION ALL
SELECT 6059150790, 2016, 'C',  'K'  FROM DUAL UNION ALL
SELECT 6059150790, 2017, NULL, 'K'  FROM DUAL UNION ALL
SELECT 6059150790, 2018, 'C',  'K'  FROM DUAL UNION ALL
SELECT 6059150790, 2019, NULL, NULL FROM DUAL UNION ALL
SELECT 6059150790, 2020, 'C',  'K'  FROM DUAL UNION ALL
SELECT 6059150790, 2021, 'C',  NULL FROM DUAL UNION ALL
SELECT 6059150790, 2022, 'C',  'K'  FROM DUAL UNION ALL
SELECT 6059150790, 2023, NULL, 'K'  FROM DUAL UNION ALL
SELECT 6059150790, 2024, NULL, 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


SELECT cp,
       rok,
       dopis_c,
       rn,
       CASE dopis_c
       WHEN 'C'
       THEN rn - COALESCE(
              LAST_VALUE(CASE WHEN dopis_c IS NULL THEN rn END) IGNORE NULLS
                OVER (PARTITION BY cp ORDER BY rok),
              0
            )
       END AS posilani
FROM (
  SELECT cp,
         rok,
         dopis_c,
         ROW_NUMBER() OVER (PARTITION BY cp ORDER BY rok) AS rn
  FROM   table_name
);


CP
ROK
DOPIS_C
RN
POSILANI


6059150790
2014
C
1
1

6059150790
2015
C
2
2

6059150790
2016
C
3
3

6059150790
2017
null
4
null

6059150790
2018
C
5
1

6059150790
2019
null
6
null

6059150790
2020
C
7
1

6059150790
2021
C
8
2

6059150790
2022
C
9
3

6059150790
2023
null
10
null

6059150790
2024
null
11
null


SELECT cp,
       rok,
       dopis_c,
       CASE dopis_c
       WHEN 'C'
       THEN ROW_NUMBER() OVER (PARTITION BY cp, dopis_c, c_grp ORDER BY rok)
       END AS posilani
FROM (
  SELECT cp,
         rok,
         dopis_c,
         COUNT(1) OVER (PARTITION BY cp ORDER BY rok)
           - COUNT(dopis_c) OVER (PARTITION BY cp ORDER BY rok) AS c_grp
  FROM   table_name
)
ORDER BY
       cp,
       rok


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


SELECT cp,
       rok,
       dopis_c,
       dopis_k,
       CASE dopis_c WHEN 'C' THEN posilani END AS posilani,
       CASE dopis_k WHEN 'K' THEN num_k END AS num_k
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'
       )
       MATCH_RECOGNIZE(
         PARTITION BY cp
         ORDER BY rok
         MEASURES
           COUNT(dopis_k) AS num_k
         ALL ROWS PER MATCH
         PATTERN ( K+ | NOT_K )
         DEFINE K AS dopis_k = 'K'
       );


CP
ROK
DOPIS_C
DOPIS_K
POSILANI
NUM_K


6059150790
2014
C
null
1
null

6059150790
2015
C
K
2
1

6059150790
2016
C
K
3
2

6059150790
2017
null
K
null
3

6059150790
2018
C
K
1
4

6059150790
2019
null
null
null
null

6059150790
2020
C
K
1
1

6059150790
2021
C
null
2
null

6059150790
2022
C
K
3
1

6059150790
2023
null
K
null
2

6059150790
2024
null
null
null
null


SELECT cp,
       rok,
       dopis_c,
       dopis_k,
       rn,
       CASE dopis_c
       WHEN 'C'
       THEN rn - COALESCE(
              LAST_VALUE(CASE WHEN dopis_c IS NULL THEN rn END) IGNORE NULLS
                OVER (PARTITION BY cp ORDER BY rok),
              0
            )
       END AS posilani,
       CASE dopis_k
       WHEN 'K'
       THEN rn - COALESCE(
              LAST_VALUE(CASE WHEN dopis_k IS NULL THEN rn END) IGNORE NULLS
                OVER (PARTITION BY cp ORDER BY rok),
              0
            )
       END AS num_k
FROM (
  SELECT cp,
         rok,
         dopis_c,
         dopis_k,
         ROW_NUMBER() OVER (PARTITION BY cp ORDER BY rok) AS rn
  FROM   table_name
);


CP
ROK
DOPIS_C
DOPIS_K
RN
POSILANI
NUM_K


6059150790
2014
C
null
1
1
null

6059150790
2015
C
K
2
2
1

6059150790
2016
C
K
3
3
2

6059150790
2017
null
K
4
null
3

6059150790
2018
C
K
5
1
4

6059150790
2019
null
null
6
null
null

6059150790
2020
C
K
7
1
1

6059150790
2021
C
null
8
2
null

6059150790
2022
C
K
9
3
1

6059150790
2023
null
K
10
null
2

6059150790
2024
null
null
11
null
null


SELECT cp,
       rok,
       dopis_c,
       dopis_k,
       CASE dopis_c
       WHEN 'C'
       THEN ROW_NUMBER() OVER (PARTITION BY cp, dopis_c, c_grp ORDER BY rok)
       END AS posilani,
       CASE dopis_k
       WHEN 'K'
       THEN ROW_NUMBER() OVER (PARTITION BY cp, dopis_k, k_grp ORDER BY rok)
       END AS num_k
FROM (
  SELECT cp,
         rok,
         dopis_c,
         dopis_k,
         COUNT(1) OVER (PARTITION BY cp ORDER BY rok)
           - COUNT(dopis_c) OVER (PARTITION BY cp ORDER BY rok) AS c_grp,
         COUNT(1) OVER (PARTITION BY cp ORDER BY rok)
           - COUNT(dopis_k) OVER (PARTITION BY cp ORDER BY rok) AS k_grp
  FROM   table_name
)
ORDER BY
       cp,
       rok


CP
ROK
DOPIS_C
DOPIS_K
POSILANI
NUM_K


6059150790
2014
C
null
1
null

6059150790
2015
C
K
2
1

6059150790
2016
C
K
3
2

6059150790
2017
null
K
null
3

6059150790
2018
C
K
1
4

6059150790
2019
null
null
null
null

6059150790
2020
C
K
1
1

6059150790
2021
C
null
2
null

6059150790
2022
C
K
3
1

6059150790
2023
null
K
null
2

6059150790
2024
null
null
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