SELECT
b.mch_typ,
COUNT(1) AS trx_cnt,
SUM(a.trx_amt) AS trx_amt
FROM
(
SELECT
mch_nm,
trx_amt
FROM
cmb_usr_trx_rcd
WHERE
usr_id = 5201314520
AND YEAR(trx_time) = 2024
) AS a
LEFT JOIN cmb_mch_typ AS b
ON a.mch_nm = b.mch_nm
GROUP BY b.mch_typ
ORDER BY trx_amt DESC
;
SELECT COUNT(DISTINCT usr_id) AS active_users
FROM user_login_log
WHERE login_time >= DATE_FORMAT(DATE_ADD(CURRENT_DATE(), INTERVAL -1 MONTH), '%y-%m-01')
AND login_time < DATE_FORMAT(CURRENT_DATE(), '%y-%m-01')
SELECT sii.singer_name,
COUNT(*) AS total_plays,
CASE WHEN sii.singer_nameIN ('周杰伦','Michael Jackson') THEN '特别推荐'
ELSE '' END AS recommendation
FROM singer_info AS sii
LEFT JOIN song_info AS soi
ON sii.singer_id = soi.origin_singer_id
LEFT JOIN listen_rcd AS lr
ON lr.song_id = soi.song_id
GROUP BY sii.singer_id,sii.singer_name
ORDER BY 2 DESC
SELECT a.live_id, t2.live_nm ,a.enter_cnt
FROM (
SELECT live_id, COUNT(*) AS enter_cnt
FROM ks_live_t1
WHERE enter_time RLIKE '2021-09-12 23'
GROUP BY live_id
) AS a LEFT JOIN ks_live_t2 AS t2
ON a.live_id = t2.live_id
ORDER BY 3 DESC
LIMIT 5
SELECT log.video_id,inf.title,
ROUND(SUM(CASE WHEN log.end_time <= DATE_ADD(log.start_time, INTERVAL inf.duration SECOND) THEN 1 END)/COUNT(*) *100,2) 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 start_time >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)
GROUP BY log.video_id
ORDER BY 3 DESC
LIMIT 5
WITH rfm AS (
SELECT cust_uid AS user_id,
TIMESTAMPDIFF(DAY, MAX(trx_dt), CURRENT_DATE) AS recency,
COUNT(DISTINCT trx_dt) AS frequency,
AVG(trx_amt) AS monetary,
DATE_FORMAT(MIN(trx_dt),'%Y-%m') AS first_trx_month
FROM mt_trx_rcd_f
GROUP BY user_id
)
SELECT user_id,
NTILE(3) OVER(ORDER BY recency DESC) AS recency_score,
NTILE(3) OVER(ORDER BY frequency) AS frequency_score,
NTILE(3) OVER(ORDER BY monetary) AS monetary_score,
first_trx_month
FROM rfm
ORDER BY user_id
SELECT CASE WHEN score >= 110 THEN '[110,120]'
WHEN score >= 90 AND score < 110 THEN '[90,110)'
WHEN score >= 60 AND score < 90 THEN '[60,90)'
WHEN score < 60 THEN '[0.60)'
END AS score_range,
COUNT(*) AS num_students
FROM scores
WHERE exam_date = '2024-06-30'
AND subject = '数学'
GROUP BY score_range
SELECT *
FROM subject_score
WHERE student_id NOT IN
(
SELECT student_id
FROM subject_score
WHERE chinese <= 100
AND math <= 100
AND english <= 100 )
AND chinese IS NOT NULL
ORDER BY chinese
SELECT HOUR(start_tm) AS hour,
SUM(CASE WHEN car_cls = 'A' THEN 1 ELSE 0 END) AS A_orders,
COUNT(*) AS total_orders,
ROUND(SUM(CASE WHEN car_cls = 'A' THEN 1 ELSE 0 END) / COUNT(*)*100,2) AS percentage_A_orders
FROM didi_sht_rcd
WHERE HOUR(start_tm) BETWEEN 18 AND 23
GROUP BY HOUR(start_tm)
ORDER BY 1
SELECT
s.song_name,
COUNT(DISTINCT lr.user_id) AS listeners,
CASE
WHEN COUNT(DISTINCT lr.user_id) > 50 THEN '热门歌曲'
ELSE '普通歌曲'
END AS song_popularity
FROM
song_info s
JOIN
listen_rcd lr ON s.song_id = lr.song_id
GROUP BY
s.song_id,s.song_name;
SELECT *, CASE WHEN avg_spending >= 300 THEN '高档'
WHEN avg_spending >= 100 AND avg_spending < 300 THEN '中档'
ELSE '低档' END AS label
FROM(
SELECT mch_nm,SUM(trx_amt) / COUNT(DISTINCT cust_uid) AS avg_spending
FROM mt_trx_rcd1
GROUP BY mch_nm ) a
ORDER BY 2
SELECT order_dt AS order_date,
COUNT(*) AS total_order_count,
COUNT(CASE WHEN status = 'completed' AND t2.banned = 0 AND t3.banned = 0 THEN 1 END) AS valid_order_count,
CONCAT(ROUND(CAST(COUNT(CASE WHEN status = 'completed' AND t2.banned = 0 AND t3.banned = 0 THEN 1 END) / COUNT(*) *100 AS float),2),'%') AS completion_rate
FROM hll_t1 AS t1
LEFT JOIN hll_t2 t2 ON t1.usr_id = t2.usr_id
LEFT JOIN hll_t2 t3 ON t1.driver_id = t3.usr_id
GROUP BY order_dt
ORDER BY 4
WITH user_stats AS (
SELECT
role,
SUM(CASE WHEN banned = 1 THEN 1 ELSE 0 END) AS banned_count,
COUNT(*) AS total_count
FROM hll_t2
GROUP BY role
)
SELECT
us.role,
us.total_count,
us.banned_count,
CONCAT(cast(us.banned_count/us.total_count*100 as decimal(4,2)), '%') AS banned_rate
FROM user_stats us
ORDER BY us.role;
SELECT LPAD(hour_entered,2,'0') AS hour_entered,
enter_count
FROM (
SELECT HOUR(enter_time) AS hour_entered,
COUNT(*) AS enter_count
FROM ks_live_t1
GROUP BY HOUR(enter_time)) AS a
ORDER BY 1
SELECT DATE_FORMAT(trx_time,'%Y') AS Y,
MONTH(trx_time) AS m,
SUM(trx_amt) AS sum_trx_amt
FROM cmb_usr_trx_rcd
WHERE usr_id = 5201314520
AND trx_time RLIKE '^202[2-4]'
AND mch_nm = '红玫瑰按摩保健休闲'
GROUP BY Y,m
ORDER BY Y,m
SELECT COUNT(*) AS rows_num,
COUNT(DISTINCT student_id) AS students_num,
COUNT(score) AS score_num,
COUNT(CASE WHEN score = 0 THEN 1 ELSE NULL END) AS score_zero_num,
COUNT(CASE WHEN score IS NULL THEN 1 ELSE NULL END) AS score_null_num
FROM scores
SELECT trx_amt,
COUNT(*) AS total_trx_cnt,
COUNT(DISTINCT usr_id) AS unique_usr_cnt,
COUNT(*) / COUNT(DISTINCT usr_id) AS avg_trx_per_user
FROM cmb_usr_trx_rcd
WHERE mch_nm = '红玫瑰按摩保健休闲'
AND LEFT(trx_time,7) BETWEEN '2023-01' AND '2024-06'
GROUP BY trx_amt
ORDER BY avg_trx_per_user DESC
LIMIT 5
SELECT cust_uid AS user_id,
TIMESTAMPDIFF(DAY, MAX(trx_dt), CURDATE()) AS recency,
COUNT(*) AS frequency,
SUM(trx_amt) AS trx_dt
FROM mt_trx_rcd_f
GROUP BY cust_uid
ORDER BY user_id
SELECT ts_code, symbol, name,area, industry, list_date
FROM(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY industry
ORDER BY list_date) AS rnk
FROM stock_info
) a
WHERE rnk = 1
ORDER BY list_date