Masked Reviewer Names for Top Reviews

Easy
105.

Masked Reviewer Names for Top Reviews

EasyStringsBasics

MySQL

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

ColumnType
review_idint
product_idint
reviewer_namevarchar
ratingint
reviewed_ondate

Primary key: review_id.

One row per review; rating is 1–5 stars.

Example

The rows must come back in the order shown.

product_reviews

review_idproduct_idreviewer_nameratingreviewed_on
1501Priya52024-08-11
2501Dev42024-08-12
3502Farhan22024-08-12
4503Ananya42024-08-15
5503Al52024-08-16
6504Kabir32024-08-20

Output

review_idproduct_iddisplay_name
1501P***a
2501D*v
4503A****a
5503Al
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.

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

ColumnType
review_idint
product_idint
reviewer_namevarchar
ratingint
reviewed_ondate

Primary key: review_id.

One row per review; rating is 1–5 stars.

Examples

Example 1

product_reviews

review_idproduct_idreviewer_nameratingreviewed_on
1501Priya52024-08-11
2501Dev42024-08-12
3502Farhan22024-08-12
4503Ananya42024-08-15
5503Al52024-08-16
6504Kabir32024-08-20

Output

review_idproduct_iddisplay_name
1501P***a
2501D*v
4503A****a
5503Al

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_id

Another 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 →