1. Learn
    2. /
  2. Courses
    3. /
  3. Cleaning Data in PostgreSQL Databases

Exercise

Aggregating film categories

For the final exercise in this course, let's return to the film_permit table. It contains a community_board TEXT column composed of a comma-separated list of integers. There is interest in doing an analysis of the types of film permits that are being provided for each community board. However, the representation of community boards (INTEGERs in a TEXT column) makes this difficult. By using techniques learned in this chapter, the data can be transformed to allow for such an analysis.

In this exercise, you will first create a (temporary) VIEW that represents the community_board values individually for two permit categories. A VIEW is a named query that can be used like a TABLE once created. You will use this VIEW in a subquery for aggregating the results in a pivot table.

Instructions 1/2

undefined XP
    1
    2
  • Use REGEXP_SPLIT_TO_TABLE() to split community_board into multiple rows using a comma (',') followed by a space character (' ') as the 2-character delimiter.
  • Restrict the category values to 'Film', 'Television', and 'Documentary'.