SELECT
M.us user_grade,
round( count( M.us ) / ( SELECT count( DISTINCT uid ) FROM tb_user_log ), 2 ) ratio
FROM
(
SELECT
(
CASE
WHEN timestampdiff( DAY, N.mada, N.md ) >= 7
AND timestampdiff( DAY, N.mida, N.md ) < 30 THEN
"沉睡用户"
WHEN timestampdiff( DAY, N.mida, N.md ) < 7 THEN "新晋用户" WHEN timestampdiff( DAY, N.mada, N.md ) >= 30 THEN
"流失用户"
WHEN timestampdiff( DAY, N.mada, N.md ) < 7 THEN
"忠实用户"
END
) us
FROM
(
SELECT
( SELECT date_format( max( in_time ), "%Y-%m-%d" ) FROM tb_user_log ) md,
date_format( min( tul.in_time ), "%Y-%m-%d" ) mida,
date_format( max( tul.in_time ), "%Y-%***da,
tul.uid
FROM
tb_user_log tul
GROUP BY
tul.uid
) N
) M
GROUP BY
M.us
order by ratio desc