Given the reviews table, write a query to get the
average stars for each product every month. The output should include the month
in numerical value, product id, and average star rating rounded to two decimal
places. Sort the output based on month followed by the product id.
table
name: reviews
Solution:
select month(submit_date) as mth,
product_id, format(avg(convert(decimal,stars)),'N2') as avg_stars
from reviews group by product_id,month(submit_date)
order by mth,product_id
Output:
SQL Script:
CREATE TABLE [dbo].[reviews](
[review_id]
[int] NOT NULL,
[user_id]
[int] NULL,
[submit_date]
[datetime] NULL,
[product_id]
[int] NULL,
[stars]
[int] NULL,
CONSTRAINT [PK_reviews] PRIMARY KEY CLUSTERED
(
[review_id]
ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[reviews] ([review_id], [user_id], [submit_date], [product_id], [stars]) VALUES (4517, 981, CAST(N'2022-07-05T00:00:00.000' AS DateTime), 69852, 2)
GO
INSERT [dbo].[reviews] ([review_id], [user_id], [submit_date], [product_id], [stars]) VALUES (5293, 362, CAST(N'2022-06-18T00:00:00.000' AS DateTime), 50001, 3)
GO
INSERT [dbo].[reviews] ([review_id], [user_id], [submit_date], [product_id], [stars]) VALUES (6171, 123, CAST(N'2022-06-08T00:00:00.000' AS DateTime), 50001, 4)
GO
INSERT [dbo].[reviews] ([review_id], [user_id], [submit_date], [product_id], [stars]) VALUES (6352, 192, CAST(N'2022-07-26T00:00:00.000' AS DateTime), 69852, 3)
GO
INSERT [dbo].[reviews] ([review_id], [user_id], [submit_date], [product_id], [stars]) VALUES (7802, 265, CAST(N'2022-06-10T00:00:00.000' AS DateTime), 69852, 4)
GO