RPA Fraud Detection Challenge 7

Hi all,
This is my code for challenge number 7 in the RPA FRAUD DETECTION project but it does not return any values.

SELECT full_name, zip_code
FROM transaction_data
WHERE zip_code BETWEEN '300%' AND '319%'

Is this correct ?

After looking the forums I found this solution:

ALTER TABLE transaction_data
ADD COLUMN zip_code VARCHAR;

UPDATE transaction_data

SET zip_code = zip;

SELECT full_name, zip_code FROM transaction_data
WHERE zip_code BETWEEN '300%' AND '319%' OR  zip_code  LIKE '398%';

I’m not sure why do we have to create a new column and set it LIKE ‘398%’

Thank you

Could you please link the lesson, some context might help :slightly_smiling_face:.

Sorry about that this is the lesson

https://www.codecademy.com/paths/data-analyst/tracks/dacp-data-acquisition/modules/dscp-sql-challenge-projects/projects/reputable-company

Ta. There are far too many lessons to remember off-hand and there an awful lot of lessons to try and look through otherwise.

Do you have the details for challenge 7?

Is it written as a comment or something? I don’t believe it was there when I did the course and I can only see the 6 main targets and the solution I wrote.

Yes it is written as a comment.

– 7

– Challenge

– Return only those customers residing in GA. Use the list of ZIP CODE prefixes

– (List of ZIP Code prefixes - Wikipedia)

– to determine the best query for zip codes belonging to Georgia(GA).

The original zip column is an integer type so you could shift this query a little to get a similar output-

BETWEEN 30000 AND 31999

So I don’t think you have to add additional columns; perhaps a simpler route if you want the string patterns instead of the explicit integers might be to CAST the type before the filtering (though it’d need something like a CTE/subquery or HAVING due to the execution order of WHERE).

I’m not all that familiar with US zip codes but so far as I know they aren’t all perfectly grouped, your links suggests there are codes for “GA”-Georgia at 398** and 399**. I’m not really sure if the 399** range is public but perhaps the original poster is more familiar with them and the 398** range are valid Georgia zip codes.

1 Like

I had a facepalm moment when I read your answer. Thanks so much! :smiley:

1 Like