Find number of Ratings requested and provided per day-CodePudding

I have the following columns

date  |  id  |  score  |

score has options like offered, good, and bad.

How to write a query that gives the number of ratings offered vs. the number of queries provided (score is either good or bad) per day.

CodePudding user response：

I'm not entire clear what output you want from this, but I think you are looking for something like the following?

SELECT t1.date, t1.all, t2.offered
FROM (SELECT date_trunc('day', date) "date", count(*) "all"
  FROM test
  GROUP BY 1) t1
JOIN (SELECT date_trunc('day', date) "date", count(*) "offered"
  FROM test
  WHERE score = 'offered'
  GROUP BY 1) t2
ON t1.date = t2.date;

This will group by the date, and output the total rows per date, and total rows with a "score" of 'offered' per date. Here's a fiddle of this running

CodePudding user response：

SELECT
   SUM(
       CASE 
           WHEN score IN ('good', 'bad') THEN 1
           ELSE 0
       END
   ) AS good_or_bad
   SUM(
       CASE
           WHEN score = 'offered' THEN 1
           ELSE 0
       END
   ) AS offered
FROM yourtable
GROUP BY date

In the above, I'm using case-when to determine the score. The first field is for good_or_bad, so I'm counting the elements where the score is either good or bad. The second is for offered, so I'm counting the records whose score is offered. Since you want to group by date, that's added as well

NOTE

date might be a varchar, a date or a timestamp. In my query above I assumed that date is of day precision. If it's like a timestamp, then you will need to modify the group by to extract the actual date.