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:

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

Good luck!

Notes:

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

Hint: Documention of pg_trgm

Solution:

create extension pg_trgm;create index prospects_idx on prospects using gin(full_name gin_trgm_ops);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 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

Link

Reference

--

--

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
Isabelle

Isabelle

In love with telling stories with data