爱吃菜叶的猫

题解

评论(2)

/ 注册

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

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

SELECT product_id, ROUND(COUNT(IF(purchase_cnt > 1, uid, NULL)) / COUNT(uid), 3) AS repurchase_rate FROM ( SELECT product_id, uid, ...

Mysql

2022-05-14

0 317

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

SELECT product_id, CONCAT(profit_rate, '%') AS profit_rate FROM ( SELECT COALESCE(product_id, '店铺汇总') AS product_id, ROUND((1 - SUM(in...

Mysql

2022-05-13

0 360

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

SELECT product_id, ROUND(COUNT(IF(if_click = 1, product_id, NULL)) / COUNT(event_time), 3) AS ctr, ROUND(COUNT(IF(if_cart = 1, product_id, NU...

Mysql

2022-05-13

0 268

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

SELECT SUBSTRING(event_time, 1, 7) AS month, ROUND(SUM(total_amount), 0) AS GMV FROM tb_order_overall WHERE YEAR(event_time) = '2021' AND stat...

Mysql

2022-05-13

0 233

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

SELECT uid, DATE_FORMAT(DATE_ADD(new_dt, INTERVAL 1 DAY), '%Y%m') AS month, SUM(day_coin) AS coin FROM ( -- 获取各uid每个消费日期与其日期排名的差值，差值一样的消费...

Mysql

2022-05-12

0 310

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

-- 建立活跃用户表 WITH act_user AS ( SELECT uid, DATE(in_time) AS dt FROM tb_user_log UNION SELECT uid, DATE(out_time) AS dt ...

Mysql

2022-05-11

0 225

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

WITH min_day AS ( SELECT uid, DATE(MIN(in_time)) AS mday FROM tb_user_log GROUP BY uid ) SELECT user_grade, ROUND(COUNT(DISTINCT uid)...

Mysql

2022-05-10

0 248

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

SELECT u_m_a.dt, # u_m_a.uid ROUND(COUNT(u_a.dt) / COUNT(u_m_a.dt), 2) AS uv_rate FROM ( -- 筛选每个用户的最早活跃日期 SELECT uid, MIN(DATE...

Mysql

2022-05-05

1 259

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

-- 每天人均浏览文章时长 = 文章浏览总时长 / 总浏览人数 SELECT dt, ROUND(SUM(cost_time) / COUNT(DISTINCT uid), 1) AS avg_viiew_len_sec FROM ( SELECT uid, SUBS...

Mysql

2022-05-04

0 283

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

使用COUNT()函数时，注意值为NULL和0的区别。值为NULL时不会被算入内，但为0时会被count()纳入计算。根据解释，最近无播放天数应该视频的最近播放日期和全部视频中最近播放天数之差 SELECT video_id, ROUND((100 * play_rate ...

Mysql

2022-05-02

0 235