ID Users

1

Please login or register to vote for this query.

(click on this box to dismiss)

Physics

Q&A for active researchers, academics and students of physics

;with userQ as
(
/*all users with their questions and answers*/
select 
    p.owneruserid as UserID,
    p.tags,
    p.posttypeid,
    p.id as PostID,
    row_number() over (partition by p.owneruserid, p.posttypeid order by p.creationdate desc) as rn
from postswithdeleted p
where p.posttypeid in (1, 2)
),
IDUsers as
(
/*users that first asked an id question*/
select 
  UserID,
  PostID
from userQ
where posttypeid = 1 
    and rn = 1
    and tags like '%identify-%'
),
IDReturn as
(
/*ID users that went on to ask or answer more questions*/
select distinct u.UserID
from userQ u
where u.UserID in (select UserID from IDUsers)
and u.PostID not in (select PostId from IDUsers)
),
QUsers as
(
/*users that first asked a question*/
select 
  UserID,
  PostID
from userQ
where posttypeid = 1 
    and rn = 1
),
QReturn as
(
/*Q users that went on to ask or answer more questions*/
select distinct u.UserID
from userQ u
where u.UserID in (select UserID from QUsers)
and u.PostID not in (select PostId from QUsers)
)








Select 
year(u.CreationDate) as Yr,
'New Users' as Category,
count(*) as Cnt
from Users u
group by year(u.CreationDate)

union all

Select 
year(u.CreationDate) as Yr,
'ID Users' as Category,
count(*) as Cnt
from Users u
  inner join IDUsers idu
      on u.id = idu.UserID
group by year(u.CreationDate)

union all

Select 
year(u.CreationDate) as Yr,
'ID Users Return' as Category,
count(*) as Cnt
from Users u
  inner join IDReturn idr
      on u.id = idr.UserID
group by year(u.CreationDate)

union all

Select 
year(u.CreationDate) as Yr,
'Q Users' as Category,
count(*) as Cnt
from Users u
  inner join QUsers idu
      on u.id = idu.UserID
group by year(u.CreationDate)

union all

Select 
year(u.CreationDate) as Yr,
'Q Users Return' as Category,
count(*) as Cnt
from Users u
  inner join QReturn idr
      on u.id = idr.UserID
group by year(u.CreationDate)

order by 1, 2, 3

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results