How to Have different Groupings for avg Score

mhouk
mhouk Member
in Magic ETL

In this instance, I am getting Google Reviews 0-5 stars and averaging them overall time. But this is the all-time average ending in that quarter.

I know I can group by quarter and year but that would only give me the reviews in that time frame.

I want to have it calculate the all-time score up until that quarter. For instance, if I'm looking at a second-quarter score it should include all the reviews until the final date in the quarter 6-30-2024.

I can join in the date of the last or first day of the quarter no problem but I'm having a hard time grouping these or using SQL to partition it.

SELECT Qr-Year 2,AVG(STAR RATING) OVER(PARTITION BY Qr-Year 2) AS Avg Rating per Quarter
FROM Detailed Google Reviews

The problem with the above SQL is that somehow over is not recognized as a command.

Answers

  • mhouk
    mhouk Member

    so i found my own solution to make a moving average.

Categories