Codewars SQL

Dealing With Messy Data

Table: customers
================
id: INT
first_name: TEXT
last_name: TEXT
credit_limit: FLOAT
Table: prospects
================
full_name: TEXT
credit_limit: FLOAT
first_name
last_name
old_limit [the current credit_limit]
new_limit [the highest credit_limit found]
  • 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.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 c.first_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