SQL from zero · Chapter 8 of 37

Lists inside rows

By the end of this chapter you can look inside a list stored in a row, turn a list into rows and back, and cut a piece out of a text. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Counting things
  • 5 tasks
  • SQL
  • In the all labs plan

The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.

Why you need this

Meera wants a "people like you" box on the profile page: members who like the same things. MingleSphere already stores what each member likes, but not one interest per row: interests is a text[], a whole list in one column ({music,travel,food}). Vikram has a question about text too: which email providers do our members use?

What you know so far compares a column with one value. On a list that fails: WHERE interests = 'music' stops with malformed array literal: "music", and LIKE '%music%' gives operator does not exist: text[] ~~ unknown. You would end up reading 24 lists by eye and counting interests by hand, and the domain of an email is hidden inside a longer text.

What you build: 5 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    chai-fans.sql: members interested in chai

    List the members whose interests include chai, with how many interests each of them has. Every member counts, including members who left.

    Return two columns: username and interest_count. Any order.

  2. Task 2

    music-or-travel.sql: members interested in music or travel

    List the members interested in music or travel, or both. Each member appears once, even if they like both. Every member counts, including members who left.

    Return one column: username. Any order.

  3. Task 3

    interest-counts.sql: members per interest

    For every interest that at least one member has, count how many members have it. Every member counts, including members who left.

    Return two columns: interest and members. Any order.

  4. Task 4

    city-roster.sql: each city's members in one text

    One row per city, leaving out members with no city: the city, and its members' usernames in one text, A to Z, separated by a comma and a space, like omar, priya, sneha. Members who left still count.

    Return two columns: city and usernames. Any order for the rows.

  5. Task 5 · interview question

    Interview: email-domains.sql, members per email domain

    For each email domain (the part of the email after the @, like gmail.com), count how many members use it. Every member counts, including members who left.

    Return two columns: domain and members. Most members first; when two domains have the same number, sort them A to Z.

What you will learn

  • Array tests (= ANY, @>, &&, cardinality): is a value in the list, are all of these, is any of these, and how long the list is.
  • unnest: turn each item of a list into its own row, so you can group and count items.
  • string_agg and array_agg: the way back, a group's values glued into one text or one list.
  • Text tools (split_part, position, substring): cut a piece out of a text, like the domain of an email.

More in Counting things

Build "Lists inside rows" yourself

MingleSphereQL, a social app, needs answers from its data every day. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.