Templates

SELECT
  DATE_TRUNC('day', TRY_CAST(created_at AS TIMESTAMP)) AS day,
  currency,
  COUNT(*) AS txn_count,
  SUM(
    TRY_CAST(precise_amount AS DECIMAL(38, 8))
    / NULLIF(TRY_CAST(precision AS DECIMAL(38, 8)), 0)
  ) AS volume
FROM transactions
WHERE status = 'APPLIED'
GROUP BY 1, 2
ORDER BY 1 DESC, 2
LIMIT 1000

SELECT
  DATE_TRUNC('day', TRY_CAST(created_at AS TIMESTAMP)) AS day,
  status,
  currency,
  COUNT(*) AS txn_count,
  SUM(
    TRY_CAST(precise_amount AS DECIMAL(38, 8))
    / NULLIF(TRY_CAST(precision AS DECIMAL(38, 8)), 0)
  ) AS volume
FROM transactions
GROUP BY 1, 2, 3
ORDER BY 1 DESC, txn_count DESC
LIMIT 1000

SELECT
  status,
  currency,
  COUNT(*) AS txn_count
FROM transactions
GROUP BY status, currency
ORDER BY txn_count DESC
LIMIT 1000

SELECT
  transaction_id,
  reference,
  currency,
  status,
  source,
  destination,
  created_at
FROM transactions
WHERE status = 'REJECTED'
ORDER BY TRY_CAST(created_at AS TIMESTAMP) DESC
LIMIT 1000

WITH outcomes AS (
  SELECT
    CASE
      WHEN p.status = 'QUEUED' THEN p.parent_transaction
      ELSE c.parent_transaction
    END AS inflight_id,
    c.status,
    TRY_CAST(c.precise_amount AS DECIMAL(38, 8)) AS precise_amount
  FROM transactions c
  LEFT JOIN transactions p ON c.parent_transaction = p.transaction_id
  WHERE c.status IN ('APPLIED', 'VOID')
)
SELECT
  t.transaction_id,
  t.reference,
  t.currency,
  t.source,
  t.destination,
  t.created_at,
  TRY_CAST(t.precise_amount AS DECIMAL(38, 8)) AS held_precise_amount,
  COALESCE(SUM(CASE WHEN o.status = 'APPLIED' THEN o.precise_amount END), 0) AS committed_precise_amount
FROM transactions t
LEFT JOIN outcomes o ON o.inflight_id = t.transaction_id
WHERE t.status = 'INFLIGHT'
GROUP BY
  t.transaction_id,
  t.reference,
  t.currency,
  t.source,
  t.destination,
  t.created_at,
  t.precise_amount
HAVING
  COUNT(CASE WHEN o.status = 'VOID' THEN 1 END) = 0
  AND COALESCE(SUM(CASE WHEN o.status = 'APPLIED' THEN o.precise_amount END), 0)
    < TRY_CAST(t.precise_amount AS DECIMAL(38, 8))
ORDER BY TRY_CAST(t.created_at AS TIMESTAMP) DESC
LIMIT 1000

SELECT
  destination AS balance_id,
  currency,
  COUNT(*) AS credit_count,
  SUM(
    TRY_CAST(precise_amount AS DECIMAL(38, 8))
    / NULLIF(TRY_CAST(precision AS DECIMAL(38, 8)), 0)
  ) AS inflow
FROM transactions
WHERE status = 'APPLIED'
GROUP BY destination, currency
ORDER BY inflow DESC
LIMIT 100

SELECT
  source AS balance_id,
  currency,
  COUNT(*) AS debit_count,
  SUM(
    TRY_CAST(precise_amount AS DECIMAL(38, 8))
    / NULLIF(TRY_CAST(precision AS DECIMAL(38, 8)), 0)
  ) AS outflow
FROM transactions
WHERE status = 'APPLIED'
GROUP BY source, currency
ORDER BY outflow DESC
LIMIT 100

SELECT
  currency,
  COUNT(*) AS balance_count
FROM (
  SELECT
    balance_id,
    ANY_VALUE(currency) AS currency
  FROM balances
  GROUP BY balance_id
) wallets
GROUP BY currency
ORDER BY balance_count DESC
LIMIT 1000

SELECT
  balance_id,
  ANY_VALUE(currency) AS currency,
  ANY_VALUE(ledger_id) AS ledger_id,
  ANY_VALUE(indicator) AS indicator,
  MIN(TRY_CAST(created_at AS TIMESTAMP)) AS created_at
FROM balances
GROUP BY balance_id
HAVING
  BOOL_AND(identity_id IS NULL)
  AND BOOL_AND(indicator IS NULL OR indicator = '')
ORDER BY created_at DESC
LIMIT 1000

SELECT
  DATE_TRUNC('day', TRY_CAST(created_at AS TIMESTAMP)) AS day,
  COUNT(DISTINCT identity_id) AS identities
FROM identity
GROUP BY 1
ORDER BY 1 DESC
LIMIT 1000

WITH balances_one AS (
  SELECT
    balance_id,
    ANY_VALUE(currency) AS currency,
    ANY_VALUE(identity_id) AS identity_id
  FROM balances
  GROUP BY balance_id
),
identity_one AS (
  SELECT
    identity_id,
    ANY_VALUE(first_name) AS first_name,
    ANY_VALUE(last_name) AS last_name,
    ANY_VALUE(email_address) AS email_address
  FROM identity
  GROUP BY identity_id
)
SELECT
  b.balance_id,
  b.currency,
  i.first_name,
  i.last_name,
  i.email_address,
  COUNT(*) AS applied_txns
FROM transactions t
JOIN balances_one b ON t.destination = b.balance_id
JOIN identity_one i ON b.identity_id = i.identity_id
WHERE t.status = 'APPLIED'
GROUP BY
  b.balance_id,
  b.currency,
  i.first_name,
  i.last_name,
  i.email_address
ORDER BY applied_txns DESC
LIMIT 100