SQL Quest › SQL Interview Questions › Window Functions

Reviewed Twice, Scored Higher the Second Time

MediumFreeQuerying BasicsWindow FunctionsSubqueries & CTEsJoins

The product team wants to know who changed their mind upwards. In reviews, find every customer who reviewed the same product exactly twice, where the second review (by review_date) gave more stars than the first.

Show exactly these 2 columns: name (from customers) and product. Order by name, then product.

Two things decide the answer: a customer who reviewed a product three times does not count, even if their second score went up; and "second" means second by date, not by review_id — one customer's rows were entered out of order. An equal score is not higher.

Solve it in the browser editor →

Runs on SQLite in your browser, graded against the expected result, no signup. A wrong answer gets a diagnosis, not just "incorrect". Free accounts get 10 free challenge solves, and this can be one of them.

Schema

reviews

review_idcustomer_idproductstarsreview_date
11Laptop Pro32024-02-01
22Wireless Mouse42024-02-03
37Office Chair42024-02-28

customers

customer_idnameemailsignup_datemembershiptotal_orders
1John Smithjohn.smith@email.com2023-01-15Gold15
2Emma Wilsonemma.wilson@email.com2023-03-20Silver8
3Michael Brownmichael.brown@email.com2023-02-10Gold12

Expected output: 3 rows — one Office Chair, two Laptop Pro

Hint

Number each customer's reviews of one product by date with ROW_NUMBER, and count them with COUNT(*) as a window over the same partition. Then put review 1 and review 2 of the same customer and product side by side (a self-join, or LAG on the second row), keep the pairs whose partition count is exactly 2, and compare the stars.

Concepts

SELECT Window Functions ROW_NUMBER PARTITION BY CTE Self-Join JOIN

Practise the topic: SQL practice questions · Window function practice · CTE practice · JOIN practice · Ranking function practice

Read the concept: SQL running total

In these company practice sets

Amazon

A SQL Quest challenge matched to patterns reported for these companies — not a question any of them has published.

Related questions

Busiest Merchants, and What a Tie Does to the RankMedium · FreeEach Merchant's Share of Its CategoryMedium · FreeRunning Total of Daily Card SpendMedium · FreeSalary Percentile RankingHard · ProFirst and Last Order per CustomerHard · ProCumulative Revenue Share (Pareto)Hard · Pro

Where would this cost you points in an interview?

Ten questions, no signup: a Skillmap across nine SQL skills and the one to fix first.

Take the readiness test