1211. Queries Quality and Percentage

Question:

Solution:

``````select
query_name,
round(avg(rating/position), 2) as quality,
round((sum(case when rating <3 then 1 else 0 end) / count(rating))*100, 2) as poor_query_percentage
from Queries
group by 1``````

1212. Team Scores in Football Tournament

Question:

Solution:

``````select
t.team_id,
t.team_name,
ifnull(sum(m.num_points),0) as num_points
from Teams t
left join(
select
match_id,
host_team as team,
case
when host_goals > guest_goals then 3
when host_goals = guest_goals then 1
when host_goals < guest_goals then 0
end as num_points
from Matches
union
select
match_id,
guest_team as team,
case
when host_goals > guest_goals then 0
when host_goals = guest_goals then 1
when host_goals < guest_goals then 3
end as num_points
from Matches
) m
on m.team = t.team_id
group by 1,2
order by 3 desc, 1``````

1241. Number of Comments per Post

Question:

Solution:

``````select
p.post_id,
from(
select distinct
sub_id as post_id
from submissions
where parent_id is null
) p
left join (
select
parent_id as post_id,
from submissions
where parent_id is not null
group by 1
) c
on p.post_id = c.post_id
order by 1``````

1251. Average Selling Price

Question:

Solution:

``````select
product_id,
round(sum(total_price)/sum(units),2) as average_price
from(
select
p.product_id,
ifnull(p.price,0) as price,
ifnull(s.units,0) as units,
ifnull(p.price * s.units,0) as total_price
from Prices p
left join UnitsSold s
on p.product_id = s.product_id
where (s.purchase_date>= p.start_date
and s.purchase_date<= p.end_date)
) a
group by 1
order by 1``````