Frosty Friday

Snowflake Weekly Challenges

You are here: Home / Medium / Week 60 – Intermediate

by on 3 Comments

Week 60 – Intermediate

We’ve had some issues with our call center


When a client calls, they give their name and our tech support checks it in the database. If the client can’t be found, a new client is created.

We thought we created a fool-proof system but alas, it is not so. If someone spells a name differently then they can’t be found! This news was so shocking that we’re tasking you to filter our database on those similar-sounding names.

As a proof of concept, we’ve got the following table :

And we expect you to find that row 1 and 2 are similar sounding , but row 5 and 6 are not

In the returned table, we’d like you to show that you’ve compared row 1 to row 2-14, but that row 2 is going to be checked against 3-14 (but not row 1 since you’ve already checked):

Your answer should contain 91 rows
CREATE OR REPLACE TABLE week_60 (
    name VARCHAR
);

INSERT INTO week_60 (name)
VALUES
    ('John Smith'),
    ('Jon Smyth'),
    ('Jane Doe'),
    ('Jan Do'),
    ('Michael Johnson'),
    ('Mike Johnson'),
    ('Sarah Williams'),
    ('Sara Williams'),
    ('Robert Brown'),
    ('Roberto Brown'),
    ('Emily White'),
    ('Emilie Whyte'),
    ('David Lee'),
    ('Davey Li');

Good luck!

Reader Interactions

Comments

  1. I think this may be the first challenge where I’d never even heard of the functionality. Apparently it’s been in Snowflake since September 2022! Thanks for bringing it to my attention

    • Solution URL - https://github.com/ChrisHastieIW/Frosty-Friday

  2. I think I still prefer some fuzzy matching that I can understand almost as poorly as I understand the sound like algorithms…

    • Solution URL - https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_60.sql

Leave a Reply