没明白哪里不对?
SELECT
inf.gd_typ,
count(pch_trq) buyer_count
FROM xhs_pchs_rcd ph
JOIN gd_inf inf ON ph.mch_id = gd_id
GROUP BY inf.gd_typ
ORDER BY buyer_count DESC
LIMIT 1;
另外感觉答案没有考虑一天内多次oha的情况?
感觉可以通过lag取到上一次消费值,然后做group by?
oha AS (SELECT trx_date,
count(1) ohya_cnt
FROM (SELECT trx_date,
trx_amt current_trx,
lag(trx_amt, 1) OVER (PARTITION BY trx_date ORDER BY trx_time) pre_trx
FROM base_table) a
WHERE current_trx = 1288
AND pre_trx = 888
GROUP BY trx_date)
答案不能包含2024的。但上一题明显说是2023-2024,这一题又没有特别说出来。 下面这个答案去掉2024就对:
SELECT
trx_mon,
sum(trx_amt) OVER (ORDER BY trx_mon) trx_amt
FROM (SELECT d.date_value trx_mon,
coalesce(trx_amt, 0) trx_amt
FROM (SELECT DISTINCT date_format(date_value, '%Y-%m') date_value
FROM date_table
WHERE year(date_value) in ('2023', '2024')) d
LEFT JOIN (SELECT date_format(crd.trx_time, '%Y-%m') trx_mon,
sum(crd.trx_amt) trx_amt
FROM cmb_usr_trx_rcd crd
JOIN cmb_mch_typ cmt
on crd.mch_nm = cmt.mch_nm
WHERE cmt.mch_typ = '休闲娱乐'
AND crd.usr_id = '5201314520'
AND year(crd.trx_time) in ('2023', '2024')
GROUP BY trx_mon) a
ON trx_mon = d.date_value) a
ORDER BY trx_mon
没懂错在哪??
with all_tb as (SELECT null as mch_typ,
mch_nm,
count(*) trx_cnt,
dense_rank() OVER (ORDER BY count(*) desc) rnk
FROM cmb_usr_trx_rcd
WHERE usr_id = '5201314520'
GROUP BY mch_nm),
all_top2 as (SELECT mch_typ, mch_nm, trx_cnt, rnk
FROM all_tb
WHERE rnk <= 2),
mch as (SELECT mch_typ mch_typ,
a.mch_nm,
count(*) trx_cnt,
dense_rank() OVER (PARTITION BY b.mch_typ ORDER BY count(*) desc) rnk
FROM cmb_usr_trx_rcd a
JOIN cmb_mch_typ b
ON a.mch_nm = b.mch_nm
WHERE a.usr_id = '5201314520'
GROUP BY b.mch_typ, a.mch_nm),
mch_top2 as (SELECT mch_typ, mch_nm, trx_cnt, rnk
FROM mch a
WHERE rnk <= 2)
SELECT mch_typ, mch_nm, trx_cnt, rnk
FROM all_top2
UNION ALL
SELECT mch_typ, mch_nm, trx_cnt, rnk
FROM mch_top2
ORDER BY mch_typ,
rnk;
是我没读懂题么?感觉SQL是符合题意的吧。提交也没有错误提示,不好排查。
```sql
select
a.mch_nm asshole_tried,
a.cnt asshole_tried_cnt,
b.mch_nm darling_tried
from
(
select
mch_nm,
count(1) cnt
from cmb_usr_trx_rcd
where usr_id = '5201314520'
and year(trx_time) in (2023,2024)
group by mch_nm
having cnt >= 20
) a
left join (
select
distinct mch_nm
from cmb_usr_trx_rcd
where usr_id = '5211314521'
and year(trx_time) in (2023,2024)
) b
on a.mch_nm = b.mch_nm
order by a.cnt desc
```
消费时间为23:00至03:00
感觉描述为[23:00, 03:00)好些吧。
不然其实应该是类似这样:
hour(trx_time) between 0 and 2 or hour(trx_time) = 23 or date_format(trx_time, '%T') = '03:00:00'
SELECT
uid,
round(AVG(watch_duration),0) AS daily_avg_watch_time
FROM (
SELECT
uid,
DATE(start_time) AS watch_date,
TIMESTAMPDIFF(SECOND, start_time, end_time) AS watch_duration
FROM ks_video_wat_log
) AS daily_watch_times
GROUP BY uid
ORDER BY daily_avg_watch_time DESC limit 5
SELECT inf.video_id,
inf.title,
avg(watch_time / duration) avg_completion_rate
FROM (SELECT video_id, timestampdiff(second, start_time, end_time) watch_time
FROM ks_video_wat_log) log
JOIN ks_video_inf inf ON log.video_id = inf.video_id
GROUP BY inf.video_id, title
ORDER BY avg_completion_rate DESC;
SELECT inf.video_id,
inf.title,
sum(watch_time) / sum(duration) avg_completion_rate
FROM (SELECT video_id, timestampdiff(second, start_time, end_time) watch_time
FROM ks_video_wat_log) log
JOIN ks_video_inf inf ON log.video_id = inf.video_id
GROUP BY inf.video_id, title
ORDER BY avg_completion_rate DESC;
SELECT inf.video_id,
inf.title,
sum(if_like) like_count
FROM ks_video_wat_log log
JOIN ks_video_inf inf ON log.video_id = inf.video_id
GROUP BY inf.video_id, inf.title
ORDER BY like_count DESC;
SELECT date(start_time) dt,
start_loc,
end_loc,
count(1) cnt,
count(distinct user_id)user_cnt,
count(1) / count(distinct user_id) cnt_per_usr
FROM hello_bike_riding_rcd
WHERE year(start_time) = 2024 AND month(start_time) = 10
GROUP BY date(start_time), start_loc, end_loc
ORDER BY dt, start_loc, end_loc
SELECT date(start_time) dt,
start_loc,
end_loc,
count(1) cnt,
count(distinct user_id)user_cnt,
count(distinct user_id) / count(1) cnt_per_usr
FROM hello_bike_riding_rcd
WHERE year(start_time) = 2024 AND month(start_time) = 10
GROUP BY date(start_time), start_loc, end_loc
ORDER BY dt, start_loc, end_loc
SELECT date(start_time) dt,
start_loc,
end_loc,
count(1) cnt,
count(distinct user_id)user_cnt,
count(distinct user_id) / count(1) cnt_per_usr
FROM hello_bike_riding_rcd
WHERE year(start_time) = 2024 AND month(start_time) = 9
GROUP BY date(start_time), start_loc, end_loc
ORDER BY dt, start_loc, end_loc
SELECT hour(start_time) H,
start_loc,
end_loc,
count(*) cnt
FROM hello_bike_riding_rcd
WHERE user_id = 'u802844'
AND start_loc in ('望京南', '方恒购物中心')
AND end_loc in ('望京南', '方恒购物中心')
GROUP BY hour(start_time),
start_loc,
end_loc
ORDER BY H, start_loc
WITH substr AS (SELECT concat(card1, card2) AS card12,
concat(substring(card1, 1, 1),
substring(card2, 1, 1),
CASE
WHEN substring(card1, 1, 1) = substring(card2, 1, 1) THEN ''
WHEN substring(card1, 2, 2) = substring(card2, 2, 2) THEN 's'
ELSE 'o' END
)AS hand
from hand_permutations)
SELECT ss.card12,
IF(pro.hand IS NULL, 0, 1) AS if_garrett
FROM substr ss
LEFT JOIN (SELECT hand
FROM hand_probabilities
WHERE ranking > (SELECT ranking FROM hand_probabilities WHERE hand = '87o')) pro
ON ss.hand = pro.hand;
WITH substr AS (SELECT concat(card1, card2) AS card12,
concat(substring(card1, 1, 1),
substring(card2, 1, 1),
CASE
WHEN substring(card1, 1, 1) = substring(card2, 1, 1) THEN ''
WHEN substring(card1, 2, 2) = substring(card2, 2, 2) THEN 's'
ELSE 'o' END
)AS hand
from hand_permutations)
SELECT card12,
IF(pro.hand IS NULL, 0, 1) AS if_garrett
FROM substr
LEFT JOIN (SELECT hand
FROM hand_probabilities
WHERE ranking > (SELECT ranking FROM hand_probabilities WHERE hand = '87o')) pro
ON substr.hand = pro.hand;
WITH substr AS (
SELECT
CONCAT(card1, card2) AS card12,
SUBSTRING(card1, 1, 1) AS card1,
SUBSTRING(card2, 1, 1) AS card2,
SUBSTRING(card1, 2, 2) AS color1,
SUBSTRING(card2, 2, 2) AS color2
FROM hand_permutations
),
concat_str AS (
SELECT
card12,
CONCAT(
card1, card2,
CASE
WHEN card1 = card2 THEN ''
WHEN color1 = color2 THEN 's'
ELSE 'o'
END
) AS hand
FROM substr
),
rank_threshold AS (
SELECT ranking
FROM hand_probabilities
WHERE hand = '87o'
)
SELECT
cs.card12,
IFNULL(CASE WHEN hp.hand IS NOT NULL THEN 1 ELSE 0 END, 0) AS if_garrett
FROM concat_str cs
LEFT JOIN hand_probabilities hp
ON cs.hand = hp.hand
AND hp.ranking > (SELECT ranking FROM rank_threshold)
WITH substr AS (SELECT concat(card1, card2) AS card12,
substring(card1, 1, 1) AS card1,
substring(card2, 1, 1) AS card2,
substring(card1, 2, 2) AS color1,
substring(card2, 2, 2) AS color2
from hand_permutations),
concat_str AS (SELECT card12,
concat(card1, card2,
CASE
WHEN card1 = card2 THEN ''
WHEN color1 = color2 THEN 's'
ELSE 'o' END
) hand
FROM substr)
SELECT card12,
IF(pro.hand IS NULL, 0, 1) AS if_garrett
FROM concat_str
LEFT JOIN (SELECT hand
FROM hand_probabilities
WHERE ranking > (SELECT ranking FROM hand_probabilities WHERE hand = '87o')) pro
ON concat_str.hand = pro.hand;
WITH substr AS (SELECT concat(card1, card2) AS card12,
substring(card1, 1, 1) AS card1,
substring(card2, 1, 1) AS card2,
substring(card1, 2, 2) AS color1,
substring(card2, 2, 2) AS color2
from hand_permutations),
concat_str AS (SELECT card12,
concat(card1, card2,
CASE
WHEN card1 = card2 THEN ''
WHEN color1 = color2 THEN 's'
ELSE 'o' END
) hand
FROM substr)
SELECT card12,
CASE WHEN pro.hand IS NULL THEN 0 ELSE 1 END AS if_garrett
FROM concat_str
LEFT JOIN (SELECT hand
FROM hand_probabilities
WHERE ranking > (SELECT ranking FROM hand_probabilities WHERE hand = '87o')) pro
ON concat_str.hand = pro.hand;
WITH permu AS (SELECT concat(card1, card2)card12,
substring(card1, 1, 1) AS card1,
substring(card2, 1, 1) AS card2,
CASE
WHEN substring(card1, 1, 1) = substring(card2, 1, 1) THEN ''
WHEN substring(card1, 2, 2) = substring(card2, 2, 2) THEN 's'
ELSE 'o' END AS ec
from hand_permutations),
permu2 AS (SELECT card12, concat(card1, card2, ec) hand FROM permu)
SELECT card12,
CASE WHEN pro.hand IS NULL THEN 0 ELSE 1 END AS if_garrett
FROM permu2
LEFT JOIN (SELECT hand
FROM hand_probabilities
WHERE ranking > (SELECT ranking FROM hand_probabilities WHERE hand = '87o')) pro
ON permu2.hand = pro.hand;
SELECT start_loc,
end_loc,
start_ctg,
end_ctg,
trip_count
FROM (SELECT start_loc,
end_loc,
start_ctg,
end_ctg,
trip_count,
row_number() over (PARTITION BY start_ctg, end_ctg ORDER BY trip_count DESC) rn
FROM (SELECT start_loc,
end_loc,
loc.loc_ctgstart_ctg,
loc2.loc_ctg end_ctg,
count(1) trip_count
FROM didi_sht_rcd rcd
JOIN loc_nm_ctg loc
ON rcd.start_loc = loc.loc_nm
JOIN loc_nm_ctg loc2
ON rcd.end_loc = loc2.loc_nm
WHERE loc.loc_ctg = '酒店'
OR loc2.loc_ctg = '酒店'
GROUP BY start_loc,
end_loc,
loc.loc_ctg,
loc2.loc_ctg
ORDER BY trip_count DESC) t) t2
WHERE t2.rn = 1
ORDER BY trip_count DESC