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
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.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