We want to write below SQL query with some optimized way like window function and don’t want to do self join, we are using BQ and in BQ self joins are not recommended so want to achieve same output with window function,

SELECT DISTINCT c.col1, c.col2
FROM table m
JOIN table c ON c.col_id = m.parent_col_id