Masked Reviewer Names for Top Reviews — SQL Strings Problem
Product pages show reviews with the reviewer's name masked for privacy: the first letter, then one * for each letter in between, then the last letter —…
- Difficulty: Easy
- Topics: Strings, Basics
- Dialect: MySQL
- Problem: #105
Problem statement
Product pages show reviews with the reviewer's name masked for privacy: the first letter, then one * for each letter in between, then the last letter — Priya becomes P***a, Dev becomes D*v.
Return the reviews with a rating of 4 or more, with the columns review_id, product_id and display_name (the masked name), ordered by review_id. Every reviewer_name is a single word of at least two letters.
Tables
Table: product_reviews
| Column | Type |
|---|---|
| review_id | int |
| product_id | int |
| reviewer_name | varchar |
| rating | int |
| reviewed_on | date |
Primary key: review_id.
One row per review; rating is 1–5 stars.
Examples
Example 1
product_reviews
| review_id | product_id | reviewer_name | rating | reviewed_on |
|---|---|---|---|---|
| 1 | 501 | Priya | 5 | 2024-08-11 |
| 2 | 501 | Dev | 4 | 2024-08-12 |
| 3 | 502 | Farhan | 2 | 2024-08-12 |
| 4 | 503 | Ananya | 4 | 2024-08-15 |
| 5 | 503 | Al | 5 | 2024-08-16 |
| 6 | 504 | Kabir | 3 | 2024-08-20 |
Output
| review_id | product_id | display_name |
|---|---|---|
| 1 | 501 | P***a |
| 2 | 501 | D*v |
| 4 | 503 | A****a |
| 5 | 503 | Al |
How to solve Masked Reviewer Names for Top Reviews
The masked name has three pieces glued with CONCAT: the first character (LEFT(name, 1)), a run of stars, and the last character (RIGHT(name, 1)). The run's length is CHAR_LENGTH(name) - 2 — every letter except the two kept — and REPEAT('*', n) produces it.
The two-letter name is the edge case: Al needs zero stars, and REPEAT('*', 0) is the empty string, so it comes out as Al unchanged. That is why the statement guarantees at least two letters; a one-letter name would ask for REPEAT with -1, which also yields an empty string but would then print the letter twice.
The alternative builds the same pieces with SUBSTRING and pads an empty string to the right length with RPAD. CHAR_LENGTH counts characters while LENGTH counts bytes; for these ASCII names they agree, but for names in Devanagari only CHAR_LENGTH is right. The filter rating >= 4 and the sort are applied to the plain columns.
Reference solution (MySQL)
SELECT review_id, product_id,
CONCAT(LEFT(reviewer_name, 1), REPEAT('*', CHAR_LENGTH(reviewer_name) - 2), RIGHT(reviewer_name, 1)) AS display_name
FROM product_reviews
WHERE rating >= 4
ORDER BY review_idAnother way
SELECT review_id, product_id, CONCAT(SUBSTRING(reviewer_name, 1, 1), RPAD('', LENGTH(reviewer_name) - 2, '*'), SUBSTRING(reviewer_name, LENGTH(reviewer_name), 1)) AS display_name FROM product_reviews WHERE rating > 3 ORDER BY review_id← Cart Shipping Fee and Amount Payable · Cart Abandonment Rate by Device →