SELECT subject, AVG(score) AS average_score
FROM scores
WHERE exam_date = '2024-06-30'
GROUP BY subject
ORDER BY subject
;

SELECT student_id, chinese,math,english
FROM (
SELECT *,
 CASE WHEN chinese >= 110 THEN 1 ELSE 0 END AS ch,
 CASE WHEN math >= 110 THEN 1 ELSE 0 END AS ma,
 CASE WHEN english >= 110 THEN 1 ELSE 0 END AS en
FROM subject_score
) a
WHERE ch + ma + en >= 2
ORDER BY student_id

WITH start_end_cnt AS (
SELECT r.start_loc,r.end_loc,ce.loc_ctg,
 COUNT(*) AS trip_count,
 ROW_NUMBER() OVER(PARTITION BY ce.loc_ctg
 ORDER BY COUNT(*) DESC) AS rnk
FROM didi_sht_rcd AS r
 JOIN loc_nm_ctg AS cs
ON r.start_loc = cs.loc_nm
 JOIN loc_nm_ctg AS ce
ON r.end_loc = ce.loc_nm
WHERE cs.loc_ctg = '酒店'
GROUP BY r.start_loc,r.end_loc,ce.loc_ctg
)
SELECT start_loc,end_loc,loc_ctg,trip_count
FROM start_end_cnt
WHERE rnk = 1
ORDER BY trip_count DESC

SELECT a.mch_nm
FROM (
SELECT DISTINCT mch_nm
FROM cmb_usr_trx_rcd
WHERE trx_time RLIKE '2024' AND usr_id = 5201314520
) a INNER JOIN
( SELECT DISTINCT mch_nm
 FROM cmb_usr_trx_rcd
 WHERE trx_time RLIKE '2024' AND usr_id = 5211314521
) b
ON a.mch_nm = b.mch_nm
ORDER BY a.mch_nm DESC

SELECT cty, status,COUNT(order_id) AS order_count
FROM hll_t1
GROUP BY cty,status
ORDER BY cty, status

WITH view_cnt AS (
SELECT t3.v_typ,
 t20.v_id,t3.v_nm,
 COUNT(*) AS view_count
FROM bilibili_t20 AS t20
LEFT JOIN bilibili_t3 AS t3
ON t20.v_id = t3.v_id
GROUP BY t3.v_typ,t20.v_id,t3.v_nm
),
view_cnt_rnk AS (
SELECT *,
 RANK() OVER(PARTITION BY v_typ ORDER BY view_count DESC) AS rnk
FROM view_cnt
)
SELECT v_typ,v_id,v_nm,view_count
FROM view_cnt_rnk
WHERE rnk = 1
ORDER BY view_count DESC

SELECT mch_nm
FROM cmb_usr_trx_rcd
WHERE trx_time RLIKE '2024'
GROUP BY mch_nm
HAVING COUNT(DISTINCT CASE WHEN usr_id IN (5201314520,5211314521) THEN usr_id ELSE NULL END) = 2 
ORDER BY mch_nm DESC

WITH usr_act AS (
SELECT usr_id,live_id,enter_time AS event_time,1 AS act
FROM ks_live_t1
UNION ALL
SELECT usr_id,live_id,leave_time AS event_time,-1 AS act
FROM ks_live_t1
ORDER BY live_id,event_time
),
online_users_cnt AS (
SELECT live_id,event_time,
 SUM(act) OVER(PARTITION BY live_id
 ORDER BY event_time) AS online_users
FROM usr_act
),
live_max_online_cnt AS (
SELECT live_id, MAX(online_users) AS max_online_users
FROM online_users_cnt
GROUP BY live_id
ORDER BY max_online_users DESC
)
SELECT lc.live_id,t2.live_nm,lc.max_online_users
FROM live_max_online_cnt AS lc
LEFT JOIN ks_live_t2 AS t2
ON lc.live_id = t2.live_id
ORDER BY max_online_users DESC

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 trx_time RLIKE '2024-09'
GROUP BY DATE(trx_time)
ORDER BY 1

SELECT *
FROM cmb_usr_trx_rcd
WHERE usr_id = 5201314520
AND (DATE(trx_time) BETWEEN '2024-09-01' AND '2024-09-30')
AND (TIME(trx_time) >= '01:00:00' AND TIME(trx_time) < '06:00:00')
ORDER BY trx_time

