<invoke name="query_sql"> <parameter name="sql">WITH p AS (SELECT regexp_extract("Date dd/mm/yyyy", '(\d{1,2})/(\d{1,2})/(\d{4})', 3) AS y, regexp_extract("Date dd/mm/yyyy", '(\d{1,2})/(\d{1,2})/(\d{4})', 2) AS m, TRY_CAST(REPLACE("Amount in USD", ',', '') AS DOUBLE) AS amt FROM raw.startup_funding) SELECT y || '-' || lpad(m, 2, '0') AS month, COUNT(*) AS deals, COUNT(amt) AS deals_with_amount, SUM(amt) AS total_usd, MAX(amt) AS largest_deal_usd FROM p WHERE y <> '' GROUP BY 1 ORDER BY 1</parameter> <parameter name="purpose">Monthly deal counts, total disclosed amounts and largest deal to find spikes, dips and breaks</parameter> <parameter name="source">raw</parameter> </invoke>