排名

用户解题统计

过去一年提交了

勋章 ①金银铜：在竞赛中获得第一二三名；②好习惯：自然月10天提交；③里程碑：解决1/2/5/10/20/50/100/200题；④每周打卡挑战：完成每周5题，每年1月1日清零。

收藏

收藏日期 题目名称 解决状态
2025-06-26 基于共同兴趣爱好的餐厅推荐（5）-好基友（3）  未解决
2025-06-19 找出所有以酒店为起点的类别组合的最热门路线  未解决
2025-06-01 CASE WHEN-男女学生的数量  未解决

评论笔记

评论日期 题目名称 评论内容 站长评论
2025-06-11 给商品打四类标签（列）  
u1s1 需求处是不是没表达准确
 
看输出示例呢？

提交记录

提交日期 题目名称 提交代码
2025-07-01 基于消费天数和平均消费金额NTILE分组计算每个用户的RFM评分  
with B as (select cust_uid,
 datediff(curdate(),max(trx_dt)) as recency,
 count(distinct trx_dt) as frequency,
 avg(trx_amt) as monetary
from mt_trx_rcd_f 
group by cust_uid
 )
select b.cust_uid,
		ntile(3)over(order by b.recency desc) asrecency_score,
ntile(3)over(order by b.frequency) as frequency_score,
ntile(3)over(order by b.monetary) as monetary_score
from B b
order by cust_uid;
2025-07-01 基于购买次数和累计购买金额计算每个用户的RFM评分  
with B as (select cust_uid,
 datediff(curdate(),max(trx_dt)) as recency,
 count( cust_uid) as frequency,
 sum(trx_amt) as monetary
from mt_trx_rcd_f 
group by cust_uid
 )
select b.cust_uid,
		case 
when b.recency <= 9 then 3
when b.recency between 10 and 30 then 2
else 1 end as recency_score,
case 
when b.frequency between 1 and 5 then 1
when b.frequency between 6 and 15 then 2
else 3 end as frequency_score,
case 
when b.monetary <2000 then 1
when b.monetary between 2000 and 5000 then 2
else 3 end as monetary_score
from B b
order by cust_uid;
2025-07-01 基于消费天数和平均单笔购买金额计算每个用户的RFM评分  
with B as (select cust_uid,
 datediff(curdate(),max(trx_dt)) as recency,
 count(distinct trx_dt) as frequency,
 avg(trx_amt) as monetary
from mt_trx_rcd_f 
group by cust_uid
 )
select b.cust_uid,
		case 
when b.recency <= 9 then 3
when b.recency between 10 and 30 then 2
else 1 end as recency_score,
case 
when b.frequency between 1 and 10 then 1
when b.frequency between 11 and 20 then 2
else 3 end as frequency_score,
case 
when b.monetary <150 then 1
when b.monetary between 150 and 250 then 2
else 3 end as monetary_score
from B b
order by cust_uid;
2025-07-01 基于消费天数和平均单笔购买金额计算每个用户的RFM评分  
with B as (select cust_uid,
 datediff(curdate(),max(trx_dt)) as recency,
 count(distinct trx_dt) as frequency,
 sum(trx_amt) as monetary
from mt_trx_rcd_f 
group by cust_uid
 )
select b.cust_uid,
		case 
when b.recency <= 9 then 3
when b.recency between 10 and 30 then 2
else 1 end as recency_score,
case 
when b.frequency between 1 and 10 then 1
when b.frequency between 11 and 20 then 2
else 3 end as frequency_score,
case 
when b.monetary <150 then 1
when b.monetary between 150 and 250 then 2
else 3 end as monetary_score
from B b
order by cust_uid;
2025-07-01 基于消费天数和平均单笔购买金额计算每个用户的RFM评分  
with B as (select cust_uid,
 datediff(curdate(),max(trx_dt)) as recency,
 count(distinct trx_dt) as frequency,
 sum(trx_amt) as monetary
from mt_trx_rcd_f 
group by cust_uid
 )
select b.cust_uid,
		case 
when b.recency <= 9 then 3
when b.recency between 10 and 30 then 2
else 1 end as recency_score,
case 
when b.frequency between 1 and 10 then 1
when b.frequency between 11 and 20 then 2
else 3 end as frequency_score,
case 
when b.monetary <= 149 then 1
when b.monetary between 150 and 250 then 2
else 3 end as monetary_score
from B b
order by cust_uid;
2025-07-01 基于消费天数和平均单笔购买金额计算每个用户的RFM评分  
with B as (select cust_uid,
 datediff(curdate(),max(trx_dt)) as recency,
 count(distinct trx_dt) as frequency,
 sum(trx_amt) as monetary
from mt_trx_rcd_f 
group by cust_uid
 )
