select uid, count(*)-count(submit_time) as incomplete_cnt , count(submit_time) as complete_cnt , group_concat(distinct concat_ws(':',date(start_time),tag) separator ';') as detail from exam_record e join examination_info ei using(exam_id) where year(start_time)='2021' group by uid having complete_cn...

select date_format (submit_time, '%Y%m') as month, round(count(distinct uid,date_format(submit_time,'%Y%m%d'))/count(distinct uid),2) as avg_active_days, count(distinct uid) as mau from exam_record where submit_time is not null and year (submit_time) = 2021 group by month;

select cust_name ,t.total_price from (select order_num,sum(item_price * quantity) as total_price from OrderItems oi group by oi.order_num ) t inner join Orders o on t.order_num=o.order_num join Customers c on c.cust_id=o.cust_id having total_price>=1000;

select Orders.cust_id from Orders join OrderItems on Orders.order_num= OrderItems.order_num group by Orders.cust_id having sum(item_price)>=10;

select order_num from (select order_num,sum(quantity) as sq from OrderItems group by order_num) as op where sq >= 100 order by order_num;

select cust_id,cust_name, upper(concat(left (cust_name,2),left (cust_city,3))) as user_login from Customers;

select difficult_level,sum(if(uq.result='right',1,0))/count(uq.question_id) as correct_rate from (select up.device_id,up.university,qpd.question_id,qpd.result from user_profile as up join question_practice_detail as qpd on up.device_id=qpd.device_id where university='浙江大学') as uq left join question_...

select up.device_id,university,count(question_id),sum(if(qpd.result='right',1,0)) as right_question_cnt from user_profile as up left join question_practice_detail as qpd on up.device_id=qpd.device_id where university='复旦大学' and month(date)=8 group by up.device_id;

select university,difficult_level,count(qpd.question_id)/count(distinct qpd.device_id) from user_profile as up,question_practice_detail as qpd,question_detail as qd where up.device_id=qpd.device_id and qpd.question_id=qd.question_id group by university,difficult_level having university='山东大学';