-- Pivots the sim count to use the sim kinds as columns. This is what is called
-- in grafana. If we get more sim kinds, they must be added here, and also in grafana.
--
--      group_name      | lice | sedimentation | transport | virus | watercontact
-- ---------------------+------+---------------+-----------+-------+--------------
--  @aqua-kompetanse.no |    0 |             0 |         0 |     0 |            0
--  @bjoroya.no         |    0 |             0 |         0 |     0 |            0
--  @dnv.com            |    0 |             0 |         0 |     0 |            0
--  @gmail.com          |    0 |             0 |         0 |     0 |            0
--  @leroy.no           |    0 |             0 |         0 |     0 |            0
--  @leroyseafood.com   |    0 |             0 |         0 |     0 |            0
--  @met.no             |    0 |             0 |         0 |     0 |            0
--  @mowi.com           |    0 |             0 |         0 |     0 |            1
--  @mowi.no            |    0 |             0 |         0 |     0 |            0
--  @oceanbox.io        |    2 |             0 |         1 |     5 |            6
--  @oceanpro.no        |    0 |             0 |         0 |     0 |            0
--  @ohshavbruk.no      |    0 |             0 |         0 |     0 |            0
--  @salmar.no          |    0 |             0 |         0 |     0 |            0
--  @scaleaq.com        |    7 |             0 |         0 |     0 |            0
-- (14 rows)

SELECT
  *
FROM
  crosstab(
    'SELECT "group", sim_type, count::text FROM weekly_sim_submit_count WHERE sim_type <> ''lice-network'' AND sim_type <> ''lice-delousing'' ORDER BY 1, 2',
    'SELECT DISTINCT sim_type FROM weekly_sim_submit_count WHERE sim_type <> ''lice-network'' AND sim_type <> ''lice-delousing'' ORDER BY 1'
  ) AS ct (
    group_name text,
    lice bigint,
    sedimentation bigint,
    transport bigint,
    virus bigint,
    watercontact bigint
  )
WHERE
  (lice + sedimentation + transport + virus + watercontact) <> 0
  AND "group" <> '@oceanbox.io'
ORDER BY
  (lice + sedimentation + transport + virus + watercontact) DESC
;