I am a newbie when it comes to SQL query so I would really appreciate the help on this. I need to fetch the records of city in which rain has fallen for 3 or more consecutive days in excess of 20 cm . All day records occuring consecutively should be printed.

The output is as follows:

CodePudding user response：

Here is your answer:

SELECT CC.* FROM CityRainfall CC
JOIN (SELECT C.City,MAX(C.DATEOFRAINFALL) AS MAXDATE,MIN(CR.DATEOFRAINFALL) 
      AS MINDATE FROM CityRainfall C
      JOIN CityRainfall CR ON DATEDIFF(DAY,CR.DateOfRainfall,C.DateOfRainfall)=2 
      AND CR.CITY=C.CITY
      WHERE C.Rainfall> 20
      GROUP BY C.CITY) C ON C.City = CC.City AND CC.DateOfRainfall >= MINDATE AND 
CC.DateOfRainfall <=MAXDATE
ORDER BY C.CITY

Let me explain this a little bit more. The inner query is a self joining query looking for Cities that have more than 20 cm of rain. Please notice the self join is only on records that are 2 days consecutive. The Max and Min dates are fetched from the inner query tables, which are used to join with the table again to get the result set.

This answer also assumes that a city has a record for each day since the city was first added to the table.

Let me know if you have difficulty in understanding the answer. I can explain more.

CodePudding user response：

If window functions are allowed.

Calculate a rank per city for the consecutive days in excess of 20 cm.

Then count per city & rank how many they have.

Then filter on that count.

select city_name, rainfall_cm, rainfall_date
from
(
  select *
  , count(*) over (partition by city_name, rnk) as cnt
  from 
  (
    select * 
    --, floor(rainfall_cm/20) as trunc_cm
    , row_number() over (partition by city_name order by rainfall_date desc)
      row_number() over (partition by city_name order by floor(rainfall_cm/20), rainfall_date) as rnk
    from tbl_city_rainfall t
  ) q1
) q2
where cnt >= 3
order by city_name, rainfall_date

city_name | rainfall_cm | rainfall_date
:-------- | ----------: | :------------
Bangalore |          21 | 2021-11-03   
Bangalore |          24 | 2021-11-04   
Bangalore |          21 | 2021-11-05   
Bangalore |          22 | 2021-11-06   
Chennai   |          21 | 2021-11-04   
Chennai   |          22 | 2021-11-05   
Chennai   |          26 | 2021-11-06   

db<>fiddle here