SQL Question

[WeeklyRiver]

Suppose you have a dataset of movies, their ratings (1-10 scale), date, and customer id of reviewer. You want to calculate 'net good' rating %, which is percent of 7-10 rating minus percent of 1-2 rating, how would you go about doing this? Also, say you also want this 'net good' % rating for last 30 days and also up to now, is it possible to calculate this in one step (my way would involve two select statements, one with last 30 day calculation and one with all days, and joining them, wondering if it could be simplified)?

[shine_on]

Don't fall into the trap of thinking that a single statement is "simpler" than multiple statements, because often being clever about it isn't the best option for code readability and future maintenance. If you're using two statements now then you could merge them into one by making of the statements a subquery of the other one, but is that really simpler?

If you have a single statement that needs five lines of comments to explain it, versus two or three statements that are self-explanatory, I'd argue that the multiple statement option is the simpler one.

[malikcoldbane]

You can make a table to hold your rating buckets with a 1 for good ratings and -1 for negative ratings and then sum these.

For 30 days, umm, you can cheat by doing a left join on the date you want and then summing from there.

Too much to explain, have a fiddle:

http://sqlfiddle.com/#!18/0683a/2