Leetcode SQL (#1294, #1303, #1308, #1321 – #1322)

1294. Weather Type in Each Country

Question:

https://leetcode.com/problems/weather-type-in-each-country/

Solution:

select 
a.country_name,
case 
    when avg_weather_state <= 15 then 'Cold'
    when avg_weather_state >= 25 then 'Hot'
    else 'Warm'
end as weather_type
from(
    select 
    c.country_name, 
    avg(weather_state) as avg_weather_state
    from Weather w
        left join Countries c 
            on w.country_id = c.country_id
    where (year(w.day) = 2019 
           and month(w.day) = 11)
    group by 1
) a

1303. Fine the Team Size

Question:

https://leetcode.com/problems/find-the-team-size/

Solution:

select 
e.employee_id, 
e1.team_size
from Employee e
left join (
    select 
    team_id, 
    count(distinct employee_id) as team_size
    from Employee
    group by 1
) e1 
    on e.team_id = e1.team_id

1308. Running Total for Different Genders

Question:

https://leetcode.com/problems/running-total-for-different-genders/

Solution:

select 
gender, 
day, 
sum(score_points) over(partition by gender order by day) as total
from Scores
order by gender, day

1321. Restaurant Growth

Question:

https://leetcode.com/problems/restaurant-growth/

Solution:

select 
c.visited_on, 
c.amount, 
round(c.average_amount,2) as average_amount
from(
    select 
    visited_on, 
    /* 7-day moving window */
    count(visited_on) over(order by visited_on rows between 6 preceding and current row) as days,
    sum(amount) over(order by visited_on rows between 6 preceding and current row) as amount,
    avg(amount) over(order by visited_on rows between 6 preceding and current row) as average_amount
    from (
        select 
        visited_on, 
        sum(amount) as amount 
        from Customer 
        group by 1 
    ) u
) c
where c.days = 7
order by 1

1322. Ads Performance

Question:

https://leetcode.com/problems/ads-performance/

Solution:

select 
ad_id, 
round(ifnull(100*(clicks/clicks_and_views),0),2) as ctr
from (
    select 
    ad_id, 
    sum(case when action = 'Clicked' then 1 else 0 end) as clicks,
    sum(case when action in ('Clicked', 'Viewed') then 1 else 0 end) as clicks_and_views
    from Ads
    group by 1
) a
order by 2 desc, 1