propagate_up.rb

query = <<-SQL
      SELECT c.id, array_agg(ind_t) AS top_industries, sum(ind.cnt) as ccnt
      FROM
        competitors c,
        LATERAL (
          SELECT t.ind_t, count(*) as cnt
          FROM (
            SELECT unnest(industry) as ind_t
            FROM companies
          ) t
          INNER JOIN companies_competitors cc ON cc.competitor_id = c.id
          INNER JOIN companies co ON co.id = cc.company_id
          GROUP BY t.ind_t
          ORDER BY count(*) DESC
        ) ind
      GROUP BY c.id
      LIMIT 5;
SQL
Competitor.connection.execute query