How to classify rows having one or more related records

I need to add the additional column called rank_2 which will show whether the source has more than 1 source_ids.

So, I need to get this result:

I don’t know how to write a query to get rank_2, which will contain 1 when the source has the only source_id (like the source “b”) and contain 2 when the source has 2 or more source_ids (like the source “a”).

Answer

Assuming window functions are available you can use a query like so:

select *
     , case when count(*) over (partition by date, source_name) = 1 then 1 else 2 end as rank_2
from t