WITH usr_typ AS (
SELECT t20.usr_id,t20.v_id,t20.v_tm,t3.v_typ
FROM bilibili_t20 AS t20
LEFT JOIN bilibili_t3 AS t3
ON t20.v_id = t3.v_id
WHERE t20.v_tm RLIKE '^2021-02-0[5678]'
)
SELECT ta.v_typ,
 COUNT(DISTINCT ta.usr_id) AS total_views,
 COUNT(DISTINCT tb.usr_id) AS retained_users,
 CAST(COUNT(DISTINCT tb.usr_id) / COUNT(DISTINCT ta.usr_id)*100 AS decimal(5,2)) AS retention_rate
FROM usr_typ AS ta
LEFT JOIN usr_typ AS tb
ON ta.usr_id = tb.usr_idAND 
 DATEDIFF(tb.v_tm,ta.v_tm)BETWEEN 1 AND 3
WHERE ta.v_tm RLIKE '^2021-02-05'
GROUP BY ta.v_typ
ORDER BY retention_rate DESC

SELECT mch_nm AS merchant_name, 
 CASE WHEN mch_nm RLIKE '拼多多' THEN '拼多多' 
WHEN mch_nm RLIKE '京东' THEN '京东'
WHEN mch_nm RLIKE '淘宝' THEN '淘宝'
WHEN mch_nm RLIKE '抖音' THEN '抖音'
WHEN mch_nm RLIKE '小红书' THEN '小红书'
ELSE '其他'
 END AS platform
FROM (
SELECT DISTINCT mch_nm
FROM ccb_trx_rcd
) new_rcd

SELECT city, CAST(AVG(tmp_h) AS decimal(4,2)) AS avg_tmp_h
FROM weather_rcd_china
WHERE YEAR(dt) = 2021
GROUP BY city
ORDER BY 2 DESC

SELECT user_id
FROM apple_pchs_rcd
GROUP BY user_id
HAVING SUM(CASE WHEN product_type <> 'iPhone' THEN 1 ELSE 0 END) = 0
ORDER BY user_id

SELECT cust_uid,mch_nm
FROM mt_trx_rcd1
WHERE cust_uid = 'MT10000'
GROUP BY cust_uid,mch_nm
ORDER BY mch_nm

WITH year_top_merchants AS (
SELECT mch_nm,
 SUM(trx_amt) AS sum_trx_amt
FROM cmb_usr_trx_rcd
WHERE usr_id = 5201314520
AND YEAR(trx_time) = 2024
GROUP BY mch_nm
ORDER BY sum_trx_amt DESC
LIMIT 3
),
month_top_merchants AS (
SELECT SUBSTR(trx_time, 1,7) AS trx_mon,
 mch_nm,
 SUM(trx_amt) AS sum_trx_amt,
 ROW_NUMBER() OVER(PARTITION BY SUBSTR(trx_time, 1,7)
ORDER BY SUM(trx_amt) DESC) AS rk
FROM cmb_usr_trx_rcd
WHERE usr_id = 5201314520
AND YEAR(trx_time) = 2024
GROUP BY SUBSTR(trx_time, 1,7), mch_nm
)
SELECT '2024' AS trx_mon,mch_nm,sum_trx_amt
FROM year_top_merchants
UNION
SELECT trx_mon, mch_nm, sum_trx_amt
FROM month_top_merchants
WHERE rk <= 3

SELECT student_id
FROM subject_score
WHERE chinese + math +english >= 300
ORDER BY student_id

SELECT *
FROM numbers_for_fun
WHERE (a = 0 AND b = 0 AND c <= 0)
OR (a = 0 AND b > 0 AND c <= 0)
OR (a < 0 AND -b/2*a >= 0 AND c <= 0)
OR (a < 0 AND -b/2*a < 0 AND 4*a*c-b*b >= 0)
ORDER BY id

SELECT start_loc, end_loc, COUNT(*) AS trip_count
FROM didi_sht_rcd
WHERE start_loc IN (SELECT loc_nm
FROM loc_nm_ctg
WHERE loc_ctg = '酒店')
AND end_loc IN (SELECT loc_nm
FROM loc_nm_ctg
WHERE loc_ctg = '餐饮')
GROUP BY start_loc,end_loc
ORDER BY 3 DESC
LIMIT 1

WITH all_live_rnk AS (
SELECT t1.live_id,t2.live_nm,t2.live_type,COUNT(*) AS enter_cnt,
 ROW_NUMBER() OVER(PARTITION BY t2.live_type
 ORDER BY COUNT(*) DESC) AS rnk
FROM ks_live_t1 AS t1
LEFT JOIN ks_live_t2 AS t2
ON t1.live_id = t2.live_id
WHERE enter_time RLIKE '^2021-09-12 23'
GROUP BY t1.live_id,t2.live_nm,t2.live_type
)
SELECT live_id,live_nm,live_type,enter_cnt
FROM all_live_rnk
WHERE rnk = 1
ORDER BY live_id