When working with a Microsoft SQL Server database, it is common to need reports that combine data from several related tables. In banking, open banking, or PSD2-style applications, one frequent requirement is to generate a report showing how many accounts, cards, loans, savings accounts, and balances are connected to each user consent.
This article explains, at a beginner-friendly level, how to write and optimize a SQL Server SELECT query that creates such a report.
Example Reporting Requirement
Assume we need a report with the following columns:
- User ID
- Bank ID
- Consent created date
- Number of accounts
- Number of account balances
- Number of cards
- Number of card balances
- Number of loans
- Number of loan balances
- Number of savings
- Number of savings balances
The database contains tables such as:
consentbank_userconsent_accountconsent_account_balanceconsent_cardconsent_card_balanceloans_accountsconsent_loan_balancesavings_accountsconsent_saving_balance
The main table is usually consent, because all account, card, loan, and saving data is linked to a consent ID.
Basic SQL Report Query
A possible SQL Server query is:
SELECT
bu.user_id AS [User ID],
bu.bank_id AS [Bank ID],
c.create_time AS [Consent created date],
COUNT(DISTINCT acc.resource_id) AS [Number of accounts],
COUNT(DISTINCT acc_bal.balance_id) AS [Number of accounts balances],
COUNT(DISTINCT card.resource_id) AS [Number of cards],
COUNT(DISTINCT card_bal.balance_id) AS [Number of cards balances],
COUNT(DISTINCT loan.resource_id) AS [Number of loans],
COUNT(DISTINCT loan_bal.balance_id) AS [Number of loans balances],
COUNT(DISTINCT sav.resource_id) AS [Number of savings],
COUNT(DISTINCT sav_bal.balance_id) AS [Number of savings balances]
FROM dbo.bank_user bu
INNER JOIN dbo.consent c
ON bu.bank_id = c.bank_id
AND bu.user_id = c.user_id
LEFT JOIN dbo.consent_account acc
ON c.id = acc.consent_id
LEFT JOIN dbo.consent_account_balance acc_bal
ON acc.consent_id = acc_bal.consent_id
AND acc.resource_id = acc_bal.resource_id
LEFT JOIN dbo.consent_card card
ON c.id = card.consent_id
LEFT JOIN dbo.consent_card_balance card_bal
ON card.consent_id = card_bal.consent_id
AND card.resource_id = card_bal.resource_id
LEFT JOIN dbo.loans_accounts loan
ON c.id = loan.consent_id
LEFT JOIN dbo.consent_loan_balance loan_bal
ON loan.consent_id = loan_bal.consent_id
AND loan.resource_id = loan_bal.resource_id
LEFT JOIN dbo.savings_accounts sav
ON c.id = sav.consent_id
LEFT JOIN dbo.consent_saving_balance sav_bal
ON sav.consent_id = sav_bal.consent_id
AND sav.resource_id = sav_bal.resource_id
GROUP BY
bu.user_id,
bu.bank_id,
c.create_time;
Why LEFT JOIN Is Used
LEFT JOIN is important because not every consent has all types of data.
For example, a user may have:
- bank accounts, but no loans;
- cards, but no savings;
- savings, but no cards.
Using LEFT JOIN allows the report to still show the consent even when some related data is missing.
Why COUNT DISTINCT Is Used
When joining many one-to-many tables, SQL can multiply rows.
For example, if a consent has:
- 2 accounts;
- 3 balances;
- 2 cards;
a normal COUNT(*) could return inflated results.
That is why COUNT(DISTINCT column) is used. It counts unique values only.
Example:
COUNT(DISTINCT acc.resource_id)
This counts each account only once.
A More Optimized Version
For large databases, joining all tables directly can become slow. A better approach is to pre-aggregate each table first, then join the summarized results.
WITH account_counts AS (
SELECT consent_id, COUNT(DISTINCT resource_id) AS number_of_accounts
FROM dbo.consent_account
GROUP BY consent_id
),
account_balance_counts AS (
SELECT consent_id, COUNT(DISTINCT balance_id) AS number_of_account_balances
FROM dbo.consent_account_balance
GROUP BY consent_id
),
card_counts AS (
SELECT consent_id, COUNT(DISTINCT resource_id) AS number_of_cards
FROM dbo.consent_card
GROUP BY consent_id
),
card_balance_counts AS (
SELECT consent_id, COUNT(DISTINCT balance_id) AS number_of_card_balances
FROM dbo.consent_card_balance
GROUP BY consent_id
),
loan_counts AS (
SELECT consent_id, COUNT(DISTINCT resource_id) AS number_of_loans
FROM dbo.loans_accounts
GROUP BY consent_id
),
loan_balance_counts AS (
SELECT consent_id, COUNT(DISTINCT balance_id) AS number_of_loan_balances
FROM dbo.consent_loan_balance
GROUP BY consent_id
),
saving_counts AS (
SELECT consent_id, COUNT(DISTINCT resource_id) AS number_of_savings
FROM dbo.savings_accounts
GROUP BY consent_id
),
saving_balance_counts AS (
SELECT consent_id, COUNT(DISTINCT balance_id) AS number_of_saving_balances
FROM dbo.consent_saving_balance
GROUP BY consent_id
)
SELECT
c.user_id AS [User ID],
c.bank_id AS [Bank ID],
c.create_time AS [Consent created date],
ISNULL(ac.number_of_accounts, 0) AS [Number of accounts],
ISNULL(abc.number_of_account_balances, 0) AS [Number of accounts balances],
ISNULL(cc.number_of_cards, 0) AS [Number of cards],
ISNULL(cbc.number_of_card_balances, 0) AS [Number of cards balances],
ISNULL(lc.number_of_loans, 0) AS [Number of loans],
ISNULL(lbc.number_of_loan_balances, 0) AS [Number of loans balances],
ISNULL(sc.number_of_savings, 0) AS [Number of savings],
ISNULL(sbc.number_of_saving_balances, 0) AS [Number of savings balances]
FROM dbo.consent c
LEFT JOIN account_counts ac
ON c.id = ac.consent_id
LEFT JOIN account_balance_counts abc
ON c.id = abc.consent_id
LEFT JOIN card_counts cc
ON c.id = cc.consent_id
LEFT JOIN card_balance_counts cbc
ON c.id = cbc.consent_id
LEFT JOIN loan_counts lc
ON c.id = lc.consent_id
LEFT JOIN loan_balance_counts lbc
ON c.id = lbc.consent_id
LEFT JOIN saving_counts sc
ON c.id = sc.consent_id
LEFT JOIN saving_balance_counts sbc
ON c.id = sbc.consent_id
ORDER BY
c.user_id,
c.bank_id,
c.create_time;
Why This Version Can Be Faster
The optimized version first counts data inside each table separately. This avoids large row multiplication caused by joining all detailed tables at once.
Instead of joining thousands or millions of raw rows, SQL Server joins smaller grouped results.
This can improve:
- execution time;
- memory usage;
- readability;
- accuracy of counts.
Recommended Indexes
Indexes are very important for this type of report.
Recommended indexes include:
CREATE INDEX IX_consent_user_bank
ON dbo.consent (user_id, bank_id, id, create_time);
CREATE INDEX IX_consent_account_consent
ON dbo.consent_account (consent_id, resource_id);
CREATE INDEX IX_consent_account_balance_consent
ON dbo.consent_account_balance (consent_id, resource_id, balance_id);
CREATE INDEX IX_consent_card_consent
ON dbo.consent_card (consent_id, resource_id);
CREATE INDEX IX_consent_card_balance_consent
ON dbo.consent_card_balance (consent_id, resource_id, balance_id);
CREATE INDEX IX_loans_accounts_consent
ON dbo.loans_accounts (consent_id, resource_id);
CREATE INDEX IX_consent_loan_balance_consent
ON dbo.consent_loan_balance (consent_id, resource_id, balance_id);
CREATE INDEX IX_savings_accounts_consent
ON dbo.savings_accounts (consent_id, resource_id);
CREATE INDEX IX_consent_saving_balance_consent
ON dbo.consent_saving_balance (consent_id, resource_id, balance_id);
Common Mistakes to Avoid
1. Using COUNT(*)
COUNT(*) may return wrong numbers when several child tables are joined together.
Use:
COUNT(DISTINCT resource_id)
or pre-aggregate with GROUP BY.
2. Joining Balance Tables Incorrectly
Balance tables should usually be joined by both:
consent_id
resource_id
This helps avoid counting balances from the wrong account, card, loan, or saving.
3. Forgetting NULL Values
When using LEFT JOIN, missing data returns NULL.
Use:
ISNULL(value, 0)
to show zero instead of NULL in the report.
4. Missing Indexes
Without indexes, SQL Server may need to scan entire tables, making the report slow.
Conclusion
To create a SQL Server report for user banking consent data, start from the consent table and join the related account, card, loan, saving, and balance tables.
For small databases, a direct query with LEFT JOIN and COUNT(DISTINCT) may be enough.
For larger databases, a better solution is to pre-aggregate each table using CTEs or subqueries, then join the summarized results.
This approach produces a cleaner, faster, and more reliable report.