select b.cust_uid,
		case 
when b.recency <= 9 then 3
when b.recency between 10 and 30 then 2
else 1 end as recency_score,
case 
when b.frequency between 1 and 10 then 1
when b.frequency between 11 and 20 then 2
else 3 end as frequency_score,
case 
when b.monetary <= 150 then 1
when b.monetary between 151 and 250 then 2
else 3 end as monetary_score
from B b
order by cust_uid;
2025-07-01 计算每个用户的RFM值（2）  
select cust_uid,
		datediff(curdate(),max(trx_dt)) as recency,
count(distinct trx_dt) as frequency,
avg(trx_amt) as Monetary
from mt_trx_rcd_f 
group by cust_uid
2025-07-01 计算每个用户的RFM值（2）  
select cust_uid,
		datediff(curdate(),max(trx_dt)) as recency,
count(trx_dt) as frequency,
avg(trx_amt) as Monetary
from mt_trx_rcd_f 
group by cust_uid
2025-07-01 计算每个用户的RFM值（2）  
select cust_uid,
		datediff(curdate(),max(trx_dt)) as recency,
count(cust_uid) as frequency,
avg(trx_amt) as Monetary
from mt_trx_rcd_f 
group by cust_uid
2025-07-01 计算每个用户的RFM值（1）  
select cust_uid,
		datediff(curdate(),max(trx_dt)) as recency,
count(cust_uid) as frequency,
sum(trx_amt) as Monetary
from mt_trx_rcd_f 
group by cust_uid
2025-07-01 直观对比两种频率计算的差异（F）  
WITH TransactionFrequency AS (
    SELECT cust_uid, COUNT(*) AS transaction_count
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
ActiveDaysFrequency AS (
    SELECT cust_uid, COUNT(DISTINCT DATE(trx_dt)) AS active_days_count
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
RankedTransactionFrequency AS (
    SELECT cust_uid, 
           transaction_count,
           CAST(RANK() OVER (ORDER BY transaction_count DESC) AS SIGNED) AS transaction_rank
    FROM TransactionFrequency
),
RankedActiveDaysFrequency AS (
    SELECT cust_uid, 
           active_days_count,
           CAST(RANK() OVER (ORDER BY active_days_count DESC) AS SIGNED) AS active_days_rank
    FROM ActiveDaysFrequency
)
SELECT 
    t.cust_uid,
    t.transaction_count,
    t.transaction_rank,
    a.active_days_count,
    a.active_days_rank,
    ABS(t.transaction_rank - a.active_days_rank) AS rank_difference
FROM RankedTransactionFrequency t
JOIN RankedActiveDaysFrequency a ON t.cust_uid = a.cust_uid
ORDER BY rank_difference DESC;
2025-07-01 计算每个用户的购买频率-天数（F）  
select cust_uid, count(distinct trx_dt) as ts
from mt_trx_rcd_f 
group by cust_uid
order by 2 desc;
2025-07-01 计算每个用户的购买频率-次数（F）  
select cust_uid,count(cust_uid) as cs
from mt_trx_rcd_f 
group by cust_uid
order by 2 desc;
2025-07-01 计算每个用户平均单笔消费金额（M）  
select cust_uid, cast(avg(trx_amt) as decimal(6,2) )as avg_trx_amt
from mt_trx_rcd_f
group by cust_uid
order by avg(trx_amt) desc
2025-07-01 计算每个用户平均单笔消费金额（M）  
select cust_uid, avg(trx_amt) as avg_trx_amt
from mt_trx_rcd_f
group by cust_uid
order by avg(trx_amt) desc
2025-07-01 计算每个用户平均单笔消费金额（M）  
select cust_uid, avg(trx_amt) as avg_trx_amt
from mt_trx_rcd_f
group by cust_uid
order by avg(trx_dt) desc
2025-07-01 计算每个用户平均单笔消费金额（M）  
select cust_uid, avg(trx_dt) as avg_trx_dt
from mt_trx_rcd_f
group by cust_uid
order by avg(trx_dt) desc
2025-07-01 计算每个用户总消费金额（M）  
WITH TotalSpending AS (
    SELECT cust_uid, SUM(trx_amt) AS total_amount
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
)
SELECT * 
FROM TotalSpending
ORDER BY total_amount DESC;
2025-07-01 计算每个用户总消费金额（M）  
select cust_uid,sum(trx_dt) as Monetary
from mt_trx_rcd_f 
group by cust_uid
order by Monetary desc ;
2025-07-01 计算每个用户最近一次购买日期（R）  
select cust_uid,max(trx_dt) as recent_trx_dt
from mt_trx_rcd_f 
group by cust_uid
order by cust_uid