Spotify: URL Slugs for Podcast Show Names

Easy
930.

Spotify: URL Slugs for Podcast Show Names

EasyStrings

MySQL

The podcast team is moving shows to readable web addresses built from the show name. Creators typed names by hand, so some have stray spaces at either end and some contain an apostrophe.

For every show, return show_id and url_slug: the name with leading and trailing spaces removed, apostrophes (') deleted, every remaining space replaced by a hyphen, and all letters in lower case. Names never contain two spaces in a row. Order by show_id.

A practice scenario set at Spotify: the tables and data are invented, and this is not a question from, or endorsed by, Spotify.

Tables

Table: PodcastShow

ColumnType
show_idint
show_namevarchar
host_namevarchar

Primary key: show_id.

One row per podcast show, the name exactly as the creator entered it.

Example

The rows must come back in the order shown.

PodcastShow

show_idshow_namehost_name
1Founders UnfilteredIshaan
2 Cricket After Hours Kabir
3Rohan's MixtapeRohan
4The Daily Byte Ananya
5Aditi's Book ClubAditi

Output

show_idurl_slug
1founders-unfiltered
2cricket-after-hours
3rohans-mixtape
4the-daily-byte
5aditis-book-club
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

Spotify: URL Slugs for Podcast Show Names — SQL Problem

The podcast team is moving shows to readable web addresses built from the show name.

  • Difficulty: Easy
  • Topics: Strings
  • Dialect: MySQL
  • Problem: #930

Problem statement

The podcast team is moving shows to readable web addresses built from the show name. Creators typed names by hand, so some have stray spaces at either end and some contain an apostrophe.

For every show, return show_id and url_slug: the name with leading and trailing spaces removed, apostrophes (') deleted, every remaining space replaced by a hyphen, and all letters in lower case. Names never contain two spaces in a row. Order by show_id.

A practice scenario set at Spotify: the tables and data are invented, and this is not a question from, or endorsed by, Spotify.

Tables

Table: PodcastShow

ColumnType
show_idint
show_namevarchar
host_namevarchar

Primary key: show_id.

One row per podcast show, the name exactly as the creator entered it.

Examples

Example 1

PodcastShow

show_idshow_namehost_name
1Founders UnfilteredIshaan
2 Cricket After Hours Kabir
3Rohan's MixtapeRohan
4The Daily Byte Ananya
5Aditi's Book ClubAditi

Output

show_idurl_slug
1founders-unfiltered
2cricket-after-hours
3rohans-mixtape
4the-daily-byte
5aditis-book-club

How to solve Spotify: URL Slugs for Podcast Show Names

A slug is built by a chain of string functions, and the only real decision is their order. TRIM must come before the space-to-hyphen replacement: ' Cricket After Hours ' trimmed is 'Cricket After Hours', which becomes cricket-after-hours; replacing first would leave hyphens at both ends.

Apostrophes are deleted with REPLACE(x, '''', ''). The odd-looking literal is a string containing one apostrophe — inside single quotes, a quote is escaped by doubling it. So Rohan's Mixtape turns into rohans-mixtape. Lower-casing can happen at any point because it does not change spaces or apostrophes, and LOWER and LCASE are synonyms.

The alternatives apply the same steps in other orders — lower-case first, or deleting apostrophes before trimming — and give identical slugs, which is a good way to convince yourself which steps commute. Each row is processed on its own, so the query is a single scan plus the sort by show_id.

Reference solution (MySQL)

SELECT show_id,
       LOWER(REPLACE(REPLACE(TRIM(show_name), '''', ''), ' ', '-')) AS url_slug
FROM PodcastShow
ORDER BY show_id

Another way

SELECT show_id, REPLACE(REPLACE(LCASE(TRIM(show_name)), ' ', '-'), '''', '') AS url_slug FROM PodcastShow ORDER BY show_id

Another way

SELECT show_id, LOWER(REPLACE(TRIM(REPLACE(show_name, '''', '')), ' ', '-')) AS url_slug FROM PodcastShow ORDER BY show_id

← Spotify: Artist Merch Revenue From Shipped Orders · Spotify: Discover Weekly Save Rate per Listener →