SELECT ROUND(AVG(total_amount), 1) AS avg_amount, ROUND(AVG(cost), 1) AS avg_cost FROM ( SELECT uid, order_id, total_amount, (SUM(price * cnt) - total_amount) AS cost FROM ( SELECT uid, order_id, total_amount, status, event_time, ROW_NUMBER() OVER(PARTITION BY uid ORDER BY event_time) AS first_rank ...

SELECT product_id, ROUND(COUNT(IF(purchase_cnt > 1, uid, NULL)) / COUNT(uid), 3) AS repurchase_rate FROM ( SELECT product_id, uid, COUNT(product_id) AS purchase_cnt FROM tb_product_info JOIN tb_order_detail USING(product_id) JOIN tb_order_overall USING(order_id) WHERE status = 1 AND DATEDIFF((SEL...

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

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, NULL)) / COUNT(IF(if_click = 1, product_id, NULL)), 3) AS cart_rate, ROUND(COUNT(IF(if_payment = 1, product_id, NULL)) / COUNT(IF(if_cart = 1, product_id, NULL))...

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 status BETWEEN 0 AND 1 GROUP BY month HAVING SUM(total_amount) > 100000 ORDER BY GMV

SELECT uid, DATE_FORMAT(DATE_ADD(new_dt, INTERVAL 1 DAY), '%Y%m') AS month, SUM(day_coin) AS coin FROM ( -- 获取各uid每个消费日期与其日期排名的差值，差值一样的消费日期即为连续签到的日期, -- 对差值和uid重新用窗口函数进行分组排序，计算每天获得的金币数（连续签到三天、七天时，第三天获得2+1=3个金币，第七天获得6+1=7个金币） SELECT uid, dt, dt_rank, DATE_SUB(dt, INTERVAL dt_rank DAY) AS new_dt, CASE...

-- 建立活跃用户表 WITH act_user AS ( SELECT uid, DATE(in_time) AS dt FROM tb_user_log UNION SELECT uid, DATE(out_time) AS dt FROM tb_user_log ) SELECT dt, dau, ROUND(IF(new_user_cnt IS NULL, 0, new_user_cnt / dau), 2) AS uv_new_ratio FROM ( -- 计算当天的活跃用户数 SELECT dt, COUNT(DISTINCT uid) AS dau FROM act_user ...

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) / (SELECT COUNT(DISTINCT uid) FROM tb_user_log WHERE in_time IS NOT NULL), 2) AS radio FROM( -- 筛选忠实用户 SELECT t_u_l.uid AS uid, '忠实用户' AS user_grade FROM tb_user_log ...

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(in_time)) AS dt FROM tb_user_log GROUP BY uid )u_m_a LEFT JOIN ( -- 根据解释，当进入时间和离开时间跨天了，则记该用户次日留存 -- 建立用户活跃表 SELECT uid, DATE(in_time) AS dt FROM tb_user_log UNION SELECT uid...

-- 每天人均浏览文章时长 = 文章浏览总时长 / 总浏览人数 SELECT dt, ROUND(SUM(cost_time) / COUNT(DISTINCT uid), 1) AS avg_viiew_len_sec FROM ( SELECT uid, SUBSTRING(in_time, 1, 10) AS dt, TIMESTAMPDIFF(SECOND, in_time, out_time) AS cost_time FROM tb_user_log WHERE YEAR(in_time) = '2021' AND MONTH(in_time) = '11' AND artical...

使用COUNT()函数时，注意值为NULL和0的区别。值为NULL时不会被算入内，但为0时会被count()纳入计算。 根据解释，最近无播放天数应该视频的最近播放日期和全部视频中最近播放天数之差  SELECT video_id, ROUND((100 * play_rate + 5 * like_cnt + 3 * comment_cnt + 2 * retweet_cnt) / fresh_index, 0) AS hot_index FROM ( SELECT video_id, COUNT(IF(TIMESTAMPDIFF(SECOND, start_time, end_time) &...

SELECT * FROM ( SELECT tag, dt, SUM(like_cnt) OVER(PARTITION BY tag ORDER BY dt DESC ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING) AS sum_like_cnt_7d, MAX(retweet_cnt) OVER(PARTITION BY tag ORDER BY dt DESC ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING) AS retweet_cnt_7d FROM ( SELECT tag, DATE(start_time) A...

SELECT author, month, ROUND(fans / play_cnt, 3) AS fans_growth_rate, SUM(fans) OVER(PARTITION BY author ORDER BY month) AS total_fans FROM ( SELECT author, SUBSTRING(start_time, 1, 7) AS month, SUM( CASE WHEN if_follow = 1 THEN 1 WHEN if_follow = 0 THEN 0 WHEN if_follow = 2 THEN -1 END ) AS fans, CO...

-- 有用户互动的最近时间：max(start_time) -- 最近一个月：当天-29天 SELECT tag, SUM(if_retweet) AS retweet_cut, ROUND(SUM(if_retweet) / COUNT(start_time), 3) AS retweet_rate FROM tb_user_video_log JOIN tb_video_info USING(video_id) WHERE DATEDIFF((SELECT MAX(start_time) FROM tb_user_video_log), start_time) <= 29 GROUP...