🌱 Laterals

Sometimes you need to have a count or sum on a sub query

Sample data

strat | a,b,c,d
strat2 | c,d
strat3 | a,d,b

SELECT
  attempted,
  count(attempted)
FROM
  LATERAL (
    SELECT
      s.shipment_line_id,
      unnest(string_to_array(attempted_strategies)) AS attempted
    FROM
      substitutions s) AS attempts
GROUP BY
  attempted
ORDER BY
  count(attempted)
  DESC;