1294. Weather Type in Each Country

Question:

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:

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:

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:

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:

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``````