【高频SQL基础50题】31-35

又到SQL。

1.查询结果的质量和占比

2.求关注者的数量

3.指定日期的产品价格

4.好友申请 II ：谁有最多的好友

5.按日期分组销售产品

1.查询结果的质量和占比

聚合题。

# Write your MySQL query statement below
SELECT t1.query_name,ROUND((SUM(t1.rating/t1.position))/COUNT(*),2) AS quality,ROUND((SUM(IF(t1.rating<3,1,0))/COUNT(*))*100,2) AS poor_query_percentage
FROM Queries t1
GROUP BY t1.query_name
HAVING query_name is not null;

2.求关注者的数量

排序和分组题。

# Write your MySQL query statement below
SELECT user_id,COUNT(follower_id) followers_count
FROM Followers
GROUP BY user_id
ORDER BY user_id asc;

asc递增排序；

3.指定日期的产品价格

窗口题。

t1用来取出商品id，

t2用来更新最新的商品价格，

t3用来包含t2，并判断是否需要更新

t1=t3的id构造出最后的表。

# Write your MySQL query statement below
select t1.product_id,if(t3.price is null,10,t3.price) price
FROM (select distinct product_id from Products) t1
LEFT JOIN (select product_id,price
FROM (select product_id,new_price price,Rank() OVER (PARTITION BY product_id ORDER BY change_date DESC) "r" FROM Products
WHERE change_date<'2019-08-17') t2
WHERE r=1) t3
ON t1.product_id=t3.product_id

4.好友申请 II ：谁有最多的好友

子查询+union all

# Write your MySQL query statement below
SELECT ids as id,COUNT(*) AS num
FROM(
    SELECT requester_id AS ids
    FROM RequestAccepted
    UNION ALL
    SELECT accepter_id AS ids
    FROM RequestAccepted
) AS t
GROUP BY ids
ORDER BY num DESC
LIMIT 1;

5.按日期分组销售产品

高级字符串函数题。

# Write your MySQL query statement below
select 
      sell_date,count(distinct product)num_sold,
      group_concat(distinct product
      order by product
      separator ',') products
from
     Activities
group by sell_date
order by sell_date