Rawabi Bank

SELECT a.account_number, p.product_type, a.status,
       a.opening_balance
       + COALESCE(SUM(CASE WHEN t.direction = 'credit' THEN t.amount ELSE -t.amount END), 0) AS balance
FROM accounts AS a
JOIN account_products AS p ON p.product_id = a.product_id
LEFT JOIN transactions AS t ON t.account_id = a.account_id
WHERE a.status <> 'closed'
GROUP BY a.account_id, a.account_number, p.product_type, a.status, a.opening_balance
ORDER BY balance DESC
LIMIT 20;

SELECT date_trunc('month', t.posted_at)::date AS month,
       COALESCE(parent.category_name, 'Uncategorised') AS category_group,
       SUM(t.amount) AS spent
FROM transactions AS t
LEFT JOIN transaction_categories AS c ON c.category_id = t.category_id
LEFT JOIN transaction_categories AS parent ON parent.category_id = c.parent_category_id
WHERE t.direction = 'debit'
  AND t.channel IN ('pos', 'online')
  AND t.reversal_of_transaction_id IS NULL
  AND NOT EXISTS (SELECT 1 FROM transactions r WHERE r.reversal_of_transaction_id = t.transaction_id)
GROUP BY 1, 2
ORDER BY 1, 3 DESC;

SELECT a.account_number, a.status, MAX(t.posted_at) AS last_transaction
FROM accounts AS a
LEFT JOIN transactions AS t ON t.account_id = a.account_id
WHERE a.status <> 'closed'
GROUP BY a.account_id, a.account_number, a.status
HAVING MAX(t.posted_at) < TIMESTAMP '2025-07-01' OR MAX(t.posted_at) IS NULL
ORDER BY last_transaction NULLS FIRST;

SELECT a.account_number, h.holder_role, c.first_name || ' ' || c.last_name AS holder
FROM account_holders AS h
JOIN accounts AS a ON a.account_id = h.account_id
JOIN customers AS c ON c.customer_id = h.customer_id
WHERE h.account_id IN (SELECT account_id FROM account_holders WHERE holder_role = 'joint')
ORDER BY a.account_number, h.holder_role DESC;

SELECT t.posted_at, t.direction, t.amount,
       a.opening_balance + SUM(CASE WHEN t.direction = 'credit' THEN t.amount ELSE -t.amount END)
         OVER (ORDER BY t.posted_at, t.transaction_id) AS running_balance
FROM transactions AS t
JOIN accounts AS a ON a.account_id = t.account_id
WHERE t.account_id = (SELECT account_id FROM transactions GROUP BY account_id ORDER BY count(*) DESC LIMIT 1)
ORDER BY t.posted_at, t.transaction_id;