Skip to content
Quantoros

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

Related questions