Post-Update Rating DropsCompany: Google | Difficulty: medium

The Chrome Web Store team wants to flag extensions whose user ratings got noticeably worse right after their most recent version update.

For each extension in extension_catalog, look at its reviews in extension_reviews and compute two averages:

avg_rating_before: the average rating from reviews posted in the 14 days immediately preceding update_date (i.e. update_date minus 14 days through update_date minus 1 day, inclusive).
avg_rating_after: the average rating from reviews posted starting on update_date and for the following 13 days (i.e. update_date through update_date plus 13 days, inclusive — 14 days total, counting update_date as day 1).

Only include an extension if it has at least 2 reviews in each window. Only output extensions where avg_rating_after is at least 0.5 lower than avg_rating_before. Round both averages to exactly 2 decimal places.

Output extension_name, avg_rating_before, and avg_rating_after. Order the results by the rating drop (avg_rating_before minus avg_rating_after, computed before rounding) descending, and break ties by extension_name ascending.

Table Schema

Table	Column	Type
`extension_catalog`	`extension_id`	integer
`extension_catalog`	`extension_name`	string
`extension_catalog`	`developer`	string
`extension_catalog`	`update_date`	date
`extension_reviews`	`review_id`	integer
`extension_reviews`	`extension_id`	integer
`extension_reviews`	`rating`	integer
`extension_reviews`	`review_date`	date

Example Input: `extension_catalog`

extension_id	extension_name	developer	update_date
1	TabMaster Pro	Nimbus Software Ltd	2024-03-15
2	Focus Timer	BrightPath Apps	2024-03-10

Example Input: `extension_reviews`

review_id	extension_id	rating	review_date
1	1	5	2024-03-03
2	1	4	2024-03-08
3	1	2	2024-03-16
4	1	1	2024-03-20
5	2	5	2024-03-05
6	2	3	2024-03-12
7	2	2	2024-03-15

Example Output Explanation

For TabMaster Pro (update_date 2024-03-15), the before-window is 2024-03-01 to 2024-03-14, giving ratings 5 and 4 (avg_rating_before = 4.50). The after-window is 2024-03-15 to 2024-03-28, giving ratings 2 and 1 (avg_rating_after = 1.50). The drop of 3.00 is at least 0.5, so it's included. Focus Timer only has 1 review in its before-window (rating 5 on 2024-03-05), so it fails the minimum-2-reviews requirement and is excluded entirely.

SchemaDROP TABLE IF EXISTS extension_reviews; DROP TABLE IF EXISTS extension_catalog; CREATE TABLE extension_catalog (extension_id INT, extension_name VARCHAR(255), developer VARCHAR(255), update_date DATE); CREATE TABLE extension_reviews (review_id INT, extension_id INT, rating INT, review_date DATE); INSERT INTO extension_catalog VALUES (1, 'TabMaster Pro', 'Nimbus Software Ltd', '2024-03-15'), (2, 'Focus Timer', 'BrightPath Apps', '2024-03-10'); INSERT INTO extension_reviews VALUES (1, 1, 5, '2024-03-03'), (2, 1, 4, '2024-03-08'), (3, 1, 2, '2024-03-16'), (4, 1, 1, '2024-03-20'), (5, 2, 5, '2024-03-05'), (6, 2, 3, '2024-03-12'), (7, 2, 2, '2024-03-15');← All SQL Challenges · Google Questions · Learn SQL Free