Skip to content
Quantoros

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

Related questions