SQL Server 2019

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

CREATE TABLE cliente_rbdigital
(
    cli_id            INT  PRIMARY KEY,
    cli_data_cadastro DATE NOT NULL
);

CREATE TABLE contrato_rbdigital
(
    cont_id              INT PRIMARY KEY,
    cont_cliente_id      INT NOT NULL,
    cont_data_vencimento DATE NOT NULL
);

CREATE TABLE titulo_rbdigital
(
    titulo_id            INT PRIMARY KEY,
    contratoid           INT NOT NULL,
    dataven              DATE NOT NULL,
    datapag              DATE,
    valorpago            MONEY
);

INSERT INTO cliente_rbdigital (cli_id, cli_data_cadastro)
VALUES (10472, '2020-07-31'),
       (10471, '2020-07-31'),
       (10470, '2020-07-31'),
       (10469, '2020-07-31'),
       (10468, '2020-07-31'),
       (10467, '2020-07-31'),
       (10466, '2020-07-31'),
       (10465, '2020-07-31'),
       (10464, '2020-07-31'),
       (10463, '2020-07-31');
       
INSERT INTO contrato_rbdigital (cont_id, cont_cliente_id, cont_data_vencimento)
VALUES (39486, 10472, '2020-08-30'),
       (39484, 10471, '2021-01-27'),
       (39483, 10470, '2020-10-29'),
       (39488, 10469, '2021-01-27'),
       (39478, 10469, '2021-01-27'),
       (39473, 10468, '2020-08-29'),
       (39472, 10467, '2021-01-26'),
       (39467, 10466, '2020-08-29'),
       (39463, 10465, '2020-08-29'),
       (39458, 10464, '2021-07-30'),
       (39459, 10463, '2020-08-29');

INSERT INTO titulo_rbdigital (titulo_id, contratoid, dataven, datapag, valorpago)
VALUES (39409, 39486, '2020-08-03', NULL, 0.00),
       (39407, 39484, '2020-08-03', '2020-07-31', 119.40),
       (39406, 39483, '2020-08-03', NULL, 0.00),
       (39411, 39488, '2020-08-01', '2020-07-31', 119.40),
       (39401, 39478, '2020-08-03', NULL, 0.00),
       (39396, 39473, '2020-08-02', '2020-07-30', 19.90),
       (39395, 39472, '2020-08-02', NULL, 0.00),
       (39391, 39467, '2020-08-02', '2020-07-30', 19.90),
       (39386, 39463, '2020-08-02', NULL, 0.00),
       (39381, 39458, '2020-08-02', '2020-07-30', 238.80),
       (39382, 39459, '2020-07-31', '2020-07-30', 9.90);

SELECT C.cli_id, 
       C.cli_data_cadastro, 
       CT2.cont_id, 
       CT2.cont_data_vencimento, 
       T2.titulo_id, 
       T2.dataven,
       T2.datapag, 
       T2.valorpago, 
       CASE
         WHEN T2.valorpago <= 0 THEN 'Não Pago'
         WHEN CT2.cont_data_vencimento > GETDATE() AND T2.valorpago > 0 THEN 'Pago'
         WHEN CT2.cont_data_vencimento < GETDATE() AND T2.valorpago > 0 THEN 'Vencido'
       END AS situacao

FROM cliente_rbdigital C

CROSS APPLY (
  SELECT TOP 1 CT.cont_id, CT.cont_data_vencimento
  FROM contrato_rbdigital CT
  WHERE CT.cont_cliente_id = C.cli_id
  ORDER BY CT.cont_data_vencimento
) CT2

CROSS APPLY (
  SELECT TOP 1 T.titulo_id, T.dataven, T.datapag, T.valorpago
  FROM titulo_rbdigital T
  WHERE T.contratoid = CT2.cont_id
  ORDER BY T.valorpago
) T2

ORDER BY C.cli_data_cadastro DESC, situacao DESC
;

cli_id	cli_data_cadastro	cont_id	cont_data_vencimento	titulo_id	dataven	datapag	valorpago	situacao
10463	2020-07-31	39459	2020-08-29	39382	2020-07-31	2020-07-30	9.9000	Pago
10464	2020-07-31	39458	2021-07-30	39381	2020-08-02	2020-07-30	238.8000	Pago
10466	2020-07-31	39467	2020-08-29	39391	2020-08-02	2020-07-30	19.9000	Pago
10468	2020-07-31	39473	2020-08-29	39396	2020-08-02	2020-07-30	19.9000	Pago
10469	2020-07-31	39488	2021-01-27	39411	2020-08-01	2020-07-31	119.4000	Pago
10471	2020-07-31	39484	2021-01-27	39407	2020-08-03	2020-07-31	119.4000	Pago
10472	2020-07-31	39486	2020-08-30	39409	2020-08-03	null	0.0000	Não Pago
10470	2020-07-31	39483	2020-10-29	39406	2020-08-03	null	0.0000	Não Pago
10467	2020-07-31	39472	2021-01-26	39395	2020-08-02	null	0.0000	Não Pago
10465	2020-07-31	39463	2020-08-29	39386	2020-08-02	null	0.0000	Não Pago