Google: Local Guides Whose Ratings Turned Harsher
The Maps Local Guides team studies reviewers whose standards drift, comparing each guide's very first star rating with their most recent one.
- Difficulty: Medium
- Topics: Window Functions
- Dialect: MySQL
- Problem: #683
Problem statement
The Maps Local Guides team studies reviewers whose standards drift, comparing each guide's very first star rating with their most recent one.
For every guide with at least 2 reviews, find the first review (earliest reviewed_on; on the same day the lower review_id) and the latest review (latest reviewed_on; on the same day the higher review_id). Return guide_name, first_rating, latest_rating and reviews (their review count) for the guides whose latest rating is strictly lower than their first. Return the rows in any order.
A practice scenario set at Google: the tables and data are invented, and this is not a question from, or endorsed by, Google.
Tables
Table: LocalGuideReview
| Column | Type |
|---|---|
| review_id | int |
| guide_name | varchar |
| place_id | int |
| rating | int |
| reviewed_on | date |
Primary key: review_id.
One review by one guide; guide_name identifies the guide. rating is 1–5 stars.
Examples
Example 1
LocalGuideReview
| review_id | guide_name | place_id | rating | reviewed_on |
|---|---|---|---|---|
| 1 | Aarav | 1 | 5 | 2025-01-05 |
| 2 | Aarav | 2 | 4 | 2025-02-10 |
| 3 | Aarav | 3 | 3 | 2025-03-01 |
| 4 | Diya | 1 | 2 | 2025-01-07 |
| 5 | Diya | 4 | 5 | 2025-03-03 |
| 8 | Kabir | 2 | 4 | 2025-02-02 |
| 6 | Kabir | 5 | 5 | 2025-02-02 |
| 9 | Neha | 3 | 1 | 2025-01-01 |
| 10 | Meera | 4 | 4 | 2025-01-10 |
| 11 | Meera | 5 | 4 | 2025-04-10 |
Output
| guide_name | first_rating | latest_rating | reviews |
|---|---|---|---|
| Aarav | 5 | 3 | 3 |
| Kabir | 5 | 4 | 2 |
How to solve Google: Local Guides Whose Ratings Turned Harsher
The comparison is between two particular rows of each guide, so the reviews are numbered in both directions: ROW_NUMBER() by reviewed_on, review_id finds the first review as number 1, and by reviewed_on DESC, review_id DESC finds the latest as number 1. The statement's tiebreaks — lower id first, higher id last — are exactly those two orderings, so a guide with two reviews on the same day still has a single first and a single latest.
A COUNT(*) OVER (PARTITION BY guide_name) puts the guide's review count on every row without grouping. Joining the first-row copy to the latest-row copy gives one row per guide, filtered to at least two reviews and to a strictly lower latest rating. A guide with one review would compare a review with itself, which is why the count condition is needed; equal ratings are not harsher.
FIRST_VALUE/LAST_VALUE with a full frame read the same two ratings (the frame must be widened, or LAST_VALUE stops at the current row). A window-free version picks each row with NOT EXISTS. The window version sorts each guide's reviews twice.
Reference solution (MySQL)
WITH r AS (
SELECT guide_name, rating,
COUNT(*) OVER (PARTITION BY guide_name) AS n,
ROW_NUMBER() OVER (PARTITION BY guide_name ORDER BY reviewed_on, review_id) AS from_first,
ROW_NUMBER() OVER (PARTITION BY guide_name ORDER BY reviewed_on DESC, review_id DESC) AS from_last
FROM LocalGuideReview
)
SELECT f.guide_name, f.rating AS first_rating, l.rating AS latest_rating, f.n AS reviews
FROM r f
JOIN r l ON l.guide_name = f.guide_name AND l.from_last = 1
WHERE f.from_first = 1
AND f.n >= 2
AND l.rating < f.ratingAnother way
SELECT guide_name, first_rating, latest_rating, reviews FROM (SELECT DISTINCT guide_name, FIRST_VALUE(rating) OVER (PARTITION BY guide_name ORDER BY reviewed_on, review_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_rating, LAST_VALUE(rating) OVER (PARTITION BY guide_name ORDER BY reviewed_on, review_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS latest_rating, COUNT(*) OVER (PARTITION BY guide_name) AS reviews FROM LocalGuideReview) x WHERE reviews >= 2 AND latest_rating < first_ratingAnother way
SELECT g.guide_name, f.rating AS first_rating, l.rating AS latest_rating, g.reviews FROM (SELECT guide_name, COUNT(*) AS reviews FROM LocalGuideReview GROUP BY guide_name HAVING COUNT(*) >= 2) g JOIN LocalGuideReview f ON f.guide_name = g.guide_name AND NOT EXISTS (SELECT 1 FROM LocalGuideReview e WHERE e.guide_name = f.guide_name AND (e.reviewed_on < f.reviewed_on OR (e.reviewed_on = f.reviewed_on AND e.review_id < f.review_id))) JOIN LocalGuideReview l ON l.guide_name = g.guide_name AND NOT EXISTS (SELECT 1 FROM LocalGuideReview e WHERE e.guide_name = l.guide_name AND (e.reviewed_on > l.reviewed_on OR (e.reviewed_on = l.reviewed_on AND e.review_id > l.review_id))) WHERE l.rating < f.rating← Google: Drive Files Shared Outside the Owner's Domain · Google: Places per Top-Level Maps Category →