Find All People With Secret - Leetcode Solution

Problem Statement:

You are given a table, Secret, that contains the following columns:

• ID: unique id of the person.
• Name: name of the person.
• Phone: phone number of the person.
• Email: email address of the person.

A person has a secret if any of the following conditions are met:

• Their phone number is a palindrome.
• Their email address starts with a palindrome and ends with a palindrome.

Write an SQL query to find all the people with a secret.

Solution:

We have to find all the people who have a secret, which is defined as having a phone number that is a palindrome or an email address that starts with a palindrome and ends with a palindrome. We can tackle this problem by dividing it into two parts.

Part 1:

Let's first find all the people with a phone number that is a palindrome. We can do this by converting the phone number to a string and checking if it is equal to its reverse. The phone number column is of type varchar, so we don't have to cast it.

SELECT * FROM Secret
WHERE Phone = REVERSE(Phone)

Part 2:

Next, let's find all the people with an email address that starts with a palindrome and ends with a palindrome. We can use the SUBSTR function to extract the first and last characters of the email address and check if they are equal to their reverse.

SELECT * FROM Secret
WHERE SUBSTR(Email, 1, 1) = REVERSE(SUBSTR(Email, -1, 1)) AND
      SUBSTR(Email, 2, LENGTH(Email)-2) = REVERSE(SUBSTR(Email, 2, LENGTH(Email)-2))

Here we are checking if the first character of the email address is equal to its reverse and if the substring of the email address after the first and last character is also equal to its reverse.

Final Query:

We can combine the above two queries using the UNION operator to get all the people with a secret.

SELECT * FROM Secret
WHERE Phone = REVERSE(Phone)

UNION

SELECT * FROM Secret
WHERE SUBSTR(Email, 1, 1) = REVERSE(SUBSTR(Email, -1, 1)) AND
      SUBSTR(Email, 2, LENGTH(Email)-2) = REVERSE(SUBSTR(Email, 2, LENGTH(Email)-2))

This query will give us all the people who have a secret, either through their phone number or email address.

1