Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
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
sql6
FirstTable
FIGURA5.2
exercise 1 – fines – database import 1.0.1.sql
STDEV vs STDEVP
SCHOOL-BUILD
Merge Sample Code
Use the right tool to get identity values back after an insert
check constraint
STACK