Query Min, Max and associated columns data from the same rows

I have query in which returns averaged values by site and month. What I want to know is the min and max of those monthly averages by site and (the difficult part) the month when each occurred.

Here is an example:

SQL Fiddle Stuff here

CREATE TABLE events (
    esite     integer NOT NULL,
    edate     timestamp with time zone NOT NULL,
    evalue    integer NOT NULL
);

INSERT INTO events Values
    (1, '2016-01-03', 11),
    (2, '2016-01-05', 90),
    (1, '2016-01-08', 7),
    (2, '2016-01-10', 40),
    (1, '2016-01-15', 12),
    (1, '2016-01-18', 66),
    (2, '2016-01-22', 54),
    (2, '2016-02-03', 70),
    (2, '2016-02-05', 56),
    (1, '2016-02-08', 61),
    (2, '2016-02-10', 23),
    (1, '2016-02-15', 30),
    (1, '2016-02-18', 15),
    (1, '2016-02-22', 41);

I'm looking for a query that returns (by site) the min and max monthly average evalue and the months at which the min and max values occurred. I can get this by using the query below:

SQL Fiddle this

select esite, date_trunc('month', edate) as emonth, round(avg(evalue),2) as evalue_avg from events
  Group by esite, emonth

Produces the following output:

esite | emonth                      | evalue_avg
2     | January, 01 2016 00:00:00   | 61.33
1     | January, 01 2016 00:00:00   | 24
1     | February, 01 2016 00:00:00  | 36.75
2     | February, 01 2016 00:00:00  | 49.67

Now for the part I'm having difficultly with, I need produce the following result - basically the min, max values and the date(month) at which each occurred by site.

Desired Output:

-- (one row per site)
esite | avg_min | eval_avg_min_date          | avg_max | eval_avg_max
1     | 24.00   | January, 01 2016 00:00:00  | 36.75   | February, 01 2016 00:00:00
2     | 49.67   |February, 01 2016 00:00:00  | 61.33   | January, 01 2016 00:00:00

I've searched around and have seen some examples using windowing and lateral joins, but I haven't been successful in getting any of them to work. This might b

The question is old, but the CommunityBot keeps bumping it. Let's add a proper answer.

Move your avg calculation into a CTE, and use the results in two joined DISTINCT ON queries:

WITH cte AS (
   SELECT esite, date_trunc('month', edate) AS emonth, round(avg(evalue), 2) AS evalue_avg
   FROM   events
   GROUP  BY esite, emonth
   )
SELECT *
FROM  (
   SELECT DISTINCT ON (esite) 
          esite, evalue_avg, emonth
   FROM   cte
   ORDER  BY esite, evalue_avg, emonth
   ) a
JOIN  (
   SELECT DISTINCT ON (esite) 
          esite, evalue_avg, emonth
   FROM   cte
   ORDER  BY esite, evalue_avg DESC, emonth
   ) z USING (esite);

db<>fiddle here

(Would even work in the outdated Postgres 9.4.)

See:

• Select first row in each GROUP BY group?

• Get values from first and last row per group

StackExchange Database Administrators Q#138677, answer score: 2