Skip to content
Quantoros

Return one number: the percentage of all accounts with status active, rounded to 1 decimal place, aliased pctactive.…

SQLLevel: FoundationCASE WHEN

Return one number: the percentage of all accounts with status active, rounded to 1 decimal place, aliased pct_active.

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: SUM(CASE WHEN ... THEN 1 ELSE 0 END) over COUNT(*). Multiply by 100.0 to avoid integer division.

SELECT ROUND(100.0 * SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END)
           / COUNT(*), 1) AS pct_active
FROM accounts;

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