【MySQL基础刷题】总结题型（三）

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

avg大神啊…

SELECT query_name, 
ROUND(avg(rating / position), 2) as quality, 
ROUND(avg(rating < 3)*100, 2) as poor_query_percentage 
FROM Queries 
WHERE query_name is not null
GROUP BY query_name;

2.每月交易I

其中DATE_FORMAT是一个日期格式化函数，用于将日期按照指定的格式转换为字符串输出

# Write your MySQL query statement below
select
    DATE_FORMAT(trans_date, '%Y-%m') as month,
    country,
    count(*) as trans_count,
    count(case when state = 'approved' then 1 end) as approved_count,
    sum(amount) as trans_total_amount,
    sum(case when state = 'approved' then amount else 0 end) as approved_total_amount
from
    Tra