total investors by selected target companies

select target_name, count(distinct investor_name) from (#Venture capital investment
WITH
  # table with basic company information
  comp_stage AS (
  SELECT
    * EXCEPT(short_description,
      description),
    CONCAT(short_description,' ', description) AS description
  FROM (
    SELECT
      uuid,
      name AS target_name,
      cb_url,
      country_code AS target_country,
      short_description,
      category_list,
      category_groups_list
    FROM
      gcp_cset_crunchbase.organizations )
  LEFT JOIN (
    SELECT
      uuid,
      description
    FROM
      gcp_cset_crunchbase.organization_descriptions)
  USING
    (uuid) ),
  comp AS ( # add CSET AI orgs
  SELECT
    *
  FROM
    comp_stage
  LEFT JOIN (
    SELECT
      CSET_id,
      crunchbase.crunchbase_uuid AS uuid
    FROM
      high_resolution_entities.organizations )
  USING
    (uuid) ),
  add_ipo_tab AS (
  SELECT
    uuid,
    target_name,
    cb_url,
    target_country,
    category_list,
    category_groups_list,
  IF
    (ipo IS NULL,
      0,
      ipo) AS ipo,
    shared_functions.isAICompany(description) AS ai_company,
  IF
    (category_groups_list LIKE '%Artificial Intelligence%',
      TRUE,
      FALSE) AS ai_category_cb,

  FROM
    comp
  LEFT JOIN (
    SELECT
      1 AS ipo,
      org_uuid AS uuid
    FROM
      gcp_cset_crunchbase.ipos)
  USING
    (uuid) ),
  # VC funding rounds target-investor pairs
  funding_round_stage AS (
  SELECT
    target_uuid,
    investor_uuid,
    investor_name,
    org_name AS target_name,
    COUNT(investor_uuid) OVER(PARTITION BY funding_round) AS n_investors,
    raised_amount_usd AS investment_value,
    investment_type,
    funding_round,
    EXTRACT(YEAR
    FROM
      announced_on) AS year
  FROM (
    SELECT
      funding_round_uuid AS funding_round,
      investor_uuid,
      investor_name
    FROM
      gcp_cset_crunchbase.investments)
  INNER JOIN (
    SELECT
      uuid AS funding_round,
      org_uuid AS target_uuid,
      org_name,
      raised_amount_usd,
      announced_on,
      investment_type
    FROM
      gcp_cset_crunchbase.funding_rounds)
  USING
    (funding_round) ),
  # add MA target-investor pairs
  # add investor country to staging round
  funding_round AS (
  SELECT
    *
  FROM
    funding_round_stage
  LEFT JOIN (
    SELECT
      uuid AS investor_uuid,
      country_code AS investor_country
    FROM
      gcp_cset_crunchbase.investors)
  USING
    (investor_uuid) ),
  add_mergers AS (
  SELECT
    target_uuid,
    investor_uuid,
    * EXCEPT(target_uuid,
      investor_uuid)
  FROM
    funding_round
  UNION ALL (
    SELECT
      acquiree_uuid AS target_uuid,
      acquirer_uuid AS investor_uuid,
      acquirer_name AS investor_name,
      acquiree_name AS target_name,
      1 AS n_investors,
      price_usd AS investment_value,
      'merge' AS investment_type,
      CONCAT( acquirer_uuid,acquiree_uuid) AS funding_round,
      EXTRACT(YEAR
      FROM
        acquired_on) AS year,
      acquirer_country_code AS investor_target_country
    FROM
      gcp_cset_crunchbase.acquisitions) ),
  --    /* add ai companies to investments */
  add_ai_to_inv AS (
  SELECT
    * EXCEPT (target_name),
    COALESCE (a.target_name,
      b.target_name) AS target_name
  FROM
    add_mergers a
  FULL JOIN
    add_ipo_tab b
  ON
    a.target_uuid = b.uuid),
  /* where ai_category_cb is True OR ai_company is True OR investment_from_ai_count > 0 OR investment_to_ai_count > 0*/ distinct_rounds AS (
  SELECT
    DISTINCT target_uuid,
    funding_round,
    investor_uuid,
    investment_value,
    IFNULL(investment_type,
      'missing') AS investment_type,
    uuid,
    target_name,
    investor_name,
    cb_url,
    IFNULL(target_country,
      'missing') AS target_country,
    category_list,
    category_groups_list,
    IFNULL(ipo,
      0) AS ipo,
    IFNULL(ai_company,
      FALSE) AS ai_company,
    ai_category_cb,
    IFNULL(investor_country,
      'missing') AS investor_country,
    year
  FROM
    add_ai_to_inv ),
  imp_step1 AS (
  SELECT
    * EXCEPT(imputed_investment_value_step),
    IFNULL(investment_value,
      imputed_investment_value_step) AS imputed_investment_value
  FROM (
    SELECT
      *,
      PERCENTILE_CONT(investment_value,
        0.5) OVER(PARTITION BY investment_type, ai_company, ipo, target_country, year) AS imputed_investment_value_step
    FROM
      distinct_rounds ) ),
  imp_step2 AS (
  SELECT
    * EXCEPT(imputed_investment_value,
      imputed_investment_value_step),
    IFNULL(imputed_investment_value,
      imputed_investment_value_step) AS imputed_investment_value
  FROM (
    SELECT
      *,
      PERCENTILE_CONT(investment_value,
        0.5) OVER(PARTITION BY investment_type, ai_company, target_country, year) AS imputed_investment_value_step
    FROM
      imp_step1 ) ),
  imp_step3 AS (
  SELECT
    * EXCEPT(imputed_investment_value,
      imputed_investment_value_step),
    IFNULL(imputed_investment_value,
      imputed_investment_value_step) AS imputed_investment_value
  FROM (
    SELECT
      *,
      PERCENTILE_CONT(investment_value,
        0.5) OVER(PARTITION BY investment_type, ai_company, target_country) AS imputed_investment_value_step
    FROM
      imp_step2 ) ),
  imp_step4 AS (
  SELECT
    * EXCEPT(imputed_investment_value,
      imputed_investment_value_step),
    IFNULL(imputed_investment_value,
      imputed_investment_value_step) AS imputed_investment_value
  FROM (
    SELECT
      *,
      PERCENTILE_CONT(investment_value,
        0.5) OVER(PARTITION BY ai_company, target_country) AS imputed_investment_value_step
    FROM
      imp_step3 ) ),
  imp_step5 AS (
  SELECT
    * EXCEPT(imputed_investment_value,
      imputed_investment_value_step),
    IFNULL(imputed_investment_value,
      imputed_investment_value_step) AS imputed_investment_value
  FROM (
    SELECT
      *,
      PERCENTILE_CONT(investment_value,
        0.5) OVER(PARTITION BY ai_company) AS imputed_investment_value_step
    FROM
      imp_step4 ) ),
  imp_step6 AS (
  SELECT
    * EXCEPT(imputed_investment_value,
      imputed_investment_value_step),
    IFNULL(imputed_investment_value,
      imputed_investment_value_step) AS imputed_investment_value
  FROM (
    SELECT
      *,
      PERCENTILE_CONT(investment_value,
        0.5) OVER() AS imputed_investment_value_step
    FROM
      imp_step5 ) ),
  #make outbound_ai table
  outbound_ai AS (
  SELECT
    DISTINCT *
  FROM
    imp_step6
  WHERE
    (ai_company IS TRUE
      OR ai_category_cb IS TRUE)
    AND (year >= 2015)
    AND (year <= 2021)
    AND ( investment_type != "missing"
      AND investment_type != "debt_financing"
      AND investment_type != "equity_crowdfunding"
      AND investment_type != "grant"
      AND investment_type != "non_equity_assistance"
      AND investment_type != "post_ipo_debt"
      AND investment_type != "product_crowdfunding"
      AND investment_type != "post_ipo_debt"))
  #where investor's country code is missing, replace with people's org's country code
SELECT
  DISTINCT investment_type,
  target_name,
  investor_name,
  year,
  imputed_investment_value,
  investor_country,
  target_country
FROM
  outbound_ai
WHERE
  target_name = "Didi"
  OR target_name = "Youibot"
  OR target_name = "Zuoyebang"
  OR target_name = "Talkpush"
  OR target_name = "Laiye"
  OR target_name = "Insilico Medicine"
  OR target_name = "Geek+"
  OR target_name = "Clobotics"
  OR target_name = "Xpeng Motors"

GROUP BY
  investment_type,
  target_name,
  investor_name,
  year,
  imputed_investment_value,
  investor_country,
  target_country
ORDER BY
  target_name)

  group by target_name