GuaMiao

分类

/ 注册

全部文章（共92篇）

给出employees表中排名为奇数行的first_name

# 先将数据原来顺序进行排名，以及按照first_name升序进行排名得到 with t1 as ( select first_name, ROW_NUMBER() OVER() as t_rank1, ROW_NUMBER() OVER (ORDER BY first_name) as t...

2023-10-27

0 312

题解 | #国庆期间每类视频点赞量和转发量#

# 先找到2021/9/25到2021/10/03的数据 with t1 as ( select b.tag, a.if_like, a.if_retweet, a.end_time from tb_user_video_log as a left join tb_video...

2023-10-26

0 333

题解 | #某宝店铺连续2天及以上购物的用户及其对应的天数#

# 一个用户在一天可能会有多个购买记录，需要去重 with t1 as ( select DISTINCT user_id, sales_date from sales_tb ), t2 as ( select user_id, sales_date, (case w...

2023-10-23

0 283

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

# 先将2021年10月以来，901店铺的信息找到 with t1 as ( select product_id, in_price from tb_product_info where shop_id = 901 ), t2 as ( # 找到2021-10及之后已...

2023-10-23

0 378

注意10月份的商城新用户意味着用户的首单时间在2021.10

# 先计算2021年10月平台的新用户的首单订单信息, # 此处新用户的定义是第一次下单时间在2021年10月份 with t1 as ( select b.uid, a.order_id, a.total_amount from tb_order_overall as a ...

2023-10-23

0 361

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

# 先计算每一天的活跃用户人数 with t1 as ( select uid, date_format(in_time, '%Y-%m-%d') as dt from tb_user_log union select uid, date_format(out_tim...

2023-10-23

0 331

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

# 先计算每一天的活跃用户人数 with t1 as ( select uid, date_format(in_time, '%Y-%m-%d') as dt from tb_user_log union select uid, date_format(out_tim...

2023-10-22

0 276

题解 | #每个创作者每月的涨粉率及截止当前的总粉丝量#

# 找到2021年每个月哥哥视频的粉丝变化量以及是视频播放量 with t1 as ( select video_id, count(case when if_follow = 1 then uid end) as fan_add, count(case when if_follow...

2023-09-29

0 274

题解 | 使用子查询的方式找出属于Action分类的所有电影

select title, description from film where film_id in ( select a.film_id from film_category as a left join category as b on a.category_...

2023-09-12

0 403

题解 | #平均工资#

# 找出在职员工的薪资 with t1 as ( select emp_no, salary from salaries where to_date = '9999-01-01' ), # 找到最大，最小的工资是多少 t2 as ( select max(salar...

2023-09-12

0 379