Untitled

From Perl Butterfly, 10 Months ago, written in Plain Text, viewed 168 times.
URL http://codebin.org/view/06a871ab 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.              acquired_at
  9.       FROM acquisition)
  10.  
  11. SELECT EXTRACT(MONTH FROM fr.funded_at) AS month,
  12.        COUNT(DISTINCT f.name) AS fund_count,
  13.        COUNT(DISTINCT a.acquired_company_id) AS company_count,
  14.        SUM(a.price_amount) AS amount
  15. FROM funding_round AS fr
  16. LEFT JOIN investment AS i ON fr.id = i.funding_round_id
  17. LEFT JOIN f ON f.id = i.fund_id
  18. LEFT JOIN company AS c ON c.id = fr.company_id
  19. LEFT JOIN a ON a.acquired_company_id = c.id
  20. WHERE EXTRACT(YEAR FROM fr.funded_at) BETWEEN 2010 AND 2013
  21.       AND fr.raised_amount > 0
  22. GROUP BY month

Reply to "Untitled"

Here you can reply to the paste above