Day: October 12, 2019

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

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

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

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