LinkedIn: Custom Profile URLs That Break the Rules

Medium
735.

LinkedIn: Custom Profile URLs That Break the Rules

MediumStrings

MySQL

Members can choose a custom public profile address, and the trust team is cleaning up ones saved before the rules were enforced.

A valid vanity_slug is 5 to 30 characters long, uses only letters, digits and hyphens, does not start or end with a hyphen, and never has two hyphens in a row. Return member_id and vanity_slug for every member whose slug breaks at least one rule. Members without a custom address (vanity_slug NULL) are not checked. Order the rows by member_id.

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

Tables

Table: Member

ColumnType
member_idint
full_namevarchar
vanity_slugvarchar

Primary key: member_id.

vanity_slug is the part after /in/ in the member's public address, or NULL if they never set one. Slugs are stored in lower case.

Example

The rows must come back in the order shown.

Member

member_idfull_namevanity_slug
1Priya Sharmapriya-sharma-42
2Rahul Vermarahul_verma
3Neha Gupta-nehagupta
4Dev Pateldev
5Simran Singhsimran--singh
6Arjun RaoNULL
7Kavya Iyerkavya.iyer
8Ishaan Dasishaan-das-
9Tanvi Joshitanvi9

Output

member_idvanity_slug
2rahul_verma
3-nehagupta
4dev
5simran--singh
7kavya.iyer
8ishaan-das-
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.

LinkedIn: Custom Profile URLs That Break the Rules

Members can choose a custom public profile address, and the trust team is cleaning up ones saved before the rules were enforced.

  • Difficulty: Medium
  • Topics: Strings
  • Dialect: MySQL
  • Problem: #735

Problem statement

Members can choose a custom public profile address, and the trust team is cleaning up ones saved before the rules were enforced.

A valid vanity_slug is 5 to 30 characters long, uses only letters, digits and hyphens, does not start or end with a hyphen, and never has two hyphens in a row. Return member_id and vanity_slug for every member whose slug breaks at least one rule. Members without a custom address (vanity_slug NULL) are not checked. Order the rows by member_id.

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

Tables

Table: Member

ColumnType
member_idint
full_namevarchar
vanity_slugvarchar

Primary key: member_id.

vanity_slug is the part after /in/ in the member's public address, or NULL if they never set one. Slugs are stored in lower case.

Examples

Example 1

Member

member_idfull_namevanity_slug
1Priya Sharmapriya-sharma-42
2Rahul Vermarahul_verma
3Neha Gupta-nehagupta
4Dev Pateldev
5Simran Singhsimran--singh
6Arjun RaoNULL
7Kavya Iyerkavya.iyer
8Ishaan Dasishaan-das-
9Tanvi Joshitanvi9

Output

member_idvanity_slug
2rahul_verma
3-nehagupta
4dev
5simran--singh
7kavya.iyer
8ishaan-das-

How to solve LinkedIn: Custom Profile URLs That Break the Rules

A slug is invalid if any rule fails, so the query lists one condition per rule and joins them with OR: the length is outside 5–30, some character is outside the allowed set (REGEXP '[^a-z0-9-]' finds an underscore, a dot or a space), the first or last character is a hyphen, or -- occurs somewhere. The NULL check stands apart and is joined with AND, so members without a custom address are never flagged.

The first alternative states the valid shape in one regular expression — ^[a-z0-9]+(-[a-z0-9]+)*$ means one or more runs of letters and digits separated by single hyphens — which covers the character set, the leading and trailing hyphen and the double hyphen at once; only the length stays a separate test. The second writes the positive definition of a valid slug and negates it, using LEFT, RIGHT and INSTR instead of LIKE and LOCATE. The slugs are ASCII, so LENGTH and CHAR_LENGTH agree. Every version is one scan with no join.

Reference solution (MySQL)

SELECT member_id, vanity_slug
FROM Member
WHERE vanity_slug IS NOT NULL
  AND (CHAR_LENGTH(vanity_slug) NOT BETWEEN 5 AND 30
       OR vanity_slug REGEXP '[^a-z0-9-]'
       OR vanity_slug LIKE '-%'
       OR vanity_slug LIKE '%-'
       OR LOCATE('--', vanity_slug) > 0)
ORDER BY member_id

Another way

SELECT member_id, vanity_slug FROM Member WHERE vanity_slug IS NOT NULL AND (CHAR_LENGTH(vanity_slug) < 5 OR CHAR_LENGTH(vanity_slug) > 30 OR vanity_slug NOT REGEXP '^[a-z0-9]+(-[a-z0-9]+)*$') ORDER BY member_id

Another way

SELECT member_id, vanity_slug FROM Member WHERE vanity_slug IS NOT NULL AND NOT (LENGTH(vanity_slug) BETWEEN 5 AND 30 AND vanity_slug NOT REGEXP '[^a-z0-9-]' AND LEFT(vanity_slug, 1) <> '-' AND RIGHT(vanity_slug, 1) <> '-' AND INSTR(vanity_slug, '--') = 0) ORDER BY member_id

← LinkedIn: Most Common Reaction on Each Post · LinkedIn: Jobs Reposted Within 30 Days →