Трейдер Operational Dashboard

SELECT SUM(tran.amount) AS payin_volume
FROM "Transaction" tran
JOIN "BankAccount" ba ON tran."bankAccount" = ba.id
JOIN "AccountProvider" ap ON ba."accountProvider" = ap.id
WHERE tran.operation = 'payin'
  AND (tran."createdAt" AT TIME ZONE 'UTC'
       AT TIME ZONE 'Asia/Kolkata')::date = :dt
  AND ap.name != 'Operation provider'

SELECT ABS(SUM(tran.amount)) AS payout_volume
FROM "Transaction" tran
JOIN "BankAccount" ba ON tran."bankAccount" = ba.id
WHERE tran.operation = 'payout'
  AND (tran."createdAt" AT TIME ZONE 'UTC'
       AT TIME ZONE 'Asia/Kolkata')::date = :dt

SELECT COUNT(*) AS active_traders
FROM (
  SELECT ba."accountProvider" AS pid
  FROM "Transaction" tran
  JOIN "BankAccount" ba ON tran."bankAccount" = ba.id
  WHERE tran.operation = 'payin'
    AND (tran."createdAt" ...)::date = :dt
  GROUP BY ba."accountProvider"
  HAVING SUM(tran.amount) > 0
) sub

SELECT SUM(accts) AS active_accounts
FROM (
  SELECT dt, hr, ap.id AS tid,
    COUNT(DISTINCT ba.id) AS accts
  FROM "ClientPayin" cp
  JOIN "BankAccount" ba ON cp."bankAccount" = ba.id
  JOIN "AccountProvider" ap ON ba."accountProvider" = ap.id
  WHERE (cp."createdAt" ...)::date = :dt
  GROUP BY dt, hr, ap.id
) sub

SELECT
  SUM(CASE WHEN cp.status IN ('completed','successed_by_partner')
      THEN 1 END)::float
  / NULLIF(COUNT(cp.id), 0) AS cr_pct
FROM "ClientPayin" cp
JOIN "BankAccount" ba ON cp."bankAccount" = ba.id
JOIN "AccountProvider" ap ON ba."accountProvider" = ap.id
WHERE (cp."createdAt" ...)::date = :dt

SELECT
  SUM(CASE WHEN cp.status IN ('completed','successed_by_partner')
      AND cp."editedBySupport" = false
      THEN 1 END)::float
  / NULLIF(SUM(CASE WHEN cp.status IN ('completed','successed_by_partner')
      THEN 1 END), 0) AS auto_pct
FROM "ClientPayin" cp ...

SELECT SUM(payin_volume) / NULLIF(active_traders, 0)
  AS avg_payin_trader
-- payin_volume: SUM из Transaction
-- active_traders: COUNT(DISTINCT provider)
--   GROUP BY provider HAVING SUM(amount) > 0

SELECT SUM(tran.total) * (-1) AS settlement
FROM "Transaction" tran
JOIN "Expense" e ON e.id = tran."expenseId"
JOIN "ExpenseCategory" ec ON e."category" = ec.id
WHERE tran.operation = 'expense'
  AND ec.label = 'Settlement'
  AND (tran."createdAt" ...)::date = :dt

SELECT SUM(settlement) / NULLIF(SUM(payin_volume), 0)
  AS settle_payin_pct
-- settlement: из Expense (category='Settlement')
-- payin_volume: из Transaction (operation='payin')

SELECT
  SUM(has_statement)::float
  / NULLIF(SUM(total_accounts), 0)
  AS statement_pct
FROM (
  SELECT dt, tid,
    SUM(has_stmt) AS has_statement,
    COUNT(DISTINCT account_number) AS total_accounts
  FROM (
    SELECT DATE(cp."createdAt" + '5h30m') AS dt,
      ba."accountProvider" AS tid,
      ba."account" AS account_number,
      MAX(CASE WHEN baf.id IS NOT NULL
          THEN 1 ELSE 0 END) AS has_stmt
    FROM "ClientPayin" cp
    JOIN "BankAccount" ba ON ...
    LEFT JOIN LATERAL (
      SELECT f.id FROM "BankAccountFile" f
      WHERE f."bankAccount" = ba.id
        AND cp."createdAt" BETWEEN
            f."beginDateTime" AND f."endDateTime"
      LIMIT 1
    ) baf ON TRUE
    GROUP BY dt, tid, account_number
  ) raw
  GROUP BY dt, tid
) agg

-- 1) Найти в History моменты выхода из статуса 'pending'
WITH payout_pending_end AS (
  SELECT "recordId" AS payout_id,
         MIN("createdAt") AS pending_end
  FROM "History"
  WHERE "table" = 'ClientPayout'
    AND "logJson"->'data'->'status'->>'old' = 'pending'
  GROUP BY "recordId"
)
-- 2) Для каждого payout: pending_start=createdAt, pending_end
-- 3) Для каждого получаса T:
--    был pending в момент T ⇔
--    pending_start <= T AND (pending_end IS NULL OR pending_end > T)

SELECT 
  COUNT(*) AS unassigned_count,
  COALESCE(SUM(amount), 0) AS unassigned_amount
FROM "ClientPayin"
WHERE "bankAccount" IS NULL 
  AND ("prevPendingClientPayinId" IS NULL
       OR "prevPendingClientPayinId" = '')
  -- + фильтр по получасу createdAt
  -- + фильтр paymentMethodType (если выбран)

-- Добавляется ко всем запросам:
WHERE hr <= EXTRACT(HOUR FROM
  now() AT TIME ZONE 'Asia/Kolkata')::int

-- Пример: сейчас 14:30 IST
-- → hr <= 14
-- → каждый день: только 00:00–14:00