select a.user_id uid from user_ad_click_time a left join user_payment_time b on a.user_id=b.user_id and a.trace_id=b.trace_id where TIMESTAMPDIFF(MINUTE, a.click_time, b.pay_time) < 5 order by uid desc;

select date,user_id,pass_count from (select date,user_id,pass_count ,rank() over(partition by date order by pass_count desc ) rk from questions_pass_record ) t where rk<=2 order by date;

select distinct u.device_id,u.university,count(q.question_id) as question_cnt, sum(if(q.result="right",1,0)) as right_question_cnt from user_profile as u left join question_practice_detail as q on u.device_id=q.device_id where u.university="复旦大学" and month(q.date)=8 group by u.de...

select u.university,d.difficult_level,count(q.question_id)/count(distinct q.device_id) avg_answer_cnt from user_profile u,question_practice_detail q,question_detail d where u.device_id=q.device_id and q.question_id=d.question_id group by u.university,d.difficult_level;

select u.device_id,q.question_id,q.result from user_profile u,question_practice_detail q where u.device_id=q.device_id and u.university="浙江大学";

select day(date) as day,count(device_id) as question_cnt from question_practice_detail where month(date)=8 group by day;

select substring_index(substring_index(profile,",",3),"," ,-1)as age,count(device_id) as number from user_submit group by age;