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.

Hint: Documention of pg_trgm


create extension pg_trgm;create index prospects_idx on prospects using gin(full_name gin_trgm_ops);select c.first_name,
c.credit_limit as old_limit,
max(p.credit_limit) as new_limit
from customers c, prospects p
where p.full_name ILIKE '%' ||c.first_name || '%'and p.full_name ILIKE '%' || c.last_name || '%'
and p.credit_limit > c.credit_limit
group by c.first_name, c.last_name, c.credit_limit
order by first_name;

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






My homepage to record my thought processes for solving SQL and Algorithm questions

Recommended from Medium

Tudo sobre APIM( Azure API Management)

How to find IFSC code and BSR Code Online

ROG Claymore RGB Backlight goes dark mode

Simple Method to align a dish

How to set ENV variables for specific rake tasks in Rails application?

Java Identifiers and Reserved Words and control statements

📱 Working with Android Shared Preferences

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


In love with telling stories with data

More from Medium

5 Advance SQL functions you must know

T-SQL :: DELETE All Data in Schema

Your SQL Server Colleagues

SQL Introduction 1

An internal hard drive

02. One SQL A Day (17/06/22)