- WITH
- fr AS (SELECT company_id,
- SUM(raised_amount) AS amount
- FROM funding_round
- GROUP BY company_id
- HAVING SUM(raised_amount) != 0),
- a AS (SELECT *
- FROM acquisition
- WHERE price_amount != 0)
- SELECT c_1.name AS acquiring_company,
- a.price_amount AS price,
- c_2.name AS acquired_company,
- fr.amount AS amount,
- round(a.price_amount / a.acquired_company_id) AS part
- FROM a LEFT OUTER JOIN company AS c_1 ON a.acquiring_company_id = c_1.id
- LEFT OUTER JOIN company AS c_2 ON a.acquired_company_id = c_2.id
- INNER JOIN fr ON c_2.id = fr.company_id
- ORDER BY a.price_amount DESC, c_2.name ASC
- LIMIT 10;