# Purpose: Find any phenomena+significance combinations which occur more than once in wwa_classification_scheme_id=1 (email alerts)
SELECT
  watm.wwa_phenomena_lut_code,
  watm.wwa_significance_lut_code,
  count(watm.id)
FROM wwa_alert_type_members watm
  INNER JOIN wwa_alert_types wat ON watm.wwa_alert_type_id = wat.id
WHERE
  wat.wwa_classification_scheme_id = 1
GROUP BY
  watm.wwa_phenomena_lut_code, watm.wwa_significance_lut_code
HAVING
  count(watm.id) > 1;

 wwa_phenomena_lut_code | wwa_significance_lut_code | count
------------------------+---------------------------+-------
 MF                     | Y                         |     2
 MS                     | Y                         |     2
 FA                     | Y                         |     2
(3 rows)

# Purpose: Provide more details on when/how these codes are repeated.
SELECT
  watm.wwa_phenomena_lut_code,
  watm.wwa_significance_lut_code,
  wat.description,
  wat.wwa_classification_scheme_id
FROM wwa_alert_type_members watm
  INNER JOIN wwa_alert_types wat ON watm.wwa_alert_type_id = wat.id
WHERE
  (watm.wwa_phenomena_lut_code || watm.wwa_significance_lut_code) IN ('MFY','MSY','FAY')
ORDER BY
  watm.wwa_phenomena_lut_code, watm.wwa_significance_lut_code;

 wwa_phenomena_lut_code | wwa_significance_lut_code |  description   | wwa_classification_scheme_id
------------------------+---------------------------+----------------+------------------------------
 FA                     | Y                         | Flood Warnings |                            1
 FA                     | Y                         | Flood Warnings |                            1
 FA                     | Y                         | Flood          |                            2
 MF                     | Y                         | Fog Advisories |                            1
 MF                     | Y                         | Marine Weather |                            1
 MS                     | Y                         | Fog Advisories |                            1
 MS                     | Y                         | Marine Weather |                            1
(7 rows)