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

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

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

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

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

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