牛客30426190号

分类

/ 注册

全部文章（共28篇）

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

WITH t1 AS ( SELECT user_id, sales_date, ROW_NUMBER() OVER ( PARTITION BY user_id ...

2024-03-23

0 176

题解 | #10月的新户客单价和获客成本#

WITH t AS ( SELECT t3.order_id, AVG(total_amount) AS sum_amount, SUM(price * cnt) - AVG(total_amount) AS cost FROM...

2023-11-28

0 192

题解 | #零食类商品中复购率top3高的商品#

WITH t AS(SELECT uid, t3.product_id, COUNT(t3.product_id) AS order_times FROM tb_order_detail t3 ...

2023-11-28

0 224

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

SELECT '店铺汇总' AS product_id, CONCAT(ROUND((1 - SUM(in_price*cnt) / SUM(price*cnt))*100, 1), '%') AS profit_rate FROM tb_order_detail t3 JOIN ...

2023-11-19

1 319

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

WITH t1 AS( SELECT DISTINCT uid, sign_in, artical_id, DATE(in_time) AS dt, DENSE_RANK() OVER(PARTITION BY ...

2023-11-12

1 259

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

SELECT t1.dt AS dt, COUNT(t1.uid) AS dau, ROUND(COUNT(t2.uid) / COUNT(t1.uid), 2) AS uv_new_ratio FROM ( SELECT uid, D...

2023-11-11

1 262

题解 | #统计活跃间隔对用户分级结果#

WITH today AS( SELECT MAX(DATE(in_time)) FROM tb_user_log ) SELECT user_grade, ROUND(COUNT(uid) / (SELECT COUNT(DISTINCT uid) FROM tb_use...

2023-11-10

1 229

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

SELECT t1.min_time AS dt, ROUND(COUNT(t2.uid) / COUNT(t1.uid), 2) AS sec_cnt FROM( SELECT uid, MIN(DATE(in_time)) min_time...

2023-11-10

0 213

题解 | #近一个月发布的视频中热度最高的top3视频#

WITH t1 AS( SELECT t1.video_id, SUM(if_like) AS like_cnt, SUM(IF(comment_id IS NULL, 0, 1)) AS comment_cnt, SUM(if...

2023-11-04

1 281

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

SELECT tag, '2021-10-01' AS dt, SUM(like_cnt) AS sum_like_cnt_7d, MAX(retweet_cnt) AS max_retweet_cnt_td FROM( SELECT tag,...

2023-11-03

0 179