Return the second-highest distinct creditlimit among creditcard accounts. Any of the three classic approaches is fine.…
SQLLevel: IntermediateWindow functions
Return the second-highest distinct credit_limit among credit_card accounts. Any of the three classic approaches is fine.
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: MAX below MAX, or ORDER BY DESC LIMIT 1 OFFSET 1 on distinct values, or DENSE_RANK = 2.
SELECT MAX(credit_limit) AS second_highest
FROM accounts
WHERE product_type = 'credit_card'
AND credit_limit < (SELECT MAX(credit_limit) FROM accounts WHERE product_type = 'credit_card');
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