Filter Groups By Column Value

We wish to apply the row filtering logic to each group separately where a group is defined by a given column.

In this example, we have a table table_1 that is grouped by the column col_10 and we wish to filter rows where the value of the column col_1 is greater than the value of the mean of col_1 for the group.

WITH grouped_table AS
         (SELECT col_10, AVG(col_1) col_1_avg
          FROM refcon.dataset.table_1
          GROUP BY col_10)

SELECT t.*
FROM refcon.dataset.table_1 t
         INNER JOIN grouped_table g ON g.col_10 = t.col_10
WHERE t.col_1 > col_1_avg;

Here is how this works:

  • We calculate the mean of col_1 per group of col_10 in grouped_table CTE.
  • We simply group by col_10 and use AVG(col_1) to calculate the mean.
  • We join the grouped table with the original table on col_10 using an INNER JOIN.
  • Rows for which the value of col_1 is greater than the mean value of col_1 for the group are retained (included in the output).
SQL
I/O