My tables:
TblFruits
| id | name |
|---|---|
| 1 | apple |
TblReview
| reviewID | verdict | fruitID | userID | datePosted |
|---|---|---|---|---|
| 2 | Delicious | 1 | 1 | 2024-12-09 00:00:00 |
| 3 | Awful | 1 | 1 | 2024-12-07 00:00:00 |
| 5 | Delicious | 1 | 2 | 2024-12-09 00:00:00 |
| 6 | Delicious | 1 | 3 | 2024-12-09 00:00:00 |
| 7 | Awful | 1 | 4 | 2024-12-09 00:00:00 |
My final query needs to look like this:
| fruit name | Verdict | % |
|---|---|---|
| apple | Delicious | 75 |
I need to return a single row containing the majority verdict of the reviews and the percent of reviews that lead to that verdict.
Catch
Users can post multiple reviews for the same fruit, in which case only the latest review will be counted
Unfortunately I only got as far as joining the tables
<code>select name as 'fruit name', tbReview.verdict, tbReview.userID as 'reviewed by', tbReview.datePosted
from tbFruits
left join tbReview on tbFruits.fruitId = tbReview.fruitID
group by tbReview.userID, tbReview.datePosted, tbFruits.name, tbReview.verdict
</code>
<code>select name as 'fruit name', tbReview.verdict, tbReview.userID as 'reviewed by', tbReview.datePosted
from tbFruits
left join tbReview on tbFruits.fruitId = tbReview.fruitID
group by tbReview.userID, tbReview.datePosted, tbFruits.name, tbReview.verdict
</code>
select name as 'fruit name', tbReview.verdict, tbReview.userID as 'reviewed by', tbReview.datePosted
from tbFruits
left join tbReview on tbFruits.fruitId = tbReview.fruitID
group by tbReview.userID, tbReview.datePosted, tbFruits.name, tbReview.verdict
New contributor
Wohn Jick is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
4
We can accomplish that as follows:
- Use a CTE to filter out reviews which shouldn’t be counted
- Group the counts
- Work out the percentages and only show the top 1.
<code>with cte1 as (
-- Determine which reviews are valid
select *
, row_number() over (partition by UserId, FruitId order by DatePosted desc) rn
from Review
), cte2 as (
-- Filter out invalid reviews and count the remaining reviews
select count(*) Votes, FruitId, Verdict
from cte1
where rn = 1
group by FruitId, Verdict
)
-- Display the top result and percentage
select top 1 f.Name [Fruit Name], Verdict
, convert(decimal(9, 2), 100.00 * Votes / sum(Votes) over ()) [%]
from cte2 r
join Fruit f on f.FruitId = r.FruitId
order by [%] desc;
</code>
<code>with cte1 as (
-- Determine which reviews are valid
select *
, row_number() over (partition by UserId, FruitId order by DatePosted desc) rn
from Review
), cte2 as (
-- Filter out invalid reviews and count the remaining reviews
select count(*) Votes, FruitId, Verdict
from cte1
where rn = 1
group by FruitId, Verdict
)
-- Display the top result and percentage
select top 1 f.Name [Fruit Name], Verdict
, convert(decimal(9, 2), 100.00 * Votes / sum(Votes) over ()) [%]
from cte2 r
join Fruit f on f.FruitId = r.FruitId
order by [%] desc;
</code>
with cte1 as (
-- Determine which reviews are valid
select *
, row_number() over (partition by UserId, FruitId order by DatePosted desc) rn
from Review
), cte2 as (
-- Filter out invalid reviews and count the remaining reviews
select count(*) Votes, FruitId, Verdict
from cte1
where rn = 1
group by FruitId, Verdict
)
-- Display the top result and percentage
select top 1 f.Name [Fruit Name], Verdict
, convert(decimal(9, 2), 100.00 * Votes / sum(Votes) over ()) [%]
from cte2 r
join Fruit f on f.FruitId = r.FruitId
order by [%] desc;
Returns
| Fruit Name | Verdict | % |
|---|---|---|
| Apple | Delicious | 75.00 |
db<>fiddle
Note: This only works for a single fruit as per your example. If you wanted to handle multiple fruit you would need to clarify your desired results.
0
Use row_number() & over() to determine “the most recent” response per user per fruit, then calculate percentages, and finally output the TOP (1) WITH TIES which will cater for 50/50 results to show both verdicts:
<code>WITH LatestReviews
AS (
SELECT
fruitID
, userID
, verdict
, ROW_NUMBER() OVER (
PARTITION BY fruitID
, userID ORDER BY datePosted DESC
) AS rn
FROM TblReview
)
, VerdictCounts AS (
SELECT
fruitID
, verdict
, COUNT(*) AS verdict_count
, COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY fruitID) AS verdict_percentage
FROM LatestReviews
WHERE rn = 1
GROUP BY
fruitID
, verdict
)
SELECT TOP (1) WITH TIES
fruitID
, verdict AS majority_verdict
, verdict_percentage AS majority_percentage
FROM VerdictCounts
ORDER BY
fruitID
, verdict_count DESC
, verdict
</code>
<code>WITH LatestReviews
AS (
SELECT
fruitID
, userID
, verdict
, ROW_NUMBER() OVER (
PARTITION BY fruitID
, userID ORDER BY datePosted DESC
) AS rn
FROM TblReview
)
, VerdictCounts AS (
SELECT
fruitID
, verdict
, COUNT(*) AS verdict_count
, COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY fruitID) AS verdict_percentage
FROM LatestReviews
WHERE rn = 1
GROUP BY
fruitID
, verdict
)
SELECT TOP (1) WITH TIES
fruitID
, verdict AS majority_verdict
, verdict_percentage AS majority_percentage
FROM VerdictCounts
ORDER BY
fruitID
, verdict_count DESC
, verdict
</code>
WITH LatestReviews
AS (
SELECT
fruitID
, userID
, verdict
, ROW_NUMBER() OVER (
PARTITION BY fruitID
, userID ORDER BY datePosted DESC
) AS rn
FROM TblReview
)
, VerdictCounts AS (
SELECT
fruitID
, verdict
, COUNT(*) AS verdict_count
, COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY fruitID) AS verdict_percentage
FROM LatestReviews
WHERE rn = 1
GROUP BY
fruitID
, verdict
)
SELECT TOP (1) WITH TIES
fruitID
, verdict AS majority_verdict
, verdict_percentage AS majority_percentage
FROM VerdictCounts
ORDER BY
fruitID
, verdict_count DESC
, verdict
see: https://dbfiddle.uk/OALtLK81
1