Scribd
Upload
8 views3 pages

PostgreSQL CTE for Banking Analysis

The document provides advanced PostgreSQL Common Table Expressions (CTE) examples for banking tables. It includes queries for summarizing account types by branch, ranking branches by total balance, identifying branches without 'Current' accounts, aggregating balances and account counts by account type, showing branches with above-average balances, and identifying customers with multiple account types. Each example demonstrates the use of CTEs to efficiently retrieve and analyze banking data.

Uploaded by

joejokelly
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
8 views3 pages

PostgreSQL CTE for Banking Analysis

The document provides advanced PostgreSQL Common Table Expressions (CTE) examples for banking tables. It includes queries for summarizing account types by branch, ranking branches by total balance, identifying branches without 'Current' accounts, aggregating balances and account counts by account type, showing branches with above-average balances, and identifying customers with multiple account types. Each example demonstrates the use of CTEs to efficiently retrieve and analyze banking data.

Uploaded by

joejokelly
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Advanced PostgreSQL CTE Examples for Banking Tables

1. Branch-wise Account Type Summary


Show how many accounts of each type exist in every branch.

WITH AccountTypeCounts AS (
SELECT
[Link],
AT.TYPE_NAME,
COUNT(*) AS TOTAL_ACCOUNTS
FROM ACCOUNT A
JOIN BANK_BRANCH BB ON [Link] = [Link]
JOIN ACCOUNT_TYPE AT ON A.TYPE_ID = AT.TYPE_ID
GROUP BY [Link], AT.TYPE_NAME
)
SELECT *
FROM AccountTypeCounts
ORDER BY BNAME, TYPE_NAME;

2. Top 3 Branches by Total Balance


Find the top 3 branches ranked by the sum of their account balances.

WITH BranchBalances AS (
SELECT
[Link],
SUM([Link]) AS TOTAL_BALANCE
FROM BANK_BRANCH BB
LEFT JOIN ACCOUNT A ON [Link] = [Link]
GROUP BY [Link]
),
RankedBranches AS (
SELECT *,
RANK() OVER (ORDER BY TOTAL_BALANCE DESC) AS BAL_RANK
FROM BranchBalances
)
SELECT *
FROM RankedBranches
WHERE BAL_RANK <= 3;

3. Branches with No 'Current' Accounts


List all branches that do not have any accounts of type 'Current'.

WITH BranchWithCurrent AS (
SELECT DISTINCT [Link]
FROM ACCOUNT A
JOIN ACCOUNT_TYPE AT ON A.TYPE_ID = AT.TYPE_ID
WHERE AT.TYPE_NAME = 'Current'
)
SELECT [Link]
FROM BANK_BRANCH BB
LEFT JOIN BranchWithCurrent BWC ON [Link] = [Link]
WHERE [Link] IS NULL;

4. Total Balance and Account Count Per Account Type


Aggregate balances and account counts grouped by account type.

WITH TypeSummary AS (
SELECT
AT.TYPE_NAME,
COUNT(A.ACC_NO) AS TOTAL_ACCOUNTS,
SUM([Link]) AS TOTAL_BALANCE
FROM ACCOUNT A
JOIN ACCOUNT_TYPE AT ON A.TYPE_ID = AT.TYPE_ID
GROUP BY AT.TYPE_NAME
)
SELECT * FROM TypeSummary;

5. Branches Where Average Balance > Overall Average


Show branches whose average account balance is above the global average.

WITH BranchAvg AS (
SELECT
[Link],
AVG([Link]) AS BRANCH_AVG
FROM BANK_BRANCH BB
JOIN ACCOUNT A ON [Link] = [Link]
GROUP BY [Link]
),
OverallAvg AS (
SELECT AVG(BALANCE) AS ALL_AVG FROM ACCOUNT
)
SELECT BA.*
FROM BranchAvg BA, OverallAvg OA
WHERE BA.BRANCH_AVG > OA.ALL_AVG;

6. Customers Holding Multiple Account Types


Identify customers who hold more than one type of account.

WITH TypeCountPerCustomer AS (
SELECT
CUST_NAME,
COUNT(DISTINCT TYPE_ID) AS TYPE_COUNT
FROM ACCOUNT
GROUP BY CUST_NAME
)
SELECT *
FROM TypeCountPerCustomer
WHERE TYPE_COUNT > 1;

Common questions

Powered by AI

