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