Kata

Back-story

It's Valentines day! 💝 But, ejini战神 is alone at home, solving katas on Codewars whilst browsing through his friends sharing PDA (Public Display of Affection) posts on social media.🥲Out of nowhere, a sudden urge then prompts him to go through dating applications and search for a new date. As one who possess fundamental PostgreSQL knowledge, it is now your job to help ejini战神 find the love of his life !!!🔥

Task

ejini战神 has retrieved a database from a dating application which stores information about individuals who are still single. The database has a table called dating with the following columns:

• user_id: A unique integer identifier for each person
• gender: A string representing gender for each person (M --> male, F --> female)
• full_name: A string representing the person's full name
• birth_date: A date representing the person's birthdate
• height: An integer representing the person's height
• weight: A float representing the person's weight
• personality: A string representing the person's personalities (each personality entry of randomized casing is separated by , )

Your job is to write a SQL query to retrieve all users from the table with the following criteria:

• Must be female (Bromance is a big no no for ejini战神)
• Same age as ejini战神 or younger, but age difference must not exceed 3 years (ejini战神 considers years only when calculating age, not the actual birthdate, also his birthyear is in 2001)
• Must be shorter than 170 cm
• Must be lighter than 60kg
• Must possess all of the following personalities: (Case insensitive)
  • charismatic
  • considerate
  • hardworking
  • sincere
  • smart

Notes

• The personality column must be displayed in lowercase form (ejini战神 has trouble reading uppercase and lowercase texts 🥴), with all other columns remain unchanged.
• The output must be sorted by user_id.
• Only 1 SELECT statement is allowed. (Subqueries are forbidden)
• Only 1 LOWER function call is allowed.
• UPPER function call is not allowed. (ejini战神 has a weird habit of not seeing trivial function calls😉)
• LIKE and ILIKE operators are forbidden.

Desired Output

The output should be:

user_id  | gender | full_name    | birth_date  | height | weight | personality                                                                                                                           |
---------+--------+--------------+-------------+--------+--------+---------------------------------------------------------------------------------------------------------------------------------------+
   31    |   F    | Ellie Hill   | 2002-02-25  | 161    | 53.09  | spontaneous, considerate, respectful, tolerant, optimistic, chivalrous, adventurous, smart, honest, hardworking, sincere, charismatic |
   82    |   F    | Amelia Brown | 2001-07-11  | 154    | 57.41  | charismatic, considerate, hardworking, kind, sincere, smart                                                                           |
...