with users as ( select DATEADD(DAY, DATEDIFF(DAY, 0, crea...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

--python  
with users as (
  -- users who asked a question with the given tag
  select DATEADD(DAY, DATEDIFF(DAY, 0, creationdate), 0) date, OwnerUserId user_id
  from posts p1
  where creationdate < '2020-07-01'
    and creationdate >= '2020-06-01'
    and ((p1.tags like '%py%' and p1.tags not like '%jupyter%' and p1.tags not like '%copy%' and p1.tags not like '%Capybara%' ) 
      or (p1.tags like '%py[^t]%' and p1.tags like '%jupyter%')  -- i.e. it is not just jupyter, it has something else py-like as well
      or (p1.tags like '%py[^>-]%' and p1.tags like '%copy%') 
      or p1.tags like '%pandas%'
      or p1.tags like '%keras%'
      or p1.tags like '%django%'
      or p1.tags like '%flask%'
    )
  union all
  -- users who answered to the questions with the given tag
  select DATEADD(DAY, DATEDIFF(DAY, 0, p2.creationdate), 0) date, p2.OwnerUserId user_id
  from posts p1 left join posts p2 on p1.id = p2.parentid
  where p2.creationdate < '2020-07-01'
    and p2.creationdate >= '2020-06-01'
    and ((p1.tags like '%py%' and p1.tags not like '%jupyter%' and p1.tags not like '%copy%' and p1.tags not like '%Capybara%' ) 
      or (p1.tags like '%py[^t]%' and p1.tags like '%jupyter%')  -- i.e. it is not just jupyter, it has something else py-like as well
      or (p1.tags like '%py[^>-]%' and p1.tags like '%copy%') 
      or p1.tags like '%pandas%'
      or p1.tags like '%keras%'
      or p1.tags like '%django%'
      or p1.tags like '%flask%'
    )
  union all
  -- users who made an edit to the questions with the given tag
  select DATEADD(DAY, DATEDIFF(DAY, 0, LastEditDate), 0) date, LastEditorUserId user_id
  from posts p1
  where LastEditDate < '2020-07-01'
    and LastEditDate >= '2020-06-01'
    and ((p1.tags like '%py%' and p1.tags not like '%jupyter%' and p1.tags not like '%copy%' and p1.tags not like '%Capybara%' ) 
      or (p1.tags like '%py[^t]%' and p1.tags like '%jupyter%')  -- i.e. it is not just jupyter, it has something else py-like as well
      or (p1.tags like '%py[^>-]%' and p1.tags like '%copy%') 
      or p1.tags like '%pandas%'
      or p1.tags like '%keras%'
      or p1.tags like '%django%'
      or p1.tags like '%flask%'
    )
  union all
  -- users who made an edit to the answers of questions with the given tag
  select DATEADD(DAY, DATEDIFF(DAY, 0, p2.LastEditDate), 0) date, p2.LastEditorUserId user_id
  from posts p1 left join posts p2 on p1.id = p2.parentid
  where p2.LastEditDate < '2020-07-01'
    and p2.LastEditDate >= '2020-06-01'
    and ((p1.tags like '%py%' and p1.tags not like '%jupyter%' and p1.tags not like '%copy%' and p1.tags not like '%Capybara%' ) 
      or (p1.tags like '%py[^t]%' and p1.tags like '%jupyter%')  -- i.e. it is not just jupyter, it has something else py-like as well
      or (p1.tags like '%py[^>-]%' and p1.tags like '%copy%') 
      or p1.tags like '%pandas%'
      or p1.tags like '%keras%'
      or p1.tags like '%django%'
      or p1.tags like '%flask%'
    )
  union all
  -- users who commented under questions with the given tag
  select DATEADD(DAY, DATEDIFF(DAY, 0, c.creationdate), 0) date, c.userid user_id
  from posts p1 join comments c on p1.id = c.postid 
  where c.creationdate < '2020-07-01'
    and c.creationdate >= '2020-06-01'
    and ((p1.tags like '%py%' and p1.tags not like '%jupyter%' and p1.tags not like '%copy%' and p1.tags not like '%Capybara%' ) 
      or (p1.tags like '%py[^t]%' and p1.tags like '%jupyter%')  -- i.e. it is not just jupyter, it has something else py-like as well
      or (p1.tags like '%py[^>-]%' and p1.tags like '%copy%') 
      or p1.tags like '%pandas%'
      or p1.tags like '%keras%'
      or p1.tags like '%django%'
      or p1.tags like '%flask%'
    )
  union all
  -- users who commented under answers to questions with the given tag
  select DATEADD(DAY, DATEDIFF(DAY, 0, c.creationdate), 0) date, c.userid user_id
  from posts p2 right join posts p1 on p1.id = p2.parentid
                      join comments c on p2.id = c.postid 
  where c.creationdate < '2020-07-01'
    and c.creationdate >= '2020-06-01'
    and ((p1.tags like '%py%' and p1.tags not like '%jupyter%' and p1.tags not like '%copy%' and p1.tags not like '%Capybara%' ) 
      or (p1.tags like '%py[^t]%' and p1.tags like '%jupyter%')  -- i.e. it is not just jupyter, it has something else py-like as well
      or (p1.tags like '%py[^>-]%' and p1.tags like '%copy%') 
      or p1.tags like '%pandas%'
      or p1.tags like '%keras%'
      or p1.tags like '%django%'
      or p1.tags like '%flask%'
    )
  )
select date, 'Python' programming_language, count(distinct user_id) distinct_users_count
from users
group by date

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results