Основные понятия
Венчурные фонды — это финансовые организации, которые могут позволить себе высокий риск и инвестировать в компании с инновационной бизнес-идеей или разработанной новой технологией, то есть в стартапы. Цель венчурных фондов — в будущем получить значительную прибыль, которая в разы превысит размер их трат на инвестиции в компанию. Если стартап подорожает, венчурный фонд может получить долю в компании или фиксированный процент от её выручки.
Чтобы процесс финансирования стал менее рискованным, его делят на стадии — раунды. Тот или иной раунд зависит от того, какого уровня развития достигла компания.
Первые этапы — предпосевной и посевной раунды. Предпосевной раунд предполагает, что компания как таковая ещё не создана и находится в стадии замысла. Следующий — посевной — раунд знаменует рост проекта: создатели компании разрабатывают бизнес-модель и привлекают инвесторов.
Если компании требуется ментор или наставник — она привлекает бизнес-ангела. Бизнес-ангелы — инвесторы, которые помимо финансовой поддержки предлагают экспертную помощь. Такой раунд называют ангельским.
Когда стартап становится компанией с проверенной бизнес-моделью и начинает зарабатывать самостоятельно, предложений инвесторов становится больше. Это раунд A, а за ним следуют и другие: B, C, D — на этих этапах компания активно развивается и готовится к IPO.
Иногда выделяют венчурный раунд — финансирование, которое могло поступить от венчурного фонда на любом этапе: начальном или более позднем.
1. Посчитайте, сколько компаний закрылось.
SELECT COUNT(id)
FROM stackoverflow.posts
WHERE (score>300 OR favorites_count >=100) AND post_type_id = 1
2. Отобразите количество привлечённых средств для новостных компаний США. Используйте данные из таблицы company. Отсортируйте таблицу по убыванию значений в поле funding_total
SELECT funding_total
FROM company
WHERE category_code LIKE 'news' AND country_code LIKE 'USA'
ORDER BY funding_total DESC
3. Найдите общую сумму сделок по покупке одних компаний другими в долларах. Отберите сделки, которые осуществлялись только за наличные с 2011 по 2013 год включительно.
SELECT SUM(price_amount)
FROM acquisition
WHERE term_code LIKE 'cash'
AND EXTRACT(YEAR FROM CAST(acquired_at AS date)) BETWEEN 2011 AND 2013
4. Отобразите имя, фамилию и названия аккаунтов людей в поле network_username, у которых названия аккаунтов начинаются на 'Silver'.
SELECT first_name, last_name, twitter_username
FROM people
WHERE twitter_username LIKE 'Silver%'
5. Выведите на экран всю информацию о людях, у которых названия аккаунтов в поле network_username содержат подстроку 'money', а фамилия начинается на 'K'.
SELECT *
FROM people
WHERE twitter_username LIKE '%money%'
AND last_name LIKE 'K%'
6. Для каждой страны отобразите общую сумму привлечённых инвестиций, которые получили компании, зарегистрированные в этой стране. Страну, в которой зарегистрирована компания, можно определить по коду страны. Отсортируйте данные по убыванию суммы.
SELECT country_code, SUM(funding_total)
FROM company
GROUP BY country_code
ORDER BY SUM(funding_total) DESC
7. Составьте таблицу, в которую войдёт дата проведения раунда, а также минимальное и максимальное значения суммы инвестиций, привлечённых в эту дату.
Оставьте в итоговой таблице только те записи, в которых минимальное значение суммы инвестиций не равно нулю и не равно максимальному значению.
SELECT funded_at, MIN(raised_amount), MAX(raised_amount)
FROM funding_round
GROUP BY funded_at
HAVING MIN(raised_amount) <> 0 AND MIN(raised_amount) <> MAX(raised_amount)
8. Создайте поле с категориями:
- Для фондов, которые инвестируют в 100 и более компаний, назначьте категорию high_activity.
- Для фондов, которые инвестируют в 20 и более компаний до 100, назначьте категорию middle_activity.
- Если количество инвестируемых компаний фонда не достигает 20, назначьте категорию low_activity.
Отобразите все поля таблицы fund и новое поле с категориями.
SELECT *,
CASE
WHEN invested_companies < 20 THEN 'low_activity'
WHEN invested_companies < 100 THEN 'middle_activity'
WHEN invested_companies >= 100 THEN 'high_activity'
END
FROM fund
9. Для каждой из категорий, назначенных в предыдущем задании, посчитайте округлённое до ближайшего целого числа среднее количество инвестиционных раундов, в которых фонд принимал участие. Выведите на экран категории и среднее число инвестиционных раундов. Отсортируйте таблицу по возрастанию среднего.
SELECT
CASE
WHEN invested_companies>=100 THEN 'high_activity'
WHEN invested_companies>=20 THEN 'middle_activity'
ELSE 'low_activity'
END AS activity,
ROUND(AVG(investment_rounds))
FROM fund
GROUP BY activity
ORDER BY ROUND(AVG(investment_rounds));
10. Проанализируйте, в каких странах находятся фонды, которые чаще всего инвестируют в стартапы.
Для каждой страны посчитайте минимальное, максимальное и среднее число компаний, в которые инвестировали фонды этой страны, основанные с 2010 по 2012 год включительно. Исключите страны с фондами, у которых минимальное число компаний, получивших инвестиции, равно нулю.
Выгрузите десять самых активных стран-инвесторов: отсортируйте таблицу по среднему количеству компаний от большего к меньшему. Затем добавьте сортировку по коду страны в лексикографическом порядке.
SELECT country_code,
MIN(invested_companies),
MAX(invested_companies),
AVG(invested_companies)
FROM fund
WHERE EXTRACT(YEAR FROM CAST(founded_at AS date)) BETWEEN 2010 AND 2012
GROUP BY country_code
HAVING MIN(invested_companies) > 0
ORDER BY AVG(invested_companies) DESC, country_code
LIMIT 10
11. Отобразите имя и фамилию всех сотрудников стартапов. Добавьте поле с названием учебного заведения, которое окончил сотрудник, если эта информация известна.
SELECT first_name, last_name, instituition
FROM people
LEFT JOIN education ON people.id = education.person_id
12. Для каждой компании найдите количество учебных заведений, которые окончили её сотрудники. Выведите название компании и число уникальных названий учебных заведений. Составьте топ-5 компаний по количеству университетов.
SELECT company.name,
COUNT(DISTINCT(instituition))
FROM company
LEFT JOIN people ON company.id = people.company_id
LEFT JOIN education ON people.id = education.person_id
GROUP BY company.id
ORDER BY COUNT(DISTINCT(instituition)) DESC
LIMIT 5
13. Составьте список с уникальными названиями закрытых компаний, для которых первый раунд финансирования оказался последним.
SELECT DISTINCT(company.name)
FROM funding_round
LEFT JOIN company ON company.id = funding_round.company_id
WHERE is_first_round = 1 AND is_last_round = 1
AND company.status LIKE 'closed'
14. Составьте список уникальных номеров сотрудников, которые работают в компаниях, отобранных в предыдущем задании.
WITH
org AS
(
SELECT DISTINCT(company.id)
FROM funding_round
LEFT JOIN company ON company.id = funding_round.company_id
WHERE is_first_round = 1 AND is_last_round = 1
AND company.status LIKE 'closed'
)
SELECT DISTINCT(people.id)
FROM people
WHERE people.company_id IN (SELECT * FROM org)
15. Составьте таблицу, куда войдут уникальные пары с номерами сотрудников из предыдущей задачи и учебным заведением, которое окончил сотрудник.
WITH
org AS
(
SELECT DISTINCT(company.id)
FROM funding_round
LEFT JOIN company ON company.id = funding_round.company_id
WHERE is_first_round = 1 AND is_last_round = 1
AND company.status LIKE 'closed'
),
p_id AS
(
SELECT DISTINCT(people.id)
FROM people
WHERE people.company_id IN (SELECT * FROM org)
)
SELECT person_id, instituition
FROM education
WHERE person_id IN (SELECT * FROM p_id)
UNION
SELECT person_id, instituition
FROM education
WHERE person_id IN (SELECT * FROM p_id)
16. Посчитайте количество учебных заведений для каждого сотрудника из предыдущего задания. При подсчёте учитывайте, что некоторые сотрудники могли окончить одно и то же заведение дважды.
WITH
org AS
(
SELECT DISTINCT(company.id)
FROM funding_round
LEFT JOIN company ON company.id = funding_round.company_id
WHERE is_first_round = 1 AND is_last_round = 1
AND company.status LIKE 'closed'
),
p_id AS
(
SELECT DISTINCT(people.id)
FROM people
WHERE people.company_id IN (SELECT * FROM org)
)
SELECT person_id, COUNT(instituition)
FROM education
WHERE person_id IN (SELECT * FROM p_id)
GROUP BY person_id
17. Дополните предыдущий запрос и выведите среднее число учебных заведений (всех, не только уникальных), которые окончили сотрудники разных компаний. Нужно вывести только одну запись, группировка здесь не понадобится.
WITH
org AS
(
SELECT DISTINCT(company.id)
FROM funding_round
LEFT JOIN company ON company.id = funding_round.company_id
WHERE is_first_round = 1 AND is_last_round = 1
AND company.status LIKE 'closed'
),
p_id AS
(
SELECT DISTINCT(people.id)
FROM people
WHERE people.company_id IN (SELECT * FROM org)
)
SELECT AVG(count)
FROM
(
SELECT person_id, COUNT(instituition)
FROM education
WHERE person_id IN (SELECT * FROM p_id)
GROUP BY person_id
) AS tab
18. Напишите похожий запрос: выведите среднее число учебных заведений
WITH
org AS
(
SELECT DISTINCT(company.id)
FROM funding_round
LEFT JOIN company ON company.id = funding_round.company_id
WHERE company.name LIKE 'Facebook'
),
p_id AS
(
SELECT DISTINCT(people.id)
FROM people
WHERE people.company_id IN (SELECT * FROM org)
)
SELECT AVG(count)
FROM
(
SELECT person_id, COUNT(instituition)
FROM education
WHERE person_id IN (SELECT * FROM p_id)
GROUP BY person_id
) AS tab
19. Составьте таблицу из полей:
- name_of_fund — название фонда;
- name_of_company — название компании;
- amount — сумма инвестиций, которую привлекла компания в раунде.
В таблицу войдут данные о компаниях, в истории которых было больше шести важных этапов, а раунды финансирования проходили с 2012 по 2013 год включительно.
SELECT
fund.name AS name_of_fund,
company.name AS name_of_company,
funding_round.raised_amount AS amount
FROM funding_round
INNER JOIN investment ON funding_round.id = investment.funding_round_id
INNER JOIN fund ON investment.fund_id = fund.id
INNER JOIN company ON funding_round.company_id = company.id
WHERE EXTRACT(YEAR FROM CAST(funding_round.funded_at AS date)) BETWEEN 2012 AND 2013
AND company.milestones > 6
20. Выгрузите таблицу, в которой будут такие поля:
- название компании-покупателя;
- сумма сделки;
- название компании, которую купили;
- сумма инвестиций, вложенных в купленную компанию;
- доля, которая отображает, во сколько раз сумма покупки превысила сумму вложенных в компанию инвестиций, округлённая до ближайшего целого числа.
Не учитывайте те сделки, в которых сумма покупки равна нулю. Если сумма инвестиций в компанию равна нулю, исключите такую компанию из таблицы.
Отсортируйте таблицу по сумме сделки от большей к меньшей, а затем по названию купленной компании в лексикографическом порядке. Ограничьте таблицу первыми десятью записями.
SELECT c_acquiring.name, acquisition.price_amount, c_acquired.name, c_acquired.funding_total, ROUND(acquisition.price_amount/c_acquired.funding_total)
FROM acquisition
LEFT JOIN company AS c_acquiring ON c_acquiring.id = acquisition.acquiring_company_id
LEFT JOIN company AS c_acquired ON c_acquired.id = acquisition.acquired_company_id
WHERE acquisition.price_amount > 0 AND c_acquired.funding_total > 0
ORDER BY acquisition.price_amount DESC, c_acquired.name
LIMIT 10
21. Выгрузите таблицу, в которую войдут названия компаний из категории social, получившие финансирование с 2010 по 2013 год включительно. Проверьте, что сумма инвестиций не равна нулю. Выведите также номер месяца, в котором проходил раунд финансирования.
SELECT company.name, EXTRACT(MONTH FROM CAST(funding_round.funded_at AS date))
FROM company
LEFT JOIN funding_round ON company.id = funding_round.company_id
WHERE company.category_code LIKE 'social'
AND EXTRACT(YEAR FROM CAST(funding_round.funded_at AS date)) BETWEEN 2010 AND 2013
AND funding_round.raised_amount > 0
22. Отберите данные по месяцам с 2010 по 2013 год, когда проходили инвестиционные раунды. Сгруппируйте данные по номеру месяца и получите таблицу, в которой будут поля:
- номер месяца, в котором проходили раунды;
- количество уникальных названий фондов из США, которые инвестировали в этом месяце;
- количество компаний, купленных за этот месяц;
- общая сумма сделок по покупкам в этом месяце.
WITH
fund AS
(SELECT EXTRACT(MONTH FROM CAST(fr.funded_at AS date)) AS month,
COUNT(DISTINCT(f.name)) AS funds
FROM funding_round AS fr
INNER JOIN investment AS i ON fr.id = i.funding_round_id
INNER JOIN fund AS f ON i.fund_id = f.id
WHERE f.country_code LIKE 'USA'
AND EXTRACT(YEAR FROM CAST(fr.funded_at AS date)) BETWEEN 2010 AND 2013
GROUP BY month),
aqui AS
(SELECT EXTRACT(MONTH FROM CAST(a.acquired_at AS date)) AS month,
COUNT(a.acquired_company_id) AS count,
SUM(a.price_amount) AS sum
FROM acquisition AS a
WHERE EXTRACT(YEAR FROM CAST(a.acquired_at AS date)) BETWEEN 2010 AND 2013
GROUP BY month)
SELECT a.month, a.funds, b.count, b.sum
FROM aqui AS b
JOIN fund AS a ON b.month = a.month
23. Составьте сводную таблицу и выведите среднюю сумму инвестиций для стран, в которых есть стартапы, зарегистрированные в 2011, 2012 и 2013 годах. Данные за каждый год должны быть в отдельном поле. Отсортируйте таблицу по среднему значению инвестиций за 2011 год от большего к меньшему.
WITH
y2011 AS (SELECT country_code AS country, AVG(funding_total) AS avg2011
FROM company
WHERE EXTRACT(YEAR FROM CAST(founded_at AS date)) = 2011
GROUP BY country_code),
y2012 AS (SELECT country_code AS country, AVG(funding_total) AS avg2012
FROM company
WHERE EXTRACT(YEAR FROM CAST(founded_at AS date)) = 2012
GROUP BY country_code),
y2013 AS (SELECT country_code AS country, AVG(funding_total) AS avg2013
FROM company
WHERE EXTRACT(YEAR FROM CAST(founded_at AS date)) = 2013
GROUP BY country_code)
SELECT y2011.country, y2011.avg2011, y2012.avg2012, y2013.avg2013
FROM y2011
INNER JOIN y2012 ON y2011.country = y2012.country
INNER JOIN y2013 ON y2011.country = y2013.country
ORDER BY y2011.avg2011 DESC