WITH cust_mch AS (
SELECT cust_uid, mch_nm
FROM mt_trx_rcd1
WHERE mch_nm = '兰州李晓明拉面馆'
GROUP BY cust_uid, mch_nm
),
mt10000_mch AS (
SELECT cust_uid
FROM mt_trx_rcd1
WHERE cust_uid = 'MT10000'
GROUP BY cust_uid
)
SELECT mm.cust_uid,cm.cust_uid AS cust_uid_1,cm.mch_nm
FROM mt10000_mch AS mm
LEFT JOIN cust_mch As cm
ON mm.cust_uid <> cm.cust_uid
ORDER BY cm.cust_uid

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

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

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 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

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 
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
substr(trx_time, 1, 7) AS trx_mon,
count(*) AS trx_cnt,
SUM(trx_amt) AS trx_amt
FROM
cmb_usr_trx_rcd
WHERE
usr_id = 5201314520
AND DATE(trx_time) BETWEEN '2022-11-01' AND '2024-12-31'
AND (
(truncate(trx_amt, 0) RLIKE '.+[89]8'
AND trx_amt > 200
AND HOUR(trx_time) IN (23, 0, 1, 2))
OR upper(mch_nm) RLIKE '.*(足疗|保健|按摩|养生|SPA).*'
)
GROUP BY
trx_mon
ORDER BY
trx_mon;

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 DISTINCT SUBSTR(a.trx_time,1,7) AS trx_mon,
 last_day(a.trx_time) AS last_day,
 day(last_day(a.trx_time)) AS day_of_mon
FROM cmb_usr_trx_rcd AS a LEFT JOIN cmb_mch_typ AS b
ON a.mch_nm = b.mch_nm
WHERE a.usr_id = 5201314520 
AND YEAR(a.trx_time) IN (2023, 2024)
AND b.mch_typ = '休闲娱乐'
ORDER BY trx_mon;

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

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 
    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;

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 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