How to determine first and last dates?

Question

Background:

I have snowpack records (expressed as Snow Water Equivalent) for the past 40+ years for numerous sites in the Pacific Northwest, USA. I need to determine the first and last dates that snow was on the ground for each water year (which extends from October 1 to September 30), for each site.

During the summer, the snowpack is (not surprisingly) zero. As winter approaches, a date will occur when the first snowfall occurs; a non-zero value will then appear in the data for that date. As winter progresses the snowpack increases to a maximum, then decreases throughout the spring back to zero.

I need to find the first and last dates where the snowpack value was non-zero, for all 40+ years, for all sites.

There is a separate Geopackage attribute table (containing all 40+ years of data) for each site, that looks like this:

Water Year Date    Snowpack
1978   1978-10-01  0.0
1978   1978-10-02  0.1
1978   1978-10-03  0.0 => Here, all the snow melted; these are ignored
1978   1978-10-04  0.1
1978   1978-10-05  0.2
1978   1978-10-06  0.4
...
This continues through water year 2021

In the above example, the first date for water year 1978 would be 1978-10-02.

I have been determining these dates manually, by a series of filters and sorts. However, this will become exhausting, so I'm looking for a way to automate.

Question:

For each water year, how do I determine the first and last dates that snow was recorded, without resorting to a manual method?

Bonus for also determining the date with the maximum snowpack value, along with that value!

Answer 1

First and last days in the year with snowpack value:

select x.theyear, x.mindate, max(sn.snowpack) as firstsnowval, y.maxdate, max(sx.snowpack) as lastsnowval
from (
    select theyear,  min(thedate)  as mindate
    from snows
    where snowpack > 0
    group by theyear )  x 
join (
    select theyear,  max(thedate)  as maxdate
    from snows
    where snowpack > 0
    group by theyear )  y on y.theyear = x.theyear
join snows sn on sn.thedate = x.mindate
join snows sx on sx.thedate = y.maxdate
group by x.theyear, x.mindate, y.maxdate
order by x.theyear

Answer 2

In QGIS you can use virtual Layers with SQL for that. The SQL statement should be something like this one:

select max(Date) from your_table group by water_year having snowpack > 0

you should avoid column names with spaces if possible