一段优雅的代码

今天看了xp的一段代码，叹为观止，

SELECT t.ds '日期',
  t.finish_cnt '当日完单用户数',
  t.finish_cnt_1/t.finish_cnt as '次1日完单率',
  t.finish_cnt_2/t.finish_cnt as '次2日完单率',
  t.finish_cnt_3/t.finish_cnt as '次3日完单率',
  t.finish_cnt_4/t.finish_cnt as '次4日完单率',
  t.finish_cnt_5/t.finish_cnt as '次5日完单率',
  t.finish_cnt_6/t.finish_cnt as '次6日完单率',
  t.finish_cnt_7/t.finish_cnt as '次7日完单率',
  t.finish_cnt_15/t.finish_cnt as '次15日完单率',
  t.finish_cnt_30/t.finish_cnt as '次30日完单率'
FROM (SELECT a2.finish_time as ds
    ,count( DISTINCT a2.passenger_id) as finish_cnt
    ,count(DISTINCT case when date_add(a1.create_route_time,0) = date_add(a2.finish_time,1) then a1.passenger_id else null end ) as finish_cnt_1
    ,count(DISTINCT case when date_add(a1.create_route_time,0) = date_add(a2.finish_time,2) then a1.passenger_id else null end ) as finish_cnt_2
    ,count(DISTINCT case when date_add(a1.create_route_time,0) = date_add(a2.finish_time,3) then a1.passenger_id else null end ) finish_cnt_3
    ,count( DISTINCT case when date_add(a1.create_route_time,0) = date_add(a2.finish_time,4) then a1.passenger_id else null end ) finish_cnt_4
    ,count(DISTINCT case when date_add(a1.create_route_time,0) = date_add(a2.finish_time,5) then a1.passenger_id else null end ) finish_cnt_5
    ,count(DISTINCT case when date_add(a1.create_route_time,0) = date_add(a2.finish_time,6) then a1.passenger_id else null end ) finish_cnt_6
    ,count(DISTINCT case when date_add(a1.create_route_time,0) = date_add(a2.finish_time,7) then a1.passenger_id else null end ) finish_cnt_7
    ,count(DISTINCT case when date_add(a1.create_route_time,0) = date_add(a2.finish_time,15) then a1.passenger_id else null end ) finish_cnt_15
    ,count(DISTINCT case when date_add(a1.create_route_time,0) = date_add(a2.finish_time,30) then a1.passenger_id else null end ) finish_cnt_30
  FROM (SELECT DISTINCT substr(create_route_time,1,10) create_route_time 
      ,passenger_id
    FROM dws_ord_route_p_detail_fact_ds 
    WHERE settlement_time is NOT NULL
      AND create_route_time >= '2020-07-17 00:00:00'
    ) a1 ---复购时间
  LEFT JOIN
    (SELECT DISTINCT substr(create_route_time,1,10) finish_time 
      ,passenger_id
    FROM dws_ord_route_p_detail_fact_ds 
    WHERE settlement_time is NOT NULL
      AND create_route_time >= '2020-07-17 00:00:00'
    ) a2 ---完单时间
        ON  a1.passenger_id = a2.passenger_id 
  GROUP BY a2.finish_time
  ) t