1. Learn
    2. /
  2. Courses
    3. /
  3. Exploratory Data Analysis in SQL

Exercise

Group and recode values

There are almost 150 distinct values of evanston311.category. But some of these categories are similar, with the form "Main Category - Details". We can get a better sense of what requests are common if we aggregate by the main category.

To do this, create a temporary table recode mapping distinct category values to new, standardized values. Make the standardized values the part of the category before a dash ('-'). Extract this value with the split_part() function:

split_part(string text, delimiter text, field int)

You'll also need to do some additional cleanup of a few cases that don't fit this pattern.

Then the evanston311 table can be joined to recode to group requests by the new standardized category values.

Instructions 1/4

undefined XP
    1
    2
    3
    4
  • Create recode with a standardized column; use split_part() and then rtrim() to remove any remaining whitespace on the result of split_part().