回答思路

select first_data as '日期' count(distinct l.user_id) as '新增用户数', round( count(distinct case when datediff(log_date,first_date) = 1 then l.user_id else null end) / count(distinct l.user_id) , 2 ) as '次日留存率', round( count(distinct case when datediff(log_date,first_date) = 29 then l.user_id else null end) / count(distinct l.user_id) , 2 ) as '30日留存率', from user_log l left join ( select user_id,min(log_date) as first_date from user_log group by user_id )t on l.user_id = t.user_id group by dirst_date