Rating Report for Each Canteen Counter — SQL Easy Problem

Problem statement

Students rate their meal from 1 to 5 stars on a tablet at the canteen's exit, choosing the counter that served them.

For every counter with at least one rating, return the columns counter, avg_stars (the average of its stars) and poor_pct (the percentage of its ratings that are 2 stars or fewer, as a number from 0 to 100). Round both to 2 decimal places. Return the rows in any order.

Tables

Table: MealRating

Primary key: rating_id.

One row per rating; stars is from 1 to 5.

Examples

Example 1

MealRating

Output

How to solve Rating Report for Each Canteen Counter

One row per counter means GROUP BY counter, and both figures are aggregates over the group.

The average is AVG(stars). The percentage needs a count of only some rows of the group, which is what conditional aggregation gives: SUM(CASE WHEN stars <= 2 THEN 1 ELSE 0 END) adds 1 for each poor rating and 0 for the rest. Dividing by COUNT(*), the number of ratings, and multiplying by 100 turns it into a percentage. The boundary is inclusive — a 2-star rating is poor, a 3-star one is not.

Two shortcuts give the same numbers. The average of a flag is a ratio, so AVG(IF(stars <= 2, 100, 0)) is the percentage directly; and in MySQL a comparison is itself 1 or 0, so AVG(stars <= 2) * 100 works as well. MySQL's / always divides as a decimal (1 / 3 is 0.3333), so the order of the factors does not matter here; in databases that divide integers as integers, multiplying by 100 before dividing is what keeps a percentage from collapsing to 0.

Round once, at the end — rounding the average or the ratio before multiplying would lose precision. A counter with no ratings has no rows, so it never appears, as the statement says. The query is a single pass over the table.

Reference solution (MySQL)

SELECT counter,
       ROUND(AVG(stars), 2) AS avg_stars,
       ROUND(100 * SUM(CASE WHEN stars <= 2 THEN 1 ELSE 0 END) / COUNT(*), 2) AS poor_pct
FROM MealRating
GROUP BY counter

Another way

SELECT counter, ROUND(SUM(stars) / COUNT(*), 2) AS avg_stars, ROUND(AVG(IF(stars <= 2, 100, 0)), 2) AS poor_pct FROM MealRating GROUP BY counter

Another way

SELECT counter, ROUND(AVG(stars), 2) AS avg_stars, ROUND(AVG(stars <= 2) * 100, 2) AS poor_pct FROM MealRating GROUP BY counter