To find the top three bank branches ranked by the sum of their account balances, we design a multi-step CTE process. First, a CTE named BranchBalances is used to calculate the total balances for each branch by summing up the account balances after joining the BANK_BRANCH and ACCOUNT tables on branch codes. Then, a second CTE named RankedBranches uses the RANK() window function ordered by TOTAL_BALANCE in descending order to rank the branches. Finally, selecting from RankedBranches where the rank is less than or equal to 3 yields the result: WITH BranchBalances AS (SELECT BB.BNAME, SUM(A.BALANCE) AS TOTAL_BALANCE FROM BANK_BRANCH BB LEFT JOIN ACCOUNT A ON BB.BCODE = A.BCODE GROUP BY BB.BNAME), RankedBranches AS (SELECT *, RANK() OVER (ORDER BY TOTAL_BALANCE DESC) AS BAL_RANK FROM BranchBalances) SELECT * FROM RankedBranches WHERE BAL_RANK <= 3 .

We can list branches that have no 'Current' type accounts by using a CTE named BranchWithCurrent to identify branch codes associated with 'Current' accounts. This step involves joining ACCOUNT and ACCOUNT_TYPE on type IDs and filtering where the account type name is 'Current'. In the final query, we perform a LEFT JOIN between BANK_BRANCH and BranchWithCurrent to identify branches that do not match any branch code in the CTE by selecting entries where BWC.BCODE is NULL: WITH BranchWithCurrent AS (SELECT DISTINCT A.BCODE FROM ACCOUNT A JOIN ACCOUNT_TYPE AT ON A.TYPE_ID = AT.TYPE_ID WHERE AT.TYPE_NAME = 'Current') SELECT BB.BNAME FROM BANK_BRANCH BB LEFT JOIN BranchWithCurrent BWC ON BB.BCODE = BWC.BCODE WHERE BWC.BCODE IS NULL .

To solve this challenge, we combine logic from two distinct SQL queries. First, identify the top three branches by total balance using a CTE that calculates total balances and ranks them. Second, craft a separate CTE to list all branches without 'Current' accounts by using a filtering technique with a LEFT JOIN. Combine the two results by a final query selecting only branches that appear in both results. This involves intersecting the outputs of the RankedBranches CTE and the filtered set of branches without 'Current' accounts, with a typical SQL conditional filtering method: (SELECT BNAME FROM RankedBranches WHERE BAL_RANK <= 3) INTERSECT (SELECT BB.BNAME FROM BANK_BRANCH BB LEFT JOIN (SELECT DISTINCT A.BCODE FROM ACCOUNT A JOIN ACCOUNT_TYPE AT ON A.TYPE_ID = AT.TYPE_ID WHERE AT.TYPE_NAME = 'Current') BWC ON BB.BCODE = BWC.BCODE WHERE BWC.BCODE IS NULL).

We can determine the number of accounts of each type at every branch by using a CTE named AccountTypeCounts. The process involves joining the ACCOUNT table with the BANK_BRANCH table on the branch code and the ACCOUNT_TYPE table on the type ID. This allows us to group the data by branch name and account type, and then count the total number of accounts for each type at each branch. The CTE is structured as follows: WITH AccountTypeCounts AS (SELECT BB.BNAME, AT.TYPE_NAME, COUNT(*) AS TOTAL_ACCOUNTS FROM ACCOUNT A JOIN BANK_BRANCH BB ON A.BCODE = BB.BCODE JOIN ACCOUNT_TYPE AT ON A.TYPE_ID = AT.TYPE_ID GROUP BY BB.BNAME, AT.TYPE_NAME). Once we have the CTE, we can select and order the results by branch name and type name to get the desired summary .

Approaching this requires calculating dual criteria rankings using two aspects: total balance and account count per branch. First, use CTEs to rank branches by both criteria separately using RANK functions. An additional CTE calculates the ranks by total balance and another by account count. Subsequently, find the intersection of branches ranked in the top 3 of both criteria: WITH BalanceRank AS (SELECT BB.BNAME, RANK() OVER (ORDER BY SUM(A.BALANCE) DESC) AS BAL_RANK FROM BANK_BRANCH BB JOIN ACCOUNT A ON BB.BCODE = A.BCODE GROUP BY BB.BNAME), CountRank AS (SELECT BB.BNAME, RANK() OVER (ORDER BY COUNT(A.ACC_NO) DESC) AS CNT_RANK FROM BANK_BRANCH BB JOIN ACCOUNT A ON BB.BCODE = A.BCODE GROUP BY BB.BNAME), DualRankedBranches AS (SELECT BR.BNAME FROM BalanceRank BR JOIN CountRank CR ON BR.BNAME = CR.BNAME WHERE BR.BAL_RANK <= 3 AND CR.CNT_RANK <= 3) SELECT * FROM DualRankedBranches [No direct source provided in prompt, but inferred from SQL logic in Source 1].

