0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people interested in the history and origins of science and mathematics

--python  (Data Science)
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 < '2017-04-01'
          and creationdate >= '2017-01-01'
              and (  p1.tags like '%scikit-learn%'
                    or p1.tags like '%pytorch%'
                          or p1.tags like '%pyspark%' 
                                or p1.tags like '%theano%'
                                      or p1.tags like '%numpy%'
                                            or p1.tags like '%scipy%'
                                                  or p1.tags like '%pandas%' 
                                                        or p1.tags like '%scrapy%'
                                                              or (p1.tags like '%py%' and ( p1.tags like '%keras%' 
                                                                                            or p1.tags like '%tensorflow%'
                                                                                                                          or p1.tags like '%machine-learning%'
                                                                                                                                                        or p1.tags like '%deep-learning%'
                                                                                                                                                                                      or p1.tags like '%h2o%'
                                                                                                                                                                                                                    or p1.tags like '%artificial-intelligence%'
                                                                                                                                                                                                                                                   )
                                                                                                                                                                                                                                                            )
                                                                                                                                                                                                                                                                  ) 
                                                                                                                                                                                                                                                                    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 < '2017-04-01'
                                                                                                                                                                                                                                                                                and p2.creationdate >= '2017-01-01'
                                                                                                                                                                                                                                                                                    and (  p1.tags like '%scikit-learn%'
                                                                                                                                                                                                                                                                                          or p1.tags like '%pytorch%'
                                                                                                                                                                                                                                                                                                or p1.tags like '%pyspark%' 
                                                                                                                                                                                                                                                                                                      or p1.tags like '%theano%'
                                                                                                                                                                                                                                                                                                            or p1.tags like '%numpy%'
                                                                                                                                                                                                                                                                                                                  or p1.tags like '%scipy%'
                                                                                                                                                                                                                                                                                                                        or p1.tags like '%pandas%' 
                                                                                                                                                                                                                                                                                                                              or p1.tags like '%scrapy%'
                                                                                                                                                                                                                                                                                                                                    or (p1.tags like '%py%' and ( p1.tags like '%keras%' 
                                                                                                                                                                                                                                                                                                                                                                  or p1.tags like '%tensorflow%'
                                                                                                                                                                                                                                                                                                                                                                                                or p1.tags like '%machine-learning%'
                                                                                                                                                                                                                                                                                                                                                                                                                              or p1.tags like '%deep-learning%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                            or p1.tags like '%h2o%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          or p1.tags like '%artificial-intelligence%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         )
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  )
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        ) 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          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 < '2017-04-01'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      and LastEditDate >= '2017-01-01'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          and (  p1.tags like '%scikit-learn%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                or p1.tags like '%pytorch%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      or p1.tags like '%pyspark%' 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            or p1.tags like '%theano%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  or p1.tags like '%numpy%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        or p1.tags like '%scipy%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              or p1.tags like '%pandas%' 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    or p1.tags like '%scrapy%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          or (p1.tags like '%py%' and ( p1.tags like '%keras%' 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        or p1.tags like '%tensorflow%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      or p1.tags like '%machine-learning%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    or p1.tags like '%deep-learning%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  or p1.tags like '%h2o%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                or p1.tags like '%artificial-intelligence%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               )
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        )
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              ) 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                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 < '2017-04-01'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            and p2.LastEditDate >= '2017-01-01'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                and (  p1.tags like '%scikit-learn%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      or p1.tags like '%pytorch%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            or p1.tags like '%pyspark%' 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  or p1.tags like '%theano%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        or p1.tags like '%numpy%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              or p1.tags like '%scipy%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    or p1.tags like '%pandas%' 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          or p1.tags like '%scrapy%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                or (p1.tags like '%py%' and ( p1.tags like '%keras%' 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              or p1.tags like '%tensorflow%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            or p1.tags like '%machine-learning%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          or p1.tags like '%deep-learning%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        or p1.tags like '%h2o%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      or p1.tags like '%artificial-intelligence%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     )
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              )
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    ) 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      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 < '2017-04-01'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  and c.creationdate >= '2017-01-01'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      and (  p1.tags like '%scikit-learn%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            or p1.tags like '%pytorch%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  or p1.tags like '%pyspark%' 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        or p1.tags like '%theano%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              or p1.tags like '%numpy%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    or p1.tags like '%scipy%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          or p1.tags like '%pandas%' 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                or p1.tags like '%scrapy%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      or (p1.tags like '%py%' and ( p1.tags like '%keras%' 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    or p1.tags like '%tensorflow%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  or p1.tags like '%machine-learning%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                or p1.tags like '%deep-learning%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              or p1.tags like '%h2o%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            or p1.tags like '%artificial-intelligence%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           )
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    )
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          ) 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            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 < '2017-04-01'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              and c.creationdate >= '2017-01-01'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  and (  p1.tags like '%scikit-learn%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        or p1.tags like '%pytorch%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              or p1.tags like '%pyspark%' 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    or p1.tags like '%theano%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          or p1.tags like '%numpy%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                or p1.tags like '%scipy%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      or p1.tags like '%pandas%' 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            or p1.tags like '%scrapy%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  or (p1.tags like '%py%' and ( p1.tags like '%keras%' 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                or p1.tags like '%tensorflow%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              or p1.tags like '%machine-learning%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            or p1.tags like '%deep-learning%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          or p1.tags like '%h2o%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        or p1.tags like '%artificial-intelligence%'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       )
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                )
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      ) 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        )
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        select date, 'Python (Data Science)' programming_language, count(distinct user_id) distinct_users_count
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        from users
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        group by date

view current version

edited jul 8 20

created jul 8 20 jon.doe33167