0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

/*
with t as(
select year(creationdate) as y, month(creationdate) as m, count(id) as c
from users
group by year(creationdate), month(creationdate))


select *
from t 
where c in (select max(c) from t)
*/
--2
/*
select year(creationdate), 1.0 * count (case when tags like '%<kotlin>%' then 1 end) / count(*) *100
from posts
where posttypeid = 1 
group by year(creationdate)
*/
--3
/*
select t.tagname
from posts p join posttags pt on pt.postid = p.id
             join tags t on t.id = pt.tagid     
             join users u on p.owneruserid = u.id
where p.posttypeid = 1
group by t.tagname
having count(case when lower(u.location) like '%nigeria%' then 1 end) > 10 and
       count(case when lower(u.location) like '%iceland%' then 1 end) > 15
*/
--4

select count(u.id)
from users u join posts p on p.owneruserid = u.id
where year(u.creationdate) < 2010 and 
not exists (select *
            from posts p1 join posts p2 
            on p1.owneruserid = p2.owneruserid and p1.owneruserid = u.id
            where p1.creationdate < p2.creationdate and 
                  abs(datediff(dd, 
                               cast(p1.creationdate as date), 
                               cast(p2.creationdate as date)
                               )
                     ) > 365
            )
group by u.id
having count(case when abs(datediff(dd, 
                                    cast('09.12.2019' as date), 
                                    cast(p.creationdate as date)
                                   )
                          ) > 730
             then 1 end) > 0

fork query

edited dec 9 19

created dec 9 19 anonymous