题解 | #某乎问答最大连续回答问题天数大于等于3天的用户及其对应等级#

SELECT '2021-10-01', ROUND( count( SUBSTRING( start_time, 1, 10 ))/ 7, 2 ), round( sum( IF ( start_time, 0, 1 ))/ 7, 2 ) FROM tb_get_car_record a JOIN tb_get_car_order b ON a.order_id = b.order_id WHERE DATEDIFF( '2021-10-01', event_time )<= 6 AND DATEDIFF( '2021-10-01', event_time )>= 0 UNION ALL SELECT '2021-10-02', ROUND( count( SUBSTRING( start_time, 1, 10 ))/ 7, 2 ), round( sum( IF ( start_time, 0, 1 ))/ 7, 2 ) FROM tb_get_car_record a JOIN tb_get_car_order b ON a.order_id = b.order_id WHERE DATEDIFF( '2021-10-02', event_time )<= 6 AND DATEDIFF( '2021-10-02', event_time )>= 0 UNION ALL SELECT '2021-10-03', ROUND( count( SUBSTRING( start_time, 1, 10 ))/ 7, 2 ), round( sum( IF ( start_time, 0, 1 ))/ 7, 2 ) FROM tb_get_car_record a JOIN tb_get_car_order b ON a.order_id = b.order_id WHERE DATEDIFF( '2021-10-03', event_time )<= 6 AND DATEDIFF( '2021-10-03', event_time )>= 0