Queries - savacano28/db-optimization GitHub Wiki

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

1. • Nombre d'utilisateurs par question populaire - (uniquement les utilisateurs avec au moins 10 questions populaires)

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

2. L'utilisateur qui pose le plus de questions et de réponses sans en être l'auteur

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

3. Usagers qui répondent à ses propres requêtes

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

4. Recherche les messages avec plus de la moitié du nombre de votes négatifs car ils ont des votes positifs Triés par votes positifs

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

6. Post avec mot “Ja”

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/