Mysql100

SQL22 统计每个学校的用户平均答题数

select university,(count(question_cnt)/count(DISTINCT(q.device_id)))
as avg_answer_cnt
from user_profile u join question_practice_detail q 
on u.device_id=q.device_id
group by university

SQL26 计算25岁以上和以下的用户数量

SELECT
if(age>=25,'25岁以及上','25岁以下') as age_cut,
count(*) as Number
from user_profile group by age_cut

SQL28 计算用户8月每天的练题数量

select day(date)as day,
count(*) as question_cnt
from question_practice_detail
where year(date)=2021 and month(date)=08
group by day

★SQL29 计算用户的平均次日留存率

SELECT 
avg(if(b.device_id is not null,1,0)) as avg_ret
from 
(select distinct device_id,date from question_practice_detail)a 
left join (select distinct device_id,
      date_sub(date,INTERVAL 1 day)as date from question_practice_detail)b 
on a.device_id=b.device_id and a.date=b.date

SQL30 统计每种性别的人数

SELECT
SUBSTRING_INDEX(profile,',',-1)as gender,
count(*) as number
from user_submit group by gender

SQL32 截取出年龄

SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(profile,',',-2),',',1)as age,
count(*) as number
from user_submit group by age