Условие

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

fct_bank(dateCreated, card_number, status, amount) — данные банка.
fct_ott(dateCreated, card_number, status, amount) — данные компании.

Нужно вывести дельту между банком и компанией с разбивкой по дням и номеру карты. Сравнение по (card_number, dateCreated, status). Если общая сумма по карте за весь период одинакова, дельта должна быть 0, даже если списания и возвраты в разные дни.

Решение

Подход

На уровне «карта × сумма за весь период» сначала проверяем сходимость общей суммы по карте.
Если общие суммы совпадают — дельта = 0, выводим всё с дельтой 0.
Если не совпадают — детализируем разницу по (карта, день, статус).

SQL

WITH agg_bank AS (
  SELECT card_number, SUM(amount) AS total FROM fct_bank GROUP BY card_number
),
agg_ott AS (
  SELECT card_number, SUM(amount) AS total FROM fct_ott  GROUP BY card_number
),
match_cards AS (
  -- Карты, у которых сумма по карте сходится — пропускаем расхождения по дням
  SELECT b.card_number
  FROM agg_bank b JOIN agg_ott o USING (card_number)
  WHERE b.total = o.total
),
day_bank AS (
  SELECT dateCreated, card_number, status, SUM(amount) AS amt FROM fct_bank
  GROUP BY dateCreated, card_number, status
),
day_ott AS (
  SELECT dateCreated, card_number, status, SUM(amount) AS amt FROM fct_ott
  GROUP BY dateCreated, card_number, status
),
joined AS (
  SELECT
    COALESCE(b.dateCreated, o.dateCreated) AS dateCreated,
    COALESCE(b.card_number, o.card_number) AS card_number,
    COALESCE(b.status, o.status)           AS status,
    COALESCE(b.amt, 0) AS bank_amt,
    COALESCE(o.amt, 0) AS ott_amt
  FROM day_bank b
  FULL OUTER JOIN day_ott o
    ON b.dateCreated = o.dateCreated
   AND b.card_number = o.card_number
   AND b.status      = o.status
)
SELECT
  dateCreated,
  card_number,
  status,
  bank_amt,
  ott_amt,
  CASE
    WHEN card_number IN (SELECT card_number FROM match_cards) THEN 0
    ELSE bank_amt - ott_amt
  END AS delta
FROM joined
ORDER BY card_number, dateCreated;

Альтернатива «возврат компенсирует списание»

Если требование жёстче — «по карте списания и возвраты в любые дни сходятся, дельта 0», то можно сразу схлопнуть bank_total = SUM(amount) (с учётом знака возвратов) и сравнивать только итоги по карте; внутри карты выводить детали с нулевой дельтой.

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

FULL OUTER JOIN — обязателен, иначе пропустите случаи, когда транзакция есть только в банке или только в компании.
Знак возвратов: Возврат -9654 — отрицательная сумма. SUM(amount) сразу даст «нетто». Если возвраты положительные с типом Возврат, нужно SIGN.
Карты с одинаковой картой, но разной валютой/MCC — реальная схема может быть сложнее.
Производительность: на миллионах транзакций — индекс по (card_number, dateCreated, status).

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

1) Подсчитать суммы по карте в обеих таблицах; 2) Карты с равными итогами — delta = 0; 3) Для остальных — FULL OUTER JOIN по (date, card, status) + bank_amt − ott_amt.

Делимобиль Middle: SQL-сверка списаний банка и компании по карте