Return each account status, the total creditlimit in that status, and its percent of all credit limit, rounded to 1…
SQLLevel: IntermediateServicing and risk analytics
Return each account status, the total credit_limit in that status, and its percent of all credit limit, rounded to 1 decimal (pct_of_exposure). "How much exposure sits in each bucket" is the first question in any loss conversation.
Table definitions (from the source exercise):
CREATE TABLE accounts(account_id INTEGER PRIMARY KEY, customer_id INTEGER, product_type TEXT, open_date TEXT, credit_limit INTEGER, status TEXT);
Hints
- Define one output row and the source-table keys.
- Check duplicates, NULLs and the order of filtering and aggregation.
Answer
Approach: GROUP BY status; for the percent, divide by SUM(SUM(credit_limit)) OVER () - a window over an aggregate - or use a scalar subquery.
SELECT status,
SUM(credit_limit) AS total_limit,
ROUND(100.0 * SUM(credit_limit) / SUM(SUM(credit_limit)) OVER (), 1) AS pct_of_exposure
FROM accounts
GROUP BY status;
The source uses SQLite with a MySQL compatibility layer. Date and string functions are dialect dependent; the query is preserved as published.
Source and licence: SQL Practice Lab — Ebrahim Sharifi · MIT