0
Follow
0
View

get lead value based on rankings

daipeiai 注册会员
2023-01-24 22:47

Your sample is too small to verify the query logic. Depending on the real data, below query may not work as expected. But hope this will be helpful for you to approach your problem.

-- Sample Table
CREATE TEMP TABLE sample (
  employee STRING,
  col_date STRING,
  job STRING,
  col_a STRING,
  col_a_date STRING,
) AS
SELECT * FROM UNNEST([
 ('john smith', '2022-05-12', 'a', 'abc', '2022-05-12'),
 ('john smith', '2022-05-12', 'b', 'def', '2022-05-12'),
 ('john smith', '2022-03-27', 'a', 'ghi', '2022-03-27'),
 ('john smith', '2021-09-21', 'a', 'abc', '2021-09-21')
]);

-- Main Query
SELECT * EXCEPT(dense_rankings), 
       IF(rankings = MAX(rankings) OVER (PARTITION BY employee), NULL, LAST_VALUE(col_a) OVER w) AS prev_col_a 
  FROM (
    SELECT RANK() OVER w rankings, DENSE_RANK() OVER w dense_rankings, *
      FROM sample
    WINDOW w AS (PARTITION BY employee ORDER BY col_a_date DESC)
  )
WINDOW w AS (PARTITION BY employee ORDER BY dense_rankings RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING)
 ORDER BY rankings, col_a_date DESC
;

output: