Skip to content
Quantoros

Return accountid, the owner's city, and creditlimit for accounts whose limit is strictly above the average credit…

SQLLevel: IntermediateBonus drills (mixed difficulty)

Return account_id, the owner's city, and credit_limit for accounts whose limit is strictly above the average credit limit of all accounts in that same city.

Table definitions (from the source exercise):

CREATE TABLE customers(customer_id INTEGER PRIMARY KEY, name TEXT, city TEXT, signup_date TEXT, credit_score INTEGER);
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: Attach AVG(credit_limit) OVER (PARTITION BY city) to every row in a subquery, then filter outside. A correlated subquery also works.

SELECT account_id, city, credit_limit
FROM (
  SELECT a.account_id, c.city, a.credit_limit,
         AVG(a.credit_limit) OVER (PARTITION BY c.city) AS city_avg
  FROM accounts a
  JOIN customers c ON c.customer_id = a.customer_id
) x
WHERE credit_limit > city_avg;

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