in Bigquery: Classify the weather for a day when two (or more) weather types (fx sun and rain) occurres on the same day

Question

I have a table with weather data as below. I wish to classify days as being either:

• "sunny" if the value is >0 (hours of sun),
• "rainy" if the value is >0 (mm precipitation)
• or both ("sun and rain") if there is >0 hours and 0> mm rain on the same day

date	element	value	unit
29.11.2021	precipitation	2,4	mm
29.11.2021	sun	0,2	hours
30.11.2021	precipitation	3,7	mm
30.11.2021	sun	4	hours
31.11.2021	sun	2	hours
31.11.2021	precipitation	0	mm

I've used CASE for classifying a day witn ONLY sun or ONLY rain like this:

case when element= 'sun' and value>0 then "sun" 
     when element= 'precipitation' and value>0 then "rain"

However, if a day has BOTH rain and sun I am not sure how to achieve this. I tried:

 case when element= 'precipitation' and value>0  
       and element= 'sun' and value>0 
 then "rain/sun" end as weather

but this will not give me any result, probably because i need to somehow partition by date (?). The outcome Im looking for is something like a classic weather report:

date	element
29.11.2021	sun and rain
30.11.2021	sun and rain
31.11.2021	sun

Answer 1

You may change precipitation to say rain as you please, but here is the general idea of a solution using string_agg

with cte as

(select date, element
 from t
 group by date, element
 having sum(value) > 0)

select date, string_agg(element,' and ' order by element) as element
from cte
group by date;