无上清颜

题解

/ 注册

全部文章 / 题解（共49篇）

题解 | #某乎问答回答过教育类问题的用户里有多少用户回答过职场类问题#

先筛选出回答过Education的用户(子查询),在此基础上筛选回答过Career的用户 select count(distinct author_id) as num from issue_tb t join answer_tb t1 using(issue_id) where author_id...

Mysql

2022-02-19

1 535

题解 | #某店铺的各商品毛利率及店铺整体毛利率#

select '店铺汇总' as pproduct_id,concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as profit_rate from tb_product_info t join tb_order_overall...

Mysql

2021-12-23

15 1590

题解 | #统计2021年10月每个退货率不大于0.5的商品各项指标#

select product_id,round(sum(if_click)/count(1),3) as ctr ,round(sum(if_cart)/sum(if_click),3) as cart_rate ,round(su...

Mysql

2021-12-23

2 477

题解 | #计算商城中2021年每月的GMV#

select date_format(event_time,"%Y-%m") as month,sum(case when status in (0,1) then total_amount else 0 end) as GMV from tb_order_overall where year(ev...

Mysql

2021-12-23

1 631

题解 | #连续签到领金币#

with t as ( select uid,date(in_time) as dint, date(in_time)-dense_rank()over(partition by uid order by in_time) as start_time from ...

Mysql

2021-12-23

6 502

题解 | #每天的日活数及新用户占比#

-- 临时表————每个用户第一次登陆数据 with t as( select distinct first_login_time,count(uid)over(partition by first_login_time) as num from (select uid,mi...

Mysql

2021-12-20

1 500

逻辑清晰

with t as ( select uid,max(date(in_time))over(partition by uid) as max_part_time, min(date(in_time))over(partition by uid) as min_...

Mysql

2021-12-20

0 352

题解 | #2021年11月每天新用户的次日留存率#

-- 第一步：建立临时表，利用窗口函数lead，取出每个日期对应的下一个日期数据(算次日留存)。因为可能会涉及跨天活跃问题，所以也需要out_time with t as ( select uid,date(in_time) as dint,date(out_time) as dout, ...

Mysql

2021-12-14

7 755

题解 | #2021年11月每天的人均浏览文章时长#

select date(out_time) as dt,round(sum(TIMESTAMPDIFF(second,in_time,out_time))/count(distinct uid),1) as avg_view_len_sec from tb_user_log where artic...

Mysql

2021-12-13

0 402

第一次用到rank函数

with t as ( select t.artical_id,t.out_time,t1.in_time,TIMESTAMPDIFF(second,t1.in_time,t.out_time) as time_gap, RANK()over(partition by artical_...

Mysql

2021-12-13

4 538