Tucha Alexander DB QA Test Tasks Answers, Sql Server

Tucha Alexander DB QA Test Tasks Answers

--First
select ua.Date, 
       ua.UserId,
	   ROW_NUMBER() OVER(PARTITION BY userid ORDER BY date) as LoginIndex 
  from test.dbo.UserActivity ua 
	   order by ua.Date
       
--Second
select a.Date, a.Result from (select --ua1.UserId, 
       ua1.Date,
       --count(ua1.userid) OVER(PARTITION BY  ua1.Date) as ThisDay,
       --MIN(ua2.Date) as NextTime,
       --DATEDIFF( D, ua1.Date, MIN(ua2.Date) ) as DaysPassed,
       --case  when DATEDIFF( D, ua1.Date, MIN(ua2.Date) )>=7 then 1 else 0 end as fl_week,
       --sum (case  when DATEDIFF( D, ua1.Date, MIN(ua2.Date) )>=7 then 1 else 0 end) over (partition by ua1.Date) as fl_week_sum,
       sum (case  when DATEDIFF( D, ua1.Date, MIN(ua2.Date) )>=7 then 1 else 0 end) over (partition by ua1.Date) * 100 / count(ua1.userid) OVER(PARTITION BY  ua1.Date) as Result
  from test.dbo.UserActivity ua1 join test.dbo.UserActivity ua2 on ua1.UserID=ua2.UserID 
                                                               and ua2.Date > ua1.Date 
                                                               and ua2.Date= ( select MIN(ua2.Date) over (partition by ua2.UserId) )
  group by ua1.UserID, ua1.Date, ua2.UserID) a
  group by a.Date, a.Result
       
--Third
select a.DateReg, a.Procent from (select --ur.UserID,
       ur.Date as DateReg, 
       --COUNT(ur.UserID) over (partition by ur.Date) RegDay,
       --ua.UserID,
       --ua.DateEnter,
       --DATEDIFF( D, ur.Date, ua.DateEnter ) as Betw,
       --case when DATEDIFF( D, ur.Date, ua.DateEnter )=7 then 1 else 0 end as fl_week,
       --sum(case when DATEDIFF( D, ur.Date, ua.DateEnter )=7 then 1 else 0 end) over (partition by ur.date) as PlayWeek,
       (sum(case when DATEDIFF( D, ur.Date, ua.DateEnter )=7 then 1 else 0 end) over (partition by ur.date)) * 100 / COUNT(ur.UserID) over (partition by ur.Date) Procent
  from test.dbo.UserRegistrations ur full join (select UserID, MIN(Date) as DateEnter from test.dbo.UserActivity group by UserID) as ua on ur.UserID=ua.UserID 
) a group by a.DateReg, a.Procent


run \| edit \| history \| help	0

λ

                                      .NET NoSQL database for rapid development