[Dúvida] Motivos de reemissão de cartões | Data Science

Bom dia pessoal,

Estou com uma dúvida em um projeto no BigQuery e gostaria da ajuda de vocês

Tenho duas bases, uma com bloqueios de cartões e outra com reemissões de cartões.

meu objetivo é cruzar essas duas bases para identificar, para cada cartão reemitido, qual foi o ultimo cartao bloqueado (com data e tipo de bloqueio) antes .

Entretanto quando uno as tabelas pelo numero da conta acabo, recebendo varias linhas por cartao reemitido.

Preciso apenas uma linha por cartao reemitido, mostrando o bloqueio anterior a ele (baseado na data)

Segue consulta:

WITH bloqueios AS (
SELECT
SUBSTR(CAST(a.lastgeneralstatusupdate AS STRING), 0, 6) AS mes_bloq,
SUBSTR(CAST(a.lastgeneralstatusupdate AS STRING), 0, 8) AS dt_bloq,
a.numberx AS cartao,
a.stgeneral AS status_bloq,
b.numberx AS conta,
c.ssnumber AS CPF
FROM trusted.tsys_cardx a
INNER JOIN trusted.tsys_caccounts b ON a.caccserno = b.serno
INNER JOIN trusted.tsys_people c ON b.peopleserno = c.serno
WHERE a.stgeneral != 'NORM'
AND a.virtual = 0
AND SUBSTR(CAST(a.lastgeneralstatusupdate AS STRING), 0, 6) IN ('202412','202501','202502','202503','202504', '202505')
),
reemissoes AS (
SELECT
NUMERO_CARTAO AS cartao,
NUMERO_CONTA AS conta,
AR_POSTAGEM,
DT_ABERTURA_CARTAO,
TIPO_EMISSAO,
CIDADE,
ESTADO,
DT_MOVIMENTO,
STATUS_CARDTRACKING,
DESCRICAO_STATUS,
DT_ATUALIZACAO_STATUS,
MOTIVO_DEVOLUCAO
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY COD_POSTAGEM ORDER BY DT_ATUALIZACAO_STATUS DESC, HR_ATUALIZACAO DESC) AS rn
FROM `csf-core-data-prd.trusted.mttechne_rastreio_cartao`
WHERE ingestion_timestamp >= "2024-09-01"
)
WHERE rn = 1
AND DT_MOVIMENTO BETWEEN '2025-01-01' AND '2025-05-31'
AND TIPO_EMISSAO = 'Replace'
)

SELECT DISTINCT
b.CPF,
b.cartao AS cartao_bloqueado,
b.conta,
b.dt_bloq,
b.status_bloq,
r.cartao AS cartao_reemitido,
r.DT_MOVIMENTO,
r.TIPO_EMISSAO,
r.DESCRICAO_STATUS,
r.CIDADE,
r.ESTADO,
r.MOTIVO_DEVOLUCAO
FROM reemissoes r
inner JOIN bloqueios b
ON r.conta = b.conta
AND PARSE_DATE('%Y%m%d', b.dt_bloq) < r.DT_MOVIMENTO

Olá, Arthur, tudo bem?

O problema que você está enfrentando, de receber várias linhas por cartão reemitido, acredito que pode ser resolvido utilizando a função ROW_NUMBER() para ordenar os bloqueios por data e selecionar apenas o mais recente para cada cartão reemitido.

O ajuste da sua consulta ficaria mais ou menos assim:

WITH bloqueios AS (
  SELECT
    SUBSTR(CAST(a.lastgeneralstatusupdate AS STRING), 0, 6) AS mes_bloq,
    SUBSTR(CAST(a.lastgeneralstatusupdate AS STRING), 0, 8) AS dt_bloq,
    a.numberx AS cartao,
    a.stgeneral AS status_bloq,
    b.numberx AS conta,
    c.ssnumber AS CPF,
    ROW_NUMBER() OVER (PARTITION BY a.numberx ORDER BY a.lastgeneralstatusupdate DESC) AS rn_bloq
  FROM trusted.tsys_cardx a
  INNER JOIN trusted.tsys_caccounts b ON a.caccserno = b.serno
  INNER JOIN trusted.tsys_people c ON b.peopleserno = c.serno
  WHERE a.stgeneral != 'NORM'
    AND a.virtual = 0
    AND SUBSTR(CAST(a.lastgeneralstatusupdate AS STRING), 0, 6) IN ('202412','202501','202502','202503','202504', '202505')
),
reemissoes AS (
  SELECT
    NUMERO_CARTAO AS cartao,
    NUMERO_CONTA AS conta,
    AR_POSTAGEM,
    DT_ABERTURA_CARTAO,
    TIPO_EMISSAO,
    CIDADE,
    ESTADO,
    DT_MOVIMENTO,
    STATUS_CARDTRACKING,
    DESCRICAO_STATUS,
    DT_ATUALIZACAO_STATUS,
    MOTIVO_DEVOLUCAO
  FROM (
    SELECT *,
      ROW_NUMBER() OVER (PARTITION BY COD_POSTAGEM ORDER BY DT_ATUALIZACAO_STATUS DESC, HR_ATUALIZACAO DESC) AS rn
    FROM `csf-core-data-prd.trusted.mttechne_rastreio_cartao`
    WHERE ingestion_timestamp >= "2024-09-01"
  )
  WHERE rn = 1
    AND DT_MOVIMENTO BETWEEN '2025-01-01' AND '2025-05-31'
    AND TIPO_EMISSAO = 'Replace'
)

SELECT DISTINCT
  b.CPF,
  b.cartao AS cartao_bloqueado,
  b.conta,
  b.dt_bloq,
  b.status_bloq,
  r.cartao AS cartao_reemitido,
  r.DT_MOVIMENTO,
  r.TIPO_EMISSAO,
  r.DESCRICAO_STATUS,
  r.CIDADE,
  r.ESTADO,
  r.MOTIVO_DEVOLUCAO
FROM reemissoes r
INNER JOIN (
  SELECT * FROM bloqueios WHERE rn_bloq = 1
) b ON r.conta = b.conta
AND PARSE_DATE('%Y%m%d', b.dt_bloq) < r.DT_MOVIMENTO

Neste ajuste, adicionei a função ROW_NUMBER() na CTE bloqueios para garantir que você pegue apenas o último bloqueio por cartão antes da reemissão. Assim, ao fazer o JOIN, você deve obter apenas uma linha por cartão reemitido com o último bloqueio correspondente.

Peço que faça o teste e veja se funciona!

Nota: Como é um projeto externo, a qual não tenho acesso, possa ser necessário fazer alguns ajustes a mais para alcançar o objetivo.

Abraços e bons estudos!

Para saber mais:

Sugestão de conteúdo para você mergulhar ainda mais sobre o tema:

[Documentação] - ROW_NUMBER

Conte com o apoio da comunidade Alura na sua jornada. Abraços e bons estudos!