-- ## 12: Tickets by Reporter ## --

  SELECT reporter, 
    Count(*) AS total, 
    Sum(CASE WHEN status = 'closed' THEN 0 ELSE 1 END) AS opened, 
    Sum(CASE WHEN status = 'closed' THEN 1 ELSE 0 END) AS closed, 
    Round(Avg(CASE WHEN status = 'closed' THEN 0 ELSE 1 END), 2) AS "ratio opened"
  FROM ticket
  WHERE resolution <> 'invalid' OR resolution IS NULL
  GROUP BY reporter
  ORDER BY total DESC