SQLAggregateUsingTrigger.sql

if object_id(N'RatingAggregate', N'U') is not null
	drop table RatingAggregate
go
if object_id(N'Ratings', N'U') is not null
	drop table Ratings
go
if object_id(N'Movies', N'U') is not null
	drop table Movies
go
create table Movies(
	Id int not null identity(1,1) primary key,
	Name varchar(max) not null
)
go

create table Ratings(
	Id int not null identity(1,1) primary key,
	Value int not null,
	MovieId int not null foreign key references Movies
)
go

create table RatingAggregate(
	MovieId int not null primary key foreign key references Movies,
	Value float not null default(0.0),
	RatingsCount int not null default(0) 
)
go

insert into Movies(Name) values
			('Deadpool'),
			('Lord of the rings'),
			('Sharknado'),
			('Hobbit'),
			('Hobbit 5')
go

if object_id(N'RatingAggregateUpdate') is not null
	drop trigger RatingAggregateUpdate
go

create trigger RatingAggregateUpdate
			on Ratings after insert
as
begin
set transaction isolation level serializable
--begin transaction

	-- initialize those which are not present yet
	insert into RatingAggregate(MovieId, Value, RatingsCount)
		select distinct i.MovieId as MovieId, 
						0.0 as Value, 
						0 as RatingsCount 
						from inserted i left join RatingAggregate ra
						on i.MovieId = ra.MovieId
						where ra.MovieId IS NULL

	-- update values
	update RatingAggregate 
		set Value = ((ra.Value*ra.RatingsCount)+ratingsForMovies.RatingsSum)/(ra.RatingsCount+ratingsForMovies.RatingsCount),
			RatingsCount = ra.RatingsCount + ratingsForMovies.RatingsCount
		from RatingAggregate ra inner join 
			(select i.MovieId, SUM(i.Value) as RatingsSum, COUNT(*) as RatingsCount
				from inserted i
				group by i.MovieId) as ratingsForMovies
		on ra.MovieId = ratingsForMovies.MovieId	
--commit transaction
end
go

insert into Ratings(Value, MovieId) values
			(5, 1),
			(5, 1),
			(1, 2),
			(1, 2),
			(3, 3),
			(3, 3),
			(4, 4),
			(4, 4)
go			

insert into Ratings(Value, MovieId) values
			(7, 1),
			(7, 1),
			(3, 2),
			(3, 2),
			(5, 3),
			(5, 3),
			(6, 4)
go			


select movies.Id, movies.Name, COALESCE(ra.Value, 0) as Rating 
	from Movies movies left outer join RatingAggregate ra 
	on movies.Id = ra.MovieId

select movies.Id, COALESCE(AVG(CAST(r.Value as float)), 0.0) as Rating
	from Movies movies
		left outer join Ratings r on movies.Id = r.MovieId
				group by movies.Id

--movies with names and ratings: cross apply 
select m.Id, m.Name, COALESCE(innerMovie.Rating, 0.0) from Movies m	
	outer apply (
		select AVG(CAST(r.Value as float)) as Rating
			from Ratings r
			where m.Id = r.MovieId
			group by r.MovieId
	) innerMovie

--movies with names and ratings: left outer join with subquery
select m.Id, m.Name, COALESCE(aggregatedRatings.Rating, 0.0)
	from Movies m	
	left outer join (
		select r.MovieId as MovieId, AVG(CAST(r.Value as float)) as Rating
			from Ratings r
			group by r.MovieId
	) aggregatedRatings on aggregatedRatings.MovieId = m.Id


--movies with names and ratings: CTE + outer apply
;with AggregatedRatings as
( select r.MovieId as MovieId, COALESCE(AVG(CAST(r.Value as float)), 0.0) as Rating
			from Ratings r
			group by r.MovieId )
select MovieId, Name, Rating
	from AggregatedRatings ar
	outer apply
		( select Name from Movies where Id = ar.MovieId ) a


--movies with names and ratings: CTE + left outer join	
;with AggregatedRatings as
( select r.MovieId as MovieId, AVG(CAST(r.Value as float)) as Rating
			from Ratings r
			group by r.MovieId )
select MovieId, Name, COALESCE(Rating, 0.0) as Rating
	from Movies movies
	left outer join AggregatedRatings ar on
		movies.Id = ar.MovieId