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
| Column | Type |
|---|---|
| member_id | int |
| full_name | varchar |
| vanity_slug | varchar |
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_id | full_name | vanity_slug |
|---|---|---|
| 1 | Priya Sharma | priya-sharma-42 |
| 2 | Rahul Verma | rahul_verma |
| 3 | Neha Gupta | -nehagupta |
| 4 | Dev Patel | dev |
| 5 | Simran Singh | simran--singh |
| 6 | Arjun Rao | NULL |
| 7 | Kavya Iyer | kavya.iyer |
| 8 | Ishaan Das | ishaan-das- |
| 9 | Tanvi Joshi | tanvi9 |
Output
| member_id | vanity_slug |
|---|---|
| 2 | rahul_verma |
| 3 | -nehagupta |
| 4 | dev |
| 5 | simran--singh |
| 7 | kavya.iyer |
| 8 | ishaan-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_idAnother 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_idAnother 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 →