WITH daily_unique_logins AS (
SELECT
usr_id,
DATE(login_time) AS login_date
FROM
user_login_log
WHERE
login_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY
usr_id,
DATE(login_time)
),
retention_days AS (
SELECT
dul1.usr_id,
dul1.login_date AS first_login_date,
dul2.login_date AS next_day_login_date,
DATEDIFF(dul2.login_date, dul1.login_date) AS days_diff
FROM
daily_unique_logins dul1
LEFT JOIN
daily_unique_logins dul2
ON
dul1.usr_id = dul2.usr_id AND
dul2.login_date BETWEEN dul1.login_date + INTERVAL 1 DAY AND dul1.login_date + INTERVAL 14 DAY
)
SELECT
first_login_date,
ROUND(COUNT(DISTINCT CASE WHEN days_diff = 1 THEN usr_id END) * 100.0 / COUNT(DISTINCT usr_id), 2) AS t_plus_1_retention_rate,
ROUND(COUNT(DISTINCT CASE WHEN days_diff = 3 THEN usr_id END) * 100.0 / COUNT(DISTINCT usr_id), 2) AS t_plus_3_retention_rate,
ROUND(COUNT(DISTINCT CASE WHEN days_diff = 7 THEN usr_id END) * 100.0 / COUNT(DISTINCT usr_id), 2) AS t_plus_7_retention_rate,
ROUND(COUNT(DISTINCT CASE WHEN days_diff = 14 THEN usr_id END) * 100.0 / COUNT(DISTINCT usr_id), 2) AS t_plus_14_retention_rate
FROM
retention_days
WHERE
first_login_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY
first_login_date
ORDER BY
first_login_date;
select *
from (select date(order_time) order_date
, goods_id
, sum(order_gmv) total_gmv
, row_number() over(partition by date(order_time) order by sum(order_gmv) asc) ranking
from order_info
where substr(order_time,1,7) = '2024-10'
group by date(order_time), goods_id
order by order_date,ranking) a
where ranking <= 3
select *
from (select date (order_time) order_date
, goods_id
, sum(order_gmv) total_gmv
, row_number() over(partition by date(order_time) order by sum(order_gmv) asc) ranking
from order_info
group by date (order_time), goods_id
order by order_date,ranking) a
where ranking <= 3
select *
from
(
select date(order_time) order_date
,goods_id
,sum(order_gmv) total_gmv
,row_number() over(partition by date(order_time) order by sum(order_gmv)) ranking
from order_info
group by date(order_time),goods_id
order by order_date) a
where ranking <= 3
select *
from
(
select goods_id,sum(order_gmv) total_gmv,rank() over(order by sum(order_gmv) desc) ranking
from order_info
where substr(order_time,1,10) = '2024-09-10'
group by goods_id
order by total_gmv desc
) a
where ranking <= 10
select *
from
(
select goods_id,sum(order_gmv) total_gmv,rank() over(order by sum(order_gmv) desc) ranking
from order_info
where substr(order_time,1,10) = '2024-09-10'
group by goods_id
order by total_gmv desc
) a
limit 10
select *
from
(
select goods_id order_id,sum(order_gmv) total_gmv,rank() over(order by sum(order_gmv) desc) ranking
from order_info
where substr(order_time,1,10) = '2024-09-10'
group by goods_id
order by total_gmv desc
) a
limit 10
select *
from
(
select goods_id order_id,sum(order_gmv) total_gmv,rank() over(order by sum(order_gmv) desc) ranking
from order_info
where substr(order_time,1,10) = '2024-09-10'
group by goods_id
order by total_gmv desc
) a
where ranking <= 10
select d.* from didi_sht_rcd d
inner join loc_nm_ctg l
on d.start_loc = l.loc_nm
inner join loc_nm_ctg lo
on d.end_loc = lo.loc_nm
where l.loc_ctg = '餐饮'
and lo.loc_ctg = '餐饮'
order by start_tm
select * from cmb_usr_trx_rcd where date(trx_time) between '2024-09-01' and '2024-09-30'
and hour(trx_time) between 1 and 5
and usr_id='5201314520'
order by trx_time
select *
from hand_permutations
where
concat(card1, card2) like '%A%A%' or
concat(card1, card2) like '%A%K%' or
concat(card1, card2) like '%K%K%' or
concat(card1, card2) like '%K%A%'
order by id;
select *
from hand_permutations
where
concat(card1, card2) like '%A%A%' or
concat(card1, card2) like '%A%K%' or
concat(card1, card2) like '%K%K%' or
concat(card1, card2) like '%K%A%' or
concat(card1, card2) like '%Q%Q%'
order by id;
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 '%A%K%' or
concat(card1, card2) like '%Q%Q%'
order by id;