力扣sql簡單篇練習(十九)

1 查詢結(jié)果的質(zhì)量和占比

1.1 題目內(nèi)容

1.1.1 基本題目信息

1.1.2 示例輸入輸出

1.2 示例sql語句

# 用count是不會統(tǒng)計為null的數(shù)據(jù)的
SELECT query_name,ROUND(AVG(rating/position),2) quality,ROUND(count(IF(rating<3,rating,null))/count(rating)*100,2) poor_query_percentage
FROM Queries
GROUP BY query_name

1.3 運行截圖

2 每個帖子的評論數(shù)

2.1.1 基本題目信息

2.1.2 示例輸入輸出

2.2 示例sql語句

# 先找到帖子,然后找到回帖的部分 然后進行拼接即可
# 帖子與回帖均有重復的
SELECT s1.sub_id post_id,IFNULL(count(s2.parent_id),0) number_of_comments 
FROM
(SELECT distinct sub_id,parent_idFROM SubmissionsWHERE parent_id is null
) s1
LEFT JOIN
(SELECT distinct sub_id,parent_idFROM SubmissionsWHERE parent_id is not null
) s2
ON s1.sub_id=s2.parent_id
GROUP BY s1.sub_id
ORDER BY post_id asc

2.3 運行截圖

3 不同國家的天氣類型

3.1 題目內(nèi)容

3.1.1 基本題目信息

3.1.2 示例輸入輸出

a 示例輸入

b 示例輸出

3.2 示例sql語句

SELECT c1.country_name,IF(AVG(c2.weather_state)<=15,'Cold',IF(AVG(c2.weather_state)>=25,'Hot','Warm'))  weather_type
FROM Countries c1            
INNER JOIN
(SELECT country_id,weather_state,dayFROM WeatherWHERE date_format(day,'%Y-%m')='2019-11'
) c2
ON c1.country_id=c2.country_id
GROUP BY c1.country_id

3.3 運行截圖