Google: Local Guides Whose Ratings Turned Harsher

Medium
683.

Google: Local Guides Whose Ratings Turned Harsher

MediumWindow Functions

MySQL

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

ColumnType
review_idint
guide_namevarchar
place_idint
ratingint
reviewed_ondate

Primary key: review_id.

One review by one guide; guide_name identifies the guide. rating is 1–5 stars.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

LocalGuideReview

review_idguide_nameplace_idratingreviewed_on
1Aarav152025-01-05
2Aarav242025-02-10
3Aarav332025-03-01
4Diya122025-01-07
5Diya452025-03-03
8Kabir242025-02-02
6Kabir552025-02-02
9Neha312025-01-01
10Meera442025-01-10
11Meera542025-04-10

Output

guide_namefirst_ratinglatest_ratingreviews
Aarav533
Kabir542
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.

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

ColumnType
review_idint
guide_namevarchar
place_idint
ratingint
reviewed_ondate

Primary key: review_id.

One review by one guide; guide_name identifies the guide. rating is 1–5 stars.

Examples

Example 1

LocalGuideReview

review_idguide_nameplace_idratingreviewed_on
1Aarav152025-01-05
2Aarav242025-02-10
3Aarav332025-03-01
4Diya122025-01-07
5Diya452025-03-03
8Kabir242025-02-02
6Kabir552025-02-02
9Neha312025-01-01
10Meera442025-01-10
11Meera542025-04-10

Output

guide_namefirst_ratinglatest_ratingreviews
Aarav533
Kabir542

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.rating

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

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