Queries

Here, we find some basic queries [1] (with constraints oracle sql) that we tried in order to optimize :

select u.id, badgecount, questioncount, cast(badgecount as float)/questioncount as ratio
from users u
inner join (
select userid, count(id) as badgecount
from badges
where UPPER(name) LIKE '%POPULAR QUESTION%'
group by userid
) pop on u.id = pop.userid
inner join (
select owneruserid, count(id) as questioncount
from posts
where posttypeid = 1
group by owneruserid
) q on u.id = q.owneruserid
where badgecount >= 10
order by ratio desc

select id,
(select count() from posts
where
posttypeid = 1 and
lasteditoruserid = users.id and
owneruserid != users.id ) questionedits,
( select count() from posts
where
posttypeid = 2 and
lasteditoruserid = users.id and
owneruserid != users.id) answeredits,
(select count(*) from posts
where
lasteditoruserid = users.id and
owneruserid != users.id) totaledits
from users
order by totaledits desc

select u.id, (cast(count(a.id) as float) / cast((select count(*) from posts p where p.owneruserid = u.id and posttypeid = 1) as float) * 100) as selfanswerpercentage
from posts q
inner join posts a on q.acceptedanswerid = a.id
inner join users u on u.id = q.owneruserid
where q.owneruserid = a.owneruserid
group by u.id, displayname
having count(a.id) > 1
order by selfanswerpercentage desc

select t.postid, t.upvotes, t.downvotes, p.body, p.score, p.owneruserid from (
select postid,
sum(case when votetypeid = 2 then 1 else 0 end) as upvotes,
sum(case when votetypeid = 3 then 1 else 0 end) as downvotes
from votes where votetypeid in (2,3)
group by postid
) t inner join posts p on t.postid = p.id
where downvotes>(upvotes * 0.5)
order by upvotes desc

select p.owneruserid, p.id, p.score
from posts p
inner join posts q on q.id = p.parentid
where p.posttypeid = 2 and p.score > 5
and q.score > 3 and q.answercount = 1
and q.acceptedanswerid = p.id

select parentid, count(id)
from posts
where posttypeid = 2 and length(body) <= 1500
and body like '%ja%'
group by parentid
having count(id) > 1
order by count(id) desc

7. **Usagers qui participent plus dans les posts **
   select users.id, count(posts.id) as answers, cast(avg(cast(score as float)) as numeric(6,2)) as average_answer_scor
   from posts
   inner join users on users.id = owneruserid
   where posttypeid = 2
   group by users.id, displayname
   having count(posts.id) > 10
   order by average_answer_scor desc

Refs : [1] https://meta.stackoverflow.com/