SQLについて

こんな感じですかね。Redshift環境です。

①
WITH tmp1 AS
( SELECT
     rank
    ,ROW_NUMBER() OVER( PARTITION BY order_n_id ORDER BY application_date) AS row_num
    ,order_n_id
    ,application_date
  FROM table1 
)
SELECT
   t1.rank
  ,t1.order_n_id
  ,t1.application_date
  ,CASE 
     WHEN t1.row_num = 1 THEN 1 
     ELSE DATEDIFF(DAY,t2.application_date,t1.application_date) 
   END AS LT
FROM tmp1 t1
LEFT JOIN tmp1 t2
  ON  t1.order_n_id = t2.order_n_id
  AND t1.row_num = t2.row_num + 1
ORDER BY 1,2;

②
WITH tmp1 AS
( SELECT
     rank
    ,ROW_NUMBER() OVER( PARTITION BY order_n_id ORDER BY application_date) AS row_num
    ,order_n_id
    ,application_date
  FROM table1 
),
tmp2 AS
( SELECT
     t1.rank
    ,t1.order_n_id
    ,t1.application_date
    ,CASE 
       WHEN t1.row_num = 1 THEN 1 
       ELSE DATEDIFF(DAY,t2.application_date,t1.application_date) 
     END AS LT
  FROM tmp1 t1
  LEFT JOIN tmp1 t2
    ON  t1.order_n_id = t2.order_n_id
    AND t1.row_num = t2.row_num + 1
),
tmp3 AS
( SELECT order_n_id,AVG(lt * 1.0) AS lt
  FROM ( SELECT * FROM tmp2 WHERE LT <> 1 AND row_num <> 1)
  GROUP BY order_n_id
)
SELECT
   tmp4.order_n_id
  ,CASE 
     WHEN tmp3.lt IS NULL THEN 1
     ELSE tmp3.lt
   END AS lt
FROM ( SELECT DISTINCT order_n_id FROM tmp1 ) tmp4
LEFT JOIN tmp3
  ON tmp4.order_n_id = tmp3.order_n_id
;

MySQL Dataflowではウインドウ関数が利用できないので、難しいですね。

Magic ETLでランクとウィンドウ処理を利用すると実現できるかもしれません。

お役にたてずすみません。