沧海ss

分类

/ 注册

全部文章（共9篇）

疑惑| #10月的新户客单价和获客成本#我的答案哪里有问题

select round(avg(t.avg_amount1),1) avg_amount ,round(avg(t.avg_cost1),1) avg_cost from ( select uid ,min(event_time) ,avg(total_amou...

2024-06-28

0 158

题解 | #计算用户的平均次日留存率#

#留存率次日继续刷题的用户/第一天刷题的用户 select avg(if(datediff(date2,date1)=1,1,0)) avg_ret from ( select device_id ,date date1 ,lead(date)over(pa...

2024-05-24

1 144

题解 | #统计复旦用户8月练题情况#

select u.device_id ,u.university ,count(q.question_id) question_cnt ,sum(if(result='right',1,0)) right_question_cnt from user_pro...

2024-05-24

1 111

题解 | #exists查找未分配具体部门的员工的所有信息#

select * from employees e where not exists ( select * from dept_emp d where d.emp_no = e.emp_no ) # not exist ；子查询对于两张表能匹配的对象返回TURE值，然...

2024-05-23

1 167

题解 | #给出表中排名为奇数行的first_name#

select first_name from ( select first_name ,row_number()over(order by first_name ) r from employees ) a where r%2!=0 ###这个不会按照原表排...

2024-05-22

1 141

题解 | #找出每个学校GPA最低的同学#

select device_id ,university ,round(gpa,4) from ( select device_id ,university ,gpa ,row_number()over(partition by university order by gpa ) rgpa...

2024-05-22

1 261

题解 |获取员工其当前的薪水比其manager当前薪水高信息

select d.emp_no ,de.emp_no,s1.salary,s2.salary from dept_emp d left join dept_manager de on d.dept_no=de.dept_no left join salaries s1 on d.emp_no...

2024-05-22

1 128

题解 | #统计每个学校的答过题的用户的平均答题数#

select university ,round(count(*)/count(distinct u.device_id),4) avg_answer_cnt # count(*)代表group by分组之后每个组含有多少行，然后计数 from user_profile u join quest...

2024-05-22

1 145

题解 | #查找当前薪水详情以及部门编号dept_no#

SELECT d.emp_no, d.salary, d.from_date, d.to_date, de.dept_no FROM salaries d LEFT JOIN dept_manager de ON d.to_date = de.to_date AND d....

2024-05-21

1 119