Gendered comments by date

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

select  c.Text comment,
        DATEADD(day,0,DATEDIFF(day,0,c.CreationDate)) comment_date
into    #coms
from    Comments c 
        left join Users u on u.Id = c.UserId
where   c.CreationDate > '09/12/19' 
        and c.CreationDate < '10/13/19'

select  comment_date,
        sum(case when 
        comment LIKE '%[^a-zA-Z0-9][Hh]e[^a-zA-Z0-9]%' OR
        comment LIKE '%[^a-zA-Z0-9][Hh]im[^a-zA-Z0-9]%' OR
        comment LIKE '%[^a-zA-Z0-9][Hh]is[^a-zA-Z0-9]%' OR
        comment LIKE '%[^a-zA-Z0-9][Ss]he[^a-zA-Z0-9]%' OR
        comment LIKE '%[^a-zA-Z0-9][Hh]er[^a-zA-Z0-9]%'
        then 1 else 0 end) gendered,
        sum(case when 
        comment LIKE '%[^a-zA-Z0-9][Tt]hey[^a-zA-Z0-9]%' OR
        comment LIKE '%[^a-zA-Z0-9][Tt]hem[^a-zA-Z0-9]%' OR
        comment LIKE '%[^a-zA-Z0-9][Tt]heir[^a-zA-Z0-9]%'
        then 1 else 0 end) neutral,
        count(comment) total
into    #coms_type
from    #coms
group by comment_date

select  comment_date,
        gendered / (total * 1.0) gendered_pct,
        neutral / (total * 1.0) neutral_pct
from    #coms_type
order by comment_date

Enter Parameters

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