To calculate the total balance and number of accounts per account type, a CTE named TypeSummary is used. This involves grouping the ACCOUNT table by account type through a join with the ACCOUNT_TYPE table based on type IDs. We count the accounts and sum their balances for each account type, then retrieve the results: WITH TypeSummary AS (SELECT AT.TYPE_NAME, COUNT(A.ACC_NO) AS TOTAL_ACCOUNTS, SUM(A.BALANCE) AS TOTAL_BALANCE FROM ACCOUNT A JOIN ACCOUNT_TYPE AT ON A.TYPE_ID = AT.TYPE_ID GROUP BY AT.TYPE_NAME) SELECT * FROM TypeSummary .

To determine the branch with the most diverse account types, we first calculate the distinct count of account types per branch. A CTE groups accounts by branch and counts unique type IDs. Next, rank branches by this diversity score using a RANK function ordered by type diversity. Finally, select the top-ranked branch to identify the branch with the greatest account type diversity: WITH TypeDiversity AS (SELECT BB.BNAME, COUNT(DISTINCT A.TYPE_ID) AS DIVERSITY_SCORE FROM BANK_BRANCH BB JOIN ACCOUNT A ON BB.BCODE = A.BCODE GROUP BY BB.BNAME), RankedBranches AS (SELECT BNAME, RANK() OVER (ORDER BY DIVERSITY_SCORE DESC) AS DIVERSITY_RANK FROM TypeDiversity) SELECT BNAME FROM RankedBranches WHERE DIVERSITY_RANK = 1 [No direct source provided in prompt, but inferred from SQL logic in Source 1].

The solution leverages CTEs and PostgreSQL's window functions. Start by calculating branch average balances and comparing against global balance averages using AVG() function. With these averages, filter customers whose branch average balances exceed the overall average. A CTE can then join on branch codes to select distinct customer names: WITH BranchBalance AS (SELECT BCODE, AVG(BALANCE) AS BRANCH_AVG FROM ACCOUNT GROUP BY BCODE), OverallAvg AS (SELECT AVG(BALANCE) AS OVERALL_AVG FROM ACCOUNT), FilteredBranches AS (SELECT BCODE FROM BranchBalance BB, OverallAvg OV WHERE BB.BRANCH_AVG > OV.OVERALL_AVG), CustomerList AS (SELECT DISTINCT CUST_NAME FROM ACCOUNT A WHERE A.BCODE IN (SELECT BCODE FROM FilteredBranches)) SELECT * FROM CustomerList .

To identify branches with an average balance higher than the overall average, two CTEs are crafted: BranchAvg and OverallAvg. BranchAvg calculates the average balance per branch by joining BANK_BRANCH with ACCOUNT, grouping by branch name, and calculating averages. OverallAvg computes the global average balance by averaging the entire ACCOUNT table balances. The final query selects branches from BranchAvg with an average greater than the overall average from OverallAvg: WITH BranchAvg AS (SELECT BB.BNAME, AVG(A.BALANCE) AS BRANCH_AVG FROM BANK_BRANCH BB JOIN ACCOUNT A ON BB.BCODE = A.BCODE GROUP BY BB.BNAME), OverallAvg AS (SELECT AVG(BALANCE) AS ALL_AVG FROM ACCOUNT) SELECT BA.* FROM BranchAvg BA, OverallAvg OA WHERE BA.BRANCH_AVG > OA.ALL_AVG .

To identify customers who have more than one type of account, a CTE named TypeCountPerCustomer is utilized. This involves grouping the ACCOUNT table by customer name (CUST_NAME) and counting distinct account type IDs for each customer. The final selection retrieves customers with a type count greater than one: WITH TypeCountPerCustomer AS (SELECT CUST_NAME, COUNT(DISTINCT TYPE_ID) AS TYPE_COUNT FROM ACCOUNT GROUP BY CUST_NAME) SELECT * FROM TypeCountPerCustomer WHERE TYPE_COUNT > 1 .

You might also like