SQL Mini-Assignment — Banking Dataset

SELECT c.customer_name, SUM(a.balance) AS total_balance
FROM customers c
JOIN accounts a ON c.customer_id = a.customer_id
GROUP BY c.customer_name
ORDER BY total_balance DESC;

SELECT c.customer_name
FROM customers c
LEFT JOIN accounts a ON c.customer_id = a.customer_id
LEFT JOIN transactions t ON a.account_id = t.account_id
WHERE t.txn_id IS NULL;
-- → Vikram Singh, Ananya Das

SELECT c.customer_name,
       SUM(CASE WHEN t.txn_type = 'Credit' THEN t.amount
                ELSE -t.amount END) AS net_flow
FROM customers c
JOIN accounts a ON c.customer_id = a.customer_id
JOIN transactions t ON a.account_id = t.account_id
GROUP BY c.customer_name;

SELECT c.customer_name, SUM(t.amount) AS total_value
FROM customers c
JOIN accounts a ON c.customer_id = a.customer_id
JOIN transactions t ON a.account_id = t.account_id
GROUP BY c.customer_name
ORDER BY total_value DESC
LIMIT 3;

SELECT account_type, ROUND(AVG(balance), 0) AS avg_balance
FROM accounts
GROUP BY account_type;

SELECT DISTINCT c.customer_name
FROM customers c
JOIN accounts a ON c.customer_id = a.customer_id
JOIN transactions t ON a.account_id = t.account_id
WHERE c.join_date >= '2023-01-01';

WITH monthly AS (
    SELECT DATE_TRUNC('month', txn_date) AS month, SUM(amount) AS volume
    FROM transactions GROUP BY DATE_TRUNC('month', txn_date)
)
SELECT month, volume,
       LAG(volume) OVER (ORDER BY month) AS prev_volume,
       volume - LAG(volume) OVER (ORDER BY month) AS change
FROM monthly ORDER BY month;

SELECT c.customer_name, SUM(a.balance) AS total_balance,
       CASE
           WHEN SUM(a.balance) >= 100000 THEN 'High'
           WHEN SUM(a.balance) >= 30000  THEN 'Medium'
           ELSE 'Low'
       END AS segment
FROM customers c
JOIN accounts a ON c.customer_id = a.customer_id
GROUP BY c.customer_name;

SELECT MAX(balance) AS second_highest
FROM accounts
WHERE balance < (SELECT MAX(balance) FROM accounts);
-- → 95000

SELECT city, customer_name, total_balance
FROM (
    SELECT c.city, c.customer_name, SUM(a.balance) AS total_balance,
           ROW_NUMBER() OVER (PARTITION BY c.city ORDER BY SUM(a.balance) DESC) AS rn
    FROM customers c
    JOIN accounts a ON c.customer_id = a.customer_id
    GROUP BY c.city, c.customer_name
) t
WHERE rn = 1;