This extends SQL analytic functions interview questions – Part 2 and SQL analytic functions interview questions – Part 1. The below example was created in IMPALA SQL engine with Common Table Expression (i.e CTE).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
WITH WORK_ORDER AS ( SELECT 101 as order_id, TO_DATE('2020-01-01') as ORDER_DATE, TO_DATE('2020-01-15') as COMPLETED_DATE UNION ALL SELECT 102 as order_id, TO_DATE('2020-06-01') as ORDER_DATE, TO_DATE('2020-06-08') as COMPLETED_DATE UNION ALL SELECT 103 as order_id, TO_DATE('2020-08-15') as ORDER_DATE, TO_DATE('2020-09-12') as COMPLETED_DATE UNION ALL SELECT 104 as order_id, TO_DATE('2020-09-13') as ORDER_DATE, TO_DATE('2020-09-18') as COMPLETED_DATE UNION ALL SELECT 105 as order_id, TO_DATE('2020-11-11') as ORDER_DATE, TO_DATE('2020-12-07') as COMPLETED_DATE UNION ALL SELECT 106 as order_id, TO_DATE('2021-02-13') as ORDER_DATE, TO_DATE('2021-02-28') as COMPLETED_DATE UNION ALL SELECT 107 as order_id, TO_DATE('2021-05-01') as ORDER_DATE, TO_DATE('2021-05-16') as COMPLETED_DATE ) SELECT order_id, DATEDIFF(COMPLETED_DATE, ORDER_DATE) AS COMPLETION_DAYS FROM WORK_ORDER ORDER BY order_id ; |
Output:
|
1 2 3 4 5 6 7 8 9 10 |
order_id completion_days ------------------------------ 101 14 102 7 103 28 104 5 105 26 106 15 107 15 |
CUME_DIST() analytic function…
(Visited 1 times, 1 visits today)