declare @periodstart nvarchar(20) = '2020-01-24 00:00:00';
declare @periodend nvarchar (20) = '2020-01-26 23:59:59';


DECLARE @ClientVer nvarchar (20) = '0.0.105';
Declare @Hours  int = 24;

DECLARE @WorldID  int = 1 ; -- 1, 2, 3, 4


;With Fails AS (
    select
	roomnumber AS RM,
     count(f.userid) AS 'Users'

    from Users  join finishrun as f on users.userid=f.userid
	where worldid = @WorldID
    and timestamp between f.created and dateadd(hh,@Hours,f.created)
	and users.created between @periodstart and @periodend
	and IsLeft = 0 and   roomnumber!=0 and roomname!='-2'
	and registrationclientversion=@ClientVer
	and f.retention1d=1


	group by roomnumber
)


select roomnumber
 ,count(distinct(userid))  as 'Users'
, count(userid) as 'Attempts'
, min(Fails.[Users]) AS 'Fails'
,avg(healthpoints) as AvgHP
,avg(healing) as Heal

from runroominfo AS rri LEFT JOIN fails ON Fails.RM = rri.roomnumber
where worldid = @WorldID
    and timestamp between created and dateadd(hh,@Hours,created)
 and created between @periodstart and @periodend
	and roomnumber!=0 and healthpoints<9999


	group by roomnumber
   order by roomnumber