WITH song AS (
SELECT a.singer_id, b.song_id
FROM singer_info a
JOIN song_info b ON a.singer_id = b.origin_singer_id
WHERE a.singer_id IN (1, 2, 3, 4, 6)
),
main AS (
SELECT
a.user_id,
DATE(a.start_time) AS start_date
FROM listen_rcd a
JOIN song c ON a.song_id = c.song_id
GROUP BY a.user_id, DATE(a.start_time)
),
consecutive_days AS (
SELECT
user_id,
start_date,
(start_date - INTERVAL
DENSE_RANK() OVER (PARTITION BY user_id ORDER BY start_date)
DAY
) AS group_id
FROM main
)
SELECT
user_id,
MAX(cnt) AS max_consecutive_days
FROM (
SELECT
user_id,
group_id,
COUNT(*) AS cnt
FROM consecutive_days
GROUP BY user_id, group_id
) AS grouped
GROUP BY user_id;
WITH rfm_inf AS (
SELECT cust_uid AS user_id,
DATEDIFF(CURDATE(), MAX(trx_dt)) AS re,
COUNT(DISTINCT trx_dt) AS fre,
AVG(trx_amt) AS mo
FROM mt_trx_rcd_f
GROUP BY user_id
ORDER BY user_id
),
rfm_score AS(
SELECT user_id,
NTILE(3) OVER(ORDER BY re DESC) AS re_score,
NTILE(3) OVER(ORDER BY fre) AS fre_score,
NTILE(3) OVER(ORDER BY mo DESC) AS mo_score
FROM rfm_inf
ORDER BY user_id
)
SELECT *
FROM rfm_score
WHERE re_score + fre_score + mo_score = 9
SELECT CASE WHEN trx_amt = 288 THEN '1.WithHand'
WHEN trx_amt = 388 THEN '2.WithMimi'
WHEN trx_amt = 588 THEN '3.BlowJobbie'
WHEN trx_amt = 888 THEN '4.Doi'
WHEN trx_amt = 1288 THEN '5.DoubleFly'
ELSE '6.other'
END AS ser_typ,
count(*) AS trx_cnt,
MIN(DATE(trx_time)) AS first_date
FROM cmb_usr_trx_rcd
WHERE usr_id = 5201314520
AND mch_nm = '红玫瑰按摩保健休闲'
GROUP BY ser_typ
ORDER BY ser_typ;
WITH first_time AS (
SELECT MIN(trx_time) AS ft
FROM cmb_usr_trx_rcd
WHERE usr_id = 5201314520 AND mch_nm = '红玫瑰按摩保健休闲'
GROUP BY usr_id
)
SELECT *
FROM cmb_usr_trx_rcd
WHERE usr_id = 5201314520
AND trx_time BETWEEN (SELECT ft FROM first_time) AND DATE_ADD((SELECT ft FROM first_time), INTERVAL 2 HOUR)
ORDER BY trx_time
WITH new_user AS (
SELECT usr_id,MIN(v_date) AS first_login
FROM bilibili_t100
GROUP BY usr_id
)
SELECT nu.first_login AS login_date,
COUNT(DISTINCT nu.usr_id) AS new_users,
COUNT(DISTINCT CASE WHEN t.m_flg = 1 THEN nu.usr_id ELSE NULL END) AS new_members,
CAST(COUNT(DISTINCT CASE WHEN t.m_flg = 1 THEN nu.usr_id ELSE NULL END) / COUNT(DISTINCT nu.usr_id)*100 AS decimal(5,2)) AS conversion_rate
FROM new_user AS nu
LEFT JOIN bilibili_t100 AS t
ON nu.usr_id = t.usr_id AND nu.first_login = t.v_date
GROUP BY login_date
ORDER BY login_date
SELECT log.video_id, inf.title,
ROUND(COUNT(DISTINCT CASE WHEN TIMESTAMPDIFF(SECOND, log.start_time, log.end_time) >= inf.duration THEN log.uid ELSE NULL END) / COUNT(DISTINCT log.uid) *100,4) AS completion_rate
FROM ks_video_wat_log AS log
LEFT JOIN ks_video_inf AS inf
ON log.video_id = inf.video_id
WHERE DATE_SUB(now(), INTERVAL 1 MONTH) <= start_time
GROUP BY log.video_id,inf.title
ORDER BY 3 DESC
LIMIT 5;
SELECT usr_id, trx_time, trx_amt,mch_nm,
LAG(trx_time,1,NULL) OVER(ORDER BY trx_time) AS prev_trx_time,
DATEDIFF(trx_time, LAG(trx_time,1,NULL) OVER(ORDER BY trx_time)) as days
FROM cmb_usr_trx_rcd
WHERE usr_id = 5201314520
AND mch_nm = '红玫瑰按摩保健休闲'
SELECT t.name AS teacher_name,
COUNT(DISTINCT st.student_id) AS total_students,
COUNT(DISTINCT CASE WHEN sc.score < 60 THEN sc.student_id ELSE NULL END) AS failed_students,
CAST(COUNT(DISTINCT CASE WHEN sc.score < 60 THEN sc.student_id ELSE NULL END) / COUNT(DISTINCT st.student_id)*100 AS decimal(5,2)) AS failure_rate
FROM teachers AS t
LEFT JOIN students AS st
ON t.class_code RLIKE st.class_code
LEFT JOIN scores AS sc
ON sc.student_id = st.student_id
WHERE t.subject = '化学' AND sc.subject = '化学'
GROUP BY t.name
SELECT t1.live_id,
t2.live_nm,
COUNT(*) AS enter_cnt
FROM ks_live_t1 AS t1 LEfT JOIN ks_live_t2 AS t2
ON t1.live_id = t2.live_id
WHERE DATE(t1.enter_time) = '2021-09-12'
AND HOUR(t1.enter_time) = 23
GROUP BY live_id,live_nm
ORDER BY enter_cnt desc
LIMIT 5
SELECT
sum(TIMESTAMPDIFF(SECOND, call_time, grab_time))/count(1) AS avg_response_time_seconds
FROM
didi_order_rcd
WHERE
grab_time != '1970-01-01 00:00:00';
SELECT DATE(trx_time) AS trx_date,
MAX(trx_amt) AS max_trx_amt,
MIN(trx_amt) AS min_trx_amt,
AVG(trx_amt) AS avg_trx_amt,
SUM(trx_amt) AS total_trx_amt
FROM cmb_usr_trx_rcd
WHERE mch_nm = '红玫瑰按摩保健休闲'
AND YEAR(trx_time) = 2024 AND MONTH(trx_time) = 9
GROUP BY DATE(trx_time)
ORDER BY trx_date;
WITH active_usr_be AS (
SELECT DISTINCT usr_id
FROM user_login_log
WHERE DATE(login_time) < '2024-07-01'
GROUP BY usr_id, DATE_FORMAT(login_time,'%Y-%m')
HAVING COUNT(*) >= 10
),
active_usr AS (
SELECT DISTINCT usr_id
FROM user_login_log
WHERE usr_id IN (SELECT usr_id FROM active_usr_be)
AND DATE(login_time) >= '2024-08-01'
GROUP BY usr_id, DATE_FORMAT(login_time,'%Y-%m')
HAVING COUNT(*) >= 10
)
SELECT COUNT(usr_id) AS inactive_user_count
FROM active_usr_be
WHERE usr_id NOT IN (SELECT usr_id FROM active_usr)
SELECT DATE_FORMAT(trx_time,'%Y-%m') AS trx_mon,
LAST_DAY(MAX(trx_time)) AS last_day,
DAY(LAST_DAY(MAX(trx_time))) AS days_of_mon,
SUM(trx_amt) AS trx_amt,
COUNT(trx_time) AS trx_cnt,
SUM(trx_amt)/DAY(LAST_DAY(MAX(trx_time))) AS avg_day_amt,
COUNT(trx_time)/DAY(LAST_DAY(MAX(trx_time))) AS avg_day_cnt
FROM cmb_usr_trx_rcd AS rcd
LEFT JOIN cmb_mch_typ AS typ
ON rcd.mch_nm = typ.mch_nm
WHERE usr_id = 5201314520
AND trx_time RLIKE '^202[34]'
AND typ.mch_typ = '休闲娱乐'
GROUP BY trx_mon
ORDER BY trx_mon
;
SELECT cmb.*
FROM cmb_usr_trx_rcd AS cmb
INNER JOIN(
SELECT MIN(trx_time) AS first_time
FROM cmb_usr_trx_rcd
WHERE usr_id = 5201314520
AND mch_nm RLIKE '红玫瑰'
)AS ft
ON cmb.trx_time BETWEEN ft.first_time AND DATE_ADD(ft.first_time, interval 2 hour)
WHERE cmb.usr_id = 5201314520
;
SELECT *
FROM cmb_usr_trx_rcd
WHERE usr_id = 5201314520
AND DATE(trx_time) BETWEEN '2024-09-01' AND '2024-09-30'
AND (HOUR(trx_time) BETWEEN 22 AND 23 OR HOUR(trx_time) BETWEEN 0 AND 5)
ORDER BY trx_time