4 kyu

Your company has an internal policy to determine your customers’ credit limit, but this procedure has been questioned recently by the board as being too conservative.

Your CEO wants to increase the current customer base credit limits in order to upsell a new line of products. In order to do that, the company hired several external consultancies to produce new credit limit estimates.

The problem is that each agency has produced the report in its own format. Some use the format "First-name Last-name" to identify a person, others use the format "Last-name, First-name". There is also no consensus on how to capitalize each word, so some used all uppercase, others used all lowercase, and some used mixed-case.

Internally, the data is structured as follows:

Table: customers
id: INT
first_name: TEXT
last_name: TEXT
credit_limit: FLOAT

The data you’ve received from all agencies was consolidated in the following table:

Table: prospects
full_name: TEXT
credit_limit: FLOAT

Keep in mind that the agencies had access only to a partial customer base. There is also the possibility of more than one agency prospecting the same customer, so it’s highly likely that there will be duplicates. Finally, they’ve prospected customers that were not in your customer base as well.

For this task you are interested in the prospected customers that are already in your customer base and the prospected credit limit is higher than your internal estimate. When more than one agency prospected the same customer, chose the highest estimate.

You have to produce a report with the following fields:

old_limit [the current credit_limit]
new_limit [the highest credit_limit found]

Good luck!


  • only list the customers that a higher credit limit was found.


create extension pg_trgm;create index prospects_idx on prospects using gin(full_name gin_trgm_ops);SELECT
c.credit_limit AS old_limit,
max(p.credit_limit) AS new_limit
customers c,
prospects p
p.full_name ILIKE '%' || c.first_name || '%'
AND p.full_name ILIKE '%' || c.last_name || '%'
AND c.credit_limit < p.credit_limit
c.first_name, c.last_name, c.credit_limit

Other Solution:

SELECT c.first_name
, c.last_name
, c.credit_limit as old_limit
, max(p.credit_limit) as new_limit
FROM customers c
, prospects p
WHERE upper(c.first_name) || ' ' || upper(c.last_name) = upper(p.full_name)
OR upper(c.Last_name) || ', ' || upper(c.first_name) = upper(p.full_name)
GROUP BY c.first_name, c.last_name, c.credit_limit
HAVING max(p.credit_limit) > c.credit_limit
ORDER BY c.first_name, c.last_name





Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store