Untitled

From Gamboge Earthworm, 10 Months ago, written in Plain Text, viewed 99 times.
URL http://codebin.org/view/70b581f6 Embed
Download Paste or View Raw
  1. WITH
  2. f AS (SELECT name,
  3.               id
  4.       FROM fund
  5.       WHERE country_code = 'USA'),
  6. a AS (SELECT acquired_company_id,
  7.               price_amount
  8.       FROM acquisition)
  9.  
  10. SELECT EXTRACT(MONTH FROM fr.funded_at) AS month,
  11.        COUNT(DISTINCT f.name) AS fund_count,
  12.        COUNT(a.acquired_company_id) AS company_count,
  13.        SUM(a.price_amount) AS amount
  14. FROM funding_round AS fr
  15. LEFT JOIN investment AS i ON fr.id = i.funding_round_id
  16. LEFT JOIN f ON f.id = i.fund_id
  17. LEFT JOIN company AS c ON c.id = fr.company_id
  18. LEFT JOIN a ON a.acquired_company_id = c.id
  19. WHERE EXTRACT(YEAR FROM fr.funded_at) BETWEEN 2010 AND 2013
  20.       AND fr.raised_amount > 0
  21. GROUP BY month
  22. ORDER BY month

Reply to "Untitled"

Here you can reply to the paste above