SELECT
t.login_date,
COUNT(DISTINCT CASE
WHEN DATEDIFF(t.login_date, t.first_date) = 29 THEN l.usr_id
ELSE NULL
END) / COUNT(DISTINCT l.usr_id) AS T1_retention_rate
FROM user_login_log l
LEFT JOIN (
SELECT
usr_id,
MIN(login_time) AS first_date,
DATE_FORMAT(MIN(login_time), '%Y-%m-%d') AS login_date
FROM user_login_log
GROUP BY usr_id
) t
ON t.usr_id = l.usr_id
GROUP BY t.login_date;
select p.prd_id,p.prd_nm,
sum(if(r.if_snd=1,1,0)) as exposure_count
from tb_pg_act_rcd r
left join tb_prd_map p
on r.prd_id=p.prd_id
group by p.prd_id,p.prd_nm
order by exposure_count desc
limit 1;
SELECT
distinct gd.gd_id,
gd.gd_nm,
COUNT(fav.mch_id) AS fav_count
FROM
xhs_fav_rcd fav
left JOIN
gd_inf gd on fav.mch_id=gd.gd_id
GROUP BY
gd.gd_id, gd.gd_nm
ORDER BY
fav_count desc
LIMIT 1
SELECT
distinct gd.gd_id,
gd.gd_nm,
COUNT(fav.mch_id) AS fav_count
FROM
xhs_fav_rcd fav
left JOIN
gd_inf gd on fav.mch_id=gd.gd_id
GROUP BY
gd.gd_id, gd.gd_nm
ORDER BY
fav_count
LIMIT 1
SELECT
distinct gd.gd_id,
gd.gd_nm,
COUNT(fav.mch_id) AS fav_count
FROM
xhs_fav_rcd fav
JOIN
gd_inf gd
GROUP BY
gd.gd_id, gd.gd_nm
ORDER BY
fav_count
LIMIT 1
select merchant_name,
CASE
WHEN merchant_name LIKE '%小红书%' OR merchant_name LIKE '%行吟信息科技' THEN '小红书'
WHEN merchant_name LIKE '%拼多多%' OR merchant_name LIKE '%上海寻梦信息技术%'THEN '拼多多'
WHEN merchant_name LIKE '%京东%' OR merchant_name LIKE '%网银在线%' OR merchant_name LIKE '%京东平台商户%' THEN '京东'
WHEN merchant_name LIKE '%抖音生活服务商家%' OR merchant_name LIKE '%合众易宝%' OR merchant_name LIKE '%格物致品网络科技%' OR merchant_name LIKE '%成都所见所得科技%' OR merchant_name LIKE '%北京空间变换科技%' THEN '抖音'
WHEN merchant_name LIKE '%淘宝%' OR merchant_name LIKE '%天猫%' OR merchant_name LIKE '%杭州今日卖场%' THEN '淘系'
ELSE '其他'end as platform
from (select distinct mch_nm as merchant_name from ccb_trx_rcd) t
select *
from hand_permutations
where
concat(card1, card2) like '%A%A%' or
concat(card2, card1) like '%A%K%' or
concat(card1, card2) like '%K%K%' or
concat(card1, card2) like '%K%A%'
order by id;
select
sum(case when right(card1,1)=right(card2,1) then 1 else 0 end)/2 as cnt
, count(1)/2 as ttl_cnt
,cast(sum(case when right(card1,1)=right(card2,1) then 1 else 0 end)/2/(count(1)/2) AS DECIMAL(4,3)) as p
from hand_permutations
select
sum(case when right(card1,1)=right(card2,1) then 1 else 0 end)/2 as cnt
, count(1)/2 as ttl_cnt
,cast(sum(case when right(card1,1)=right(card2,1) then 1 else 0 end)/2/count(1)/2 AS DECIMAL(4,4)) as p
from hand_permutations
select
round(sum(case when right(card1,1)=right(card2,1) then 1 else 0 end)/2,0) as cnt
, round(count(1)/2,0) as ttl_cnt
,round((sum(case when right(card1,1)=right(card2,1) then 1 else 0 end)/2)/(count(1)/2),3) as p
from hand_permutations
select
round(sum(case when left(card1,1)=left(card2,1) then 1 else 0 end)/2,0) as cnt
, round(count(1)/2,0) as ttl_cnt
,round((sum(case when left(card1,1)=left(card2,1) then 1 else 0 end)/2)/(count(1)/2),3) as p
from hand_permutations
select
round(sum(case when right(card1,1)=right(card2,1) then 1 else 0 end)/2,1) as cnt
, round(count(1)/2,1) as ttl_cnt
,round((sum(case when right(card1,1)=right(card2,1) then 1 else 0 end)/2)/(count(1)/2),1) as p
from hand_permutations
select
round(sum(case when right(card1,1)=right(card2,1) then 1 else 0 end)/2,0) as cnt
, round(count(1)/2,0) as ttl_cnt
,round((sum(case when right(card1,1)=right(card2,1) then 1 else 0 end)/2)/(count(1)/2),1) as p
from hand_permutations
select
round(sum(case when right(card1,1)=right(card2,1) then 1 else 0 end)/2,0) as cnt
, round(count(1)/2,0) as ttl_cnt
,round(sum(case when right(card1,1)=right(card2,1) then 1 else 0 end)/2/count(1)/2,3) as p
from hand_permutations
select
sum(case when right(card1,1)=right(card2,1) then 1 else 0 end)/2 as cnt
, count(1)/2 as ttl_cnt
,sum(case when right(card1,1)=right(card2,1) then 1 else 0 end)/2/count(1)/2 as p
from hand_permutations
