#22/23

From Sweltering Matamata, 10 Months ago, written in Plain Text, viewed 233 times. This paste is a reply to Re: Re: Untitled from Social Curlew - view diff
URL http://codebin.org/view/692e88d0 Embed
Download Paste or View Raw
  1. WITH
  2. tab1 AS (SELECT EXTRACT(MONTH FROM CAST(funded_at AS timestamp)) AS month1,
  3.                 COUNT (DISTINCT fund.name) AS funds
  4.          FROM  funding_round AS fr
  5.          LEFT OUTER JOIN investment AS i ON fr.id = i.funding_round_id
  6.          LEFT OUTER JOIN fund ON i.company_id = fund.id
  7.          WHERE EXTRACT(YEAR FROM CAST(funded_at AS timestamp)) BETWEEN 2010 AND 2013
  8.          AND fund.country_code='USA'
  9.          GROUP BY month1),
  10.      
  11.      
  12. tab2 AS (SELECT EXTRACT(MONTH FROM CAST(acquired_at AS timestamp)) AS month2,
  13.                COUNT(acquired_company_id) AS acquired,
  14.                SUM(price_amount) AS summ
  15.         FROM acquisition
  16.         WHERE EXTRACT(YEAR FROM CAST(acquired_at AS date)) BETWEEN 2010 AND 2013
  17.         GROUP BY month2)
  18.        
  19. SELECT month1,
  20.        funds,
  21.        acquired,
  22.        summ
  23. FROM tab1 JOIN tab2 ON tab1.month1 = tab2.month2
  24. ORDER BY month1;

Replies to #22/23 rss

Title Name Language When
Re: #23/23 Mammoth Human text 10 Months ago.

Reply to "#22/23"

Here you can reply to the paste above