-- 29_window_functions_ranking.sql
-- Banking Pattern Pack — PostgreSQL
-- Purpose: Window functions for top customers/branches.
-- Context: Retail bank example with customers, accounts, transactions, cards, loans.
-- Date: 2025-08-18
-- Notes:
--   * Safe to read; run on a development DB.
--   * Requires: CREATE privileges; some files may need superuser for extensions.
--   * Search path set to 'bank' schema for clarity.
SET search_path = bank, public;

-- Window functions: rank branches by monthly net deposits
SELECT b.branch_id, b.name,
       RANK() OVER (ORDER BY SUM(CASE WHEN t.tx_type='credit' THEN t.amount ELSE 0 END) DESC) AS deposit_rank
FROM bank.branches b
LEFT JOIN bank.accounts a ON a.branch_id = b.branch_id
LEFT JOIN bank.transactions t ON t.account_id = a.account_id
WHERE date_trunc('month', t.created_at) = date_trunc('month', CURRENT_DATE)
GROUP BY b.branch_id, b.name;
