WITH
user_mon_cnt AS (
SELECT
user_id,
DATE_FORMAT(start_time, '%Y-%m-01') AS mon,
count(*) AS cnt
FROM
hello_bike_riding_rcd
WHERE
(
start_loc IN (
'北京机床研究所',
'天启大厦',
'恒通国际商务园',
'瀚海国际大厦',
'西门子大厦'
)
AND end_loc IN ('望京', '望京南', '阜通', '将台西')
)
OR (
start_loc IN ('望京', '望京南', '阜通', '将台西')
AND end_loc IN (
'北京机床研究所',
'天启大厦',
'恒通国际商务园',
'瀚海国际大厦',
'西门子大厦'
)
)
GROUP BY
user_id,
mon
HAVING
cnt >= 5
ORDER BY
user_id,
mon
)
SELECT
DISTINCT user_id,
CASE
WHEN user_id IN (
SELECT
user_id
FROM
(
SELECT
*,
DATE_SUB(mon, interval rnk MONTH) AS base_mon
FROM
(
SELECT
user_id,
mon,
ROW_NUMBER() OVER(
PARTITION BY user_id
ORDER BY
mon
) rnk
FROM
user_mon_cnt
) t
) t2
GROUP BY
user_id,
base_mon
HAVING
COUNT(*) >= 3
) THEN 1
ELSE 0
END AS active_tag
FROM
hello_bike_riding_rcd
ORDER BY
user_id;
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
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 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 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 '2022-10-03 17:20:20' AS time_he_love_me,
DATEDIFF(NOW(),'2022-10-03 17:20:20') AS days_we_falling_me,
TIMESTAMPDIFF(HOUR,'2022-10-03 17:20:20',NOW()) AS hours_we_falling_me,
TIMESTAMPDIFF(DAY,'2022-10-03 17:20:20',MIN(trx_time)) AS days_he_fvck_else
FROM cmb_usr_trx_rcd
WHERE usr_id = 5201314520 AND mch_nm = '红玫瑰按摩保健休闲'
GROUP BY usr_id
WITH fav_gd AS (
SELECT DISTINCT mch_id
FROM xhs_fav_rcd
),
pchs_gd AS (
SELECT DISTINCT mch_id
FROM xhs_pchs_rcd
)
SELECT gd.*
FROM gd_inf AS gd
LEFT JOIN fav_gd AS fav
ON fav.mch_id = gd.gd_id
LEFT JOIN pchs_gd AS pchs
ON pchs.mch_id = gd.gd_id
WHERE fav.mch_id IS NULL
AND pchs.mch_id IS NOT NULL
;
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 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
SELECT fav.mch_id,
COUNT(DISTINCT fav.cust_uid) AS fav_user_count,
COUNT(DISTINCT CASE WHEN fav.mch_id = pchs.mch_id AND fav.fav_tm < pchs.pchs_tm THEN pchs.cust_uid ELSE NULL END) AS conv_user_count,
ROUND(CAST(COUNT(DISTINCT CASE WHEN fav.mch_id = pchs.mch_id AND fav.fav_tm < pchs.pchs_tm THEN pchs.cust_uid ELSE NULL END) AS float) / COUNT(DISTINCT fav.cust_uid) * 100,2) AS conversion_rate
FROM xhs_fav_rcd AS fav
LEFT JOIN xhs_pchs_rcd AS pchs
ON fav.cust_uid = pchs.cust_uid
GROUP BY fav.mch_id
ORDER BY conversion_rate DESC