Первые 70% курса по SQL кажутся довольно простыми. Сложности начинаются на остальных 30%.
С 2015 по 2019 годы я прошёл четыре цикла собеседований на должность аналитика данных и специалиста по анализу данных в более чем десятке компаний. После очередного неудачного интервью в 2017 году — когда я запутался в сложных вопросах по SQL — я начал составлять задачник с вопросами по SQL средней и высокой сложности, чтобы лучше готовиться к собеседованиям. Этот справочник очень пригодился в последнем цикле собеседований 2019 года. За последний год я поделился этим руководством с парой друзей, а благодаря дополнительному свободному времени из-за пандемии отшлифовал его — и составил этот документ.
Есть множество отличных руководств по SQL для начинающих. Мои любимые — это интерактивные курсы Codecademy по SQL и Select Star SQL от Цзы Чон Као. Но в реальности первые 70% из курса SQL довольно просты, а настоящие сложности начинаются в остальных 30%, которые не освещаются в руководствах для начинающих. Так вот, на собеседованиях для аналитиков данных и специалистов по анализу данных в технологических компаниях часто задают вопросы именно по этим 30%.
Удивительно, но я не нашёл исчерпывающего источника по таким вопросам среднего уровня сложности, поэтому составил данное руководство.
Оно полезно для собеседований, но заодно повысит вашу эффективность на текущем и будущих местах работы. Лично я считаю, что некоторые упомянутые шаблоны SQL полезны и для ETL-систем, на которых работают инструменты отчётности и функции анализа данных для выявления тенденций.
Содержание
- Сделанные допущения и как использовать руководство
- Советы по решению сложных задач на собеседованиях по SQL
- Задачи на самообъединения
- № 1. Процентное изменение месяц к месяцу
- № 2. Маркировка древовидной структуры
- № 3. Удержание пользователей в месяц (несколько частей)
- Часть 1
- Часть 2
- Часть 3
- № 4. Нарастающий итог
- № 5. Скользящее среднее
- № 6. Несколько условий соединения
- Задачи на оконные функции
- № 1. Найти идентификатор с максимальным значением
- № 2. Среднее значение и ранжирование с оконной функцией (несколько частей)
- Часть 1
- Часть 2
- Другие задачи средней и высокой сложности
- № 1. Гистограммы
- № 2. Перекрёстное соединение (несколько частей)
- Часть 1
- Часть 2
- № 3. Продвинутые расчёты
Нужно понимать, что на собеседованиях дата-аналитиков и специалистов по анализу данных задают вопросы не только по SQL. Другие общие темы включают обсуждение прошлых проектов, A/B-тестирование, разработку метрик и открытые аналитические проблемы. Примерно три года назад на Quora публиковались советы по собеседованию на должность аналитика продукта (product analyst) в Facebook. Там эта тема обсуждается более подробно. Тем не менее, если улучшение знаний по SQL поможет вам на собеседовании, то это руководство вполне стоит потраченного времени.
В будущем я могу перенести код из этого руководства на сайт вроде Select Star SQL, чтобы было проще писать инструкции SQL — и видеть результат выполнения кода в реальном времени. Как вариант — добавить вопросы как проблемы на платформу для подготовки к собеседованиям LeetCode. Пока же я просто хотел опубликовать этот документ, чтобы люди могли прямо сейчас ознакомиться с этой информацией.
Сделанные допущения и как использовать руководство
Предположения о знании языка SQL: Предполагается, что у вас есть рабочие знания SQL. Вероятно, вы часто используете его на работе, но хотите отточить навыки в таких темах, как самообъединения и оконные функции.
Как использовать данное руководство: Поскольку на собеседовании часто используется доска или виртуальный блокнот (без компиляции кода), то рекомендую взять карандаш и бумагу — и записать решения для каждой проблемы, а после завершения сравнить свои записи с ответами. Или отработайте свои ответы вместе с другом, который выступит в качестве интервьюера!
- Небольшие синтаксические ошибки не имеют большого значения во время собеседования с доской или блокнотом. Но они могут отвлекать интервьюера, поэтому в идеале старайтесь уменьшить их количество, чтобы сконцентрировать всё внимание на логике.
- Приведённые ответы не обязательно единственный способ решить каждую задачу. Не стесняйтесь писать комментарии с дополнительными решениями, которые можно добавить в это руководство!
Советы по решению сложных задач на собеседованиях по SQL
Сначала стандартные советы для всех собеседований по программированию…
- Внимательно выслушайте описание проблемы, повторите всю суть проблемы интервьюеру
- Сформулируйте пограничный случай, чтобы продемонстрировать, что вы действительно понимаете проблему (т. е. строку, которая не будет включена в итоговый запрос SQL, который вы собираетесь написать)
- (Если проблема связана с самообъединением) для своей же пользы нарисуйте, как будет выглядеть самообъединение — обычно это минимум три столбца: нужный столбец из основной таблицы, столбец для объединения из основной таблицы и столбец для объединения из вторичной таблицы
- Или, когда вы лучше освоите задачи самообъединения, можете объяснить этот шаг устно
- Начните писать SQL, пусть с ошибками, вместо попыток полностью понять проблему. Формулируйте свои предположения по ходу дела, чтобы ваш интервьюер мог вас поправить.
Благодарности и дополнительные ресурсы
Некоторые из перечисленных здесь проблем адаптированы из старых записей в блоге Periscope (в основном написанных Шоном Куком около 2014 года, хотя его авторство, видимо, убрали из материалов после слияния SiSense с Periscope), а также из обсуждений на StackOverflow. В случае необходимости, источники отмечены в начале каждого вопроса.
На Select Star SQL тоже хорошая подборка задачек, дополняющих проблемы из этого документа.
Пожалуйста, обратите внимание, что эти вопросы не являются буквальными копиями вопросов с моих собственных собеседований, и они не использовались в компаниях, в которых я работал или работаю.
Задачи на самообъединение
№ 1. Процентное изменение месяц к месяцу
Контекст: часто полезно знать, как изменяется ключевая метрика, например, месячная аудитория активных пользователей, от месяца к месяцу. Допустим у нас есть таблица logins в таком виде:
| user_id | date | |---------|------------| | 1 | 2018-07-01 | | 234 | 2018-07-02 | | 3 | 2018-07-02 | | 1 | 2018-07-02 | | ... | ... | | 234 | 2018-10-04 |
Задача: найти ежемесячное процентное изменение месячной аудитории активных пользователей (MAU).
Решение:
(Это решение, как и другие блоки кода в этом документе, содержит комментарии об элементах синтаксисе SQL, которые могут отличаться между разными вариантами SQL, и прочие заметки)
WITH mau AS
(
SELECT
/*
* Обычно интервьюер позволяет вам написать псевдокод для
* функций даты, т. е. НЕ будет проверять, как вы их помните.
* Просто объясните на доске, что делает функция
*
* В Postgres доступна DATE_TRUNC(), но аналогичный результат
* могут дать другие функции даты SQL или их комбинации
* См. https://www.postgresql.org/docs/9.0/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
*/
DATE_TRUNC('month', date) month_timestamp,
COUNT(DISTINCT user_id) mau
FROM
logins
GROUP BY
DATE_TRUNC('month', date)
)
SELECT
/*
* В эту инструкцию SELECT не нужно буквально включать предыдущий месяц.
*
* Но как упоминалось в разделе с советами выше, может быть полезно
* хотя бы набросать самообъединения, чтобы не запутаться, какая
* таблица представляет прошлый месяц к текущему и т.д.
*/
a.month_timestamp previous_month,
a.mau previous_mau,
b.month_timestamp current_month,
b.mau current_mau,
ROUND(100.0*(b.mau - a.mau)/a.mau,2) AS percent_change
FROM
mau a
JOIN
/*
* Как вариант `ON b.month_timestamp = a.month_timestamp + interval '1 month'`
*/
mau b ON a.month_timestamp = b.month_timestamp - interval '1 month' № 2. Маркировка древовидной структуры
Контекст: предположим, у вас есть таблица tree с двумя столбцами: в первом указаны узлы, а во втором — родительские узлы.
node parent 1 2 2 5 3 5 4 3 5 NULL
Задача: написать SQL таким образом, чтобы мы обозначили каждый узел как внутренний (inner), корневой (root) или конечный узел/лист (leaf), так что для вышеперечисленных значений получится следующее:
node label
1 Leaf
2 Inner
3 Inner
4 Leaf
5 Root(Примечание: более подробно о терминологии древовидной структуры данных можно почитать здесь. Однако для решения этой проблемы она не нужна!)
Решение:
Благодарность: это более обобщённое решение предложил Фабиан Хофман 2 мая 2020 года. Спасибо, Фабиан!
WITH join_table AS
(
SELECT
cur.node,
cur.parent,
COUNT(next.node) AS num_children
FROM
tree cur
LEFT JOIN
tree next ON (next.parent = cur.node)
GROUP BY
cur.node,
cur.parent
)
SELECT
node,
CASE
WHEN parent IS NULL THEN "Root"
WHEN num_children = 0 THEN "Leaf"
ELSE "Inner"
END AS label
FROM
join_table Альтернативное решение, без явных соединений:
Благодарность: Уильям Чарджин 2 мая 2020 года обратил внимание на необходимость условия WHERE parent IS NOT NULL, чтобы это решение возвращало Leaf вместо NULL. Спасибо, Уильям!
SELECT
node,
CASE
WHEN parent IS NULL THEN 'Root'
WHEN node NOT IN
(SELECT parent FROM tree WHERE parent IS NOT NULL) THEN 'Leaf'
WHEN node IN (SELECT parent FROM tree) AND parent IS NOT NULL THEN 'Inner'
END AS label
from
tree№ 3. Удержание пользователей в месяц (несколько частей)
Благодарность: эта задача адаптирована из статьи в блоге SiSense «Использование самообъединений для расчёта показателей удержания, оттока и реактивации».
Часть 1
Контекст: допустим, у нас есть статистика по авторизации пользователей на сайте в таблице logins:
| user_id | date | |---------|------------| | 1 | 2018-07-01 | | 234 | 2018-07-02 | | 3 | 2018-07-02 | | 1 | 2018-07-02 | | ... | ... | | 234 | 2018-10-04 |
Задача: написать запрос, который получает количество удержанных пользователей в месяц. В нашем случае данный параметр определяется как количество пользователей, которые авторизовались в системе и в этом, и в предыдущем месяце.
Решение:
SELECT
DATE_TRUNC('month', a.date) month_timestamp,
COUNT(DISTINCT a.user_id) retained_users
FROM
logins a
JOIN
logins b ON a.user_id = b.user_id
AND DATE_TRUNC('month', a.date) = DATE_TRUNC('month', b.date) +
interval '1 month'
GROUP BY
date_trunc('month', a.date)Благодарность:
Том Моэртел указал на то, что предварительная дедубликация user_id перед самообъединением делает решение более эффективным, и предложил код ниже. Спасибо, Том!
Альтернативное решение:
WITH DistinctMonthlyUsers AS (
/*
* Для каждого месяца определяем *набор* пользователей, которые
* выполнили авторизацию
*/
SELECT DISTINCT
DATE_TRUNC('MONTH', a.date) AS month_timestamp,
user_id
FROM logins
)
SELECT
CurrentMonth.month_timestamp month_timestamp,
COUNT(PriorMonth.user_id) AS retained_user_count
FROM
DistinctMonthlyUsers AS CurrentMonth
LEFT JOIN
DistinctMonthlyUsers AS PriorMonth
ON
CurrentMonth.month_timestamp = PriorMonth.month_timestamp + INTERVAL '1 MONTH'
AND
CurrentMonth.user_id = PriorMonth.user_idЧасть 2
Задача: теперь возьмём предыдущую задачу по вычислению количества удержанных пользователей в месяц — и перевернём её с ног на голову. Напишем запрос для подсчёта пользователей, которые не вернулись на сайт в этом месяце. То есть «потерянных» пользователей.
Решение:
SELECT
DATE_TRUNC('month', a.date) month_timestamp,
COUNT(DISTINCT b.user_id) churned_users
FROM
logins a
FULL OUTER JOIN
logins b ON a.user_id = b.user_id
AND DATE_TRUNC('month', a.date) = DATE_TRUNC('month', b.date) +
interval '1 month'
WHERE
a.user_id IS NULL
GROUP BY
DATE_TRUNC('month', a.date)Обратите внимание, что эту проблему можно решить также с помощью соединений LEFT или RIGHT.
Часть 3
Примечание: вероятно, это более сложная задача, чем вам предложат на реальном собеседовании. Воспринимайте её скорее как головоломку — или можете пропустить и перейти к следующей задаче.
Контекст: итак, мы хорошо справились с двумя предыдущими проблемами. По условиям новой задачи теперь у нас появилась таблица потерянных пользователей user_churns. Если пользователь была активен в прошлом месяце, но затем не активен в этом, то он вносится в таблицу за этот месяц. Вот как выглядит user_churns:
| user_id | month_date | |---------|------------| | 1 | 2018-05-01 | | 234 | 2018-05-01 | | 3 | 2018-05-01 | | 12 | 2018-05-01 | | ... | ... | | 234 | 2018-10-01 |
Задача: теперь вы хотите провести когортный анализ, то есть анализ совокупности активных пользователей, которые были реактивированы в прошлом. Создайте таблицу с такими пользователями. Для создания когорты можете использовать таблицы user_churns и logins. В Postgres текущая временная метка доступна через current_timestamp.
Решение:
WITH user_login_data AS
(
SELECT
DATE_TRUNC('month', a.date) month_timestamp,
a.user_id,
/*
* По крайней мере, в тех вариантах SQL, что я использовал,
* не нужно включать в инструкцию SELECT колонки из HAVING.
* Я здесь выписал их для большей ясности.
*/
MAX(b.month_date) as most_recent_churn,
MAX(DATE_TRUNC('month', c.date)) as most_recent_active
FROM
logins a
JOIN
user_churns b
ON a.user_id = b.user_id AND DATE_TRUNC('month', a.date) > b.month_date
JOIN
logins c
ON a.user_id = c.user_id
AND
DATE_TRUNC('month', a.date) > DATE_TRUNC('month', c.date)
WHERE
DATE_TRUNC('month', a.date) = DATE_TRUNC('month', current_timestamp)
GROUP BY
DATE_TRUNC('month', a.date),
a.user_id
HAVING
most_recent_churn > most_recent_active№ 4. Нарастающий итог
Благодарность: эта задача адаптирована из статьи в блоге SiSense «Моделирование денежных потоков в SQL».
Контекст: допустим, у нас есть таблица transactions в таком виде:
| date | cash_flow | |------------|-----------| | 2018-01-01 | -1000 | | 2018-01-02 | -100 | | 2018-01-03 | 50 | | ... | ... |
Где cash_flow — это выручка минус затраты за каждый день.
Задача: написать запрос, чтобы получить нарастающий итог для денежного потока каждый день таким образом, чтобы в конечном итоге получилась таблица в такой форме:
| date | cumulative_cf | |------------|---------------| | 2018-01-01 | -1000 | | 2018-01-02 | -1100 | | 2018-01-03 | -1050 | | ... | ... |
Решение:
SELECT
a.date date,
SUM(b.cash_flow) as cumulative_cf
FROM
transactions a
JOIN b
transactions b ON a.date >= b.date
GROUP BY
a.date
ORDER BY
date ASCАльтернативное решение с использованием оконной функции (более эффективное!):
SELECT
date,
SUM(cash_flow) OVER (ORDER BY date ASC) as cumulative_cf
FROM
transactions
ORDER BY
date ASC№ 5. Скользящее среднее
Благодарность: эта задача адаптирована из статьи в блоге SiSense «Скользящие средние в MySQL и SQL Server».
Примечание: скользящее среднее можно вычислить разными способами. Здесь мы используем предыдущее среднее значение. Таким образом, метрика для седьмого дня месяца будет средним значением предыдущих шести дней и его самого.
Контекст: допустим, у нас есть таблица signups в таком виде:
| date | sign_ups | |------------|----------| | 2018-01-01 | 10 | | 2018-01-02 | 20 | | 2018-01-03 | 50 | | ... | ... | | 2018-10-01 | 35 |
Задача: написать запрос, чтобы получить 7-дневное скользящее среднее ежедневных регистраций.
Решение:
SELECT
a.date,
AVG(b.sign_ups) average_sign_ups
FROM
signups a
JOIN
signups b ON a.date <= b.date + interval '6 days' AND a.date >= b.date
GROUP BY
a.date№ 6. Несколько условий соединения
Благодарность: эта задача адаптирована из статьи в блоге SiSense «Анализ вашей электронной почты с помощью SQL».
Контекст: скажем, наша таблица emails содержит электронные письма, отправленные с адреса zach@g.com и полученные на него:
| id | subject | from | to | timestamp | |----|----------|--------------|--------------|---------------------| | 1 | Yosemite | zach@g.com | thomas@g.com | 2018-01-02 12:45:03 | | 2 | Big Sur | sarah@g.com | thomas@g.com | 2018-01-02 16:30:01 | | 3 | Yosemite | thomas@g.com | zach@g.com | 2018-01-02 16:35:04 | | 4 | Running | jill@g.com | zach@g.com | 2018-01-03 08:12:45 | | 5 | Yosemite | zach@g.com | thomas@g.com | 2018-01-03 14:02:01 | | 6 | Yosemite | thomas@g.com | zach@g.com | 2018-01-03 15:01:05 | | .. | .. | .. | .. | .. |
Задача:написать запрос, чтобы получить время отклика на каждое письмо (id), отправленное на zach@g.com. Не включать письма на другие адреса. Предположим, что у каждого треда уникальная тема. Имейте в виду, что в треде может быть несколько писем туда и обратно между zach@g.com и другими адресатами.
Решение:
SELECT
a.id,
MIN(b.timestamp) - a.timestamp as time_to_respond
FROM
emails a
JOIN
emails b
ON
b.subject = a.subject
AND
a.to = b.from
AND
a.from = b.to
AND
a.timestamp < b.timestamp
WHERE
a.to = 'zach@g.com'
GROUP BY
a.id Задачи на оконные функции
№ 1. Найти идентификатор с максимальным значением
Контекст: Допустим, у нас есть таблица salaries с данными об отделах и зарплате сотрудников в следующем формате:
depname | empno | salary | -----------+-------+--------+ develop | 11 | 5200 | develop | 7 | 4200 | develop | 9 | 4500 | develop | 8 | 6000 | develop | 10 | 5200 | personnel | 5 | 3500 | personnel | 2 | 3900 | sales | 3 | 4800 | sales | 1 | 5000 | sales | 4 | 4800 |
Задача: написать запрос, чтобы получить empno с самой высокой зарплатой. Убедитесь, что ваше решение обрабатывает случаи одинаковых зарплатами!
Решение:
WITH max_salary AS (
SELECT
MAX(salary) max_salary
FROM
salaries
)
SELECT
s.empno
FROM
salaries s
JOIN
max_salary ms ON s.salary = ms.max_salaryАльтернативное решение с использованием RANK():
WITH sal_rank AS
(SELECT
empno,
RANK() OVER(ORDER BY salary DESC) rnk
FROM
salaries)
SELECT
empno
FROM
sal_rank
WHERE
rnk = 1;№ 2. Среднее значение и ранжирование с оконной функцией (несколько частей)
Часть 1
Контекст: допустим, у нас есть таблица salaries в таком формате:
depname | empno | salary | -----------+-------+--------+ develop | 11 | 5200 | develop | 7 | 4200 | develop | 9 | 4500 | develop | 8 | 6000 | develop | 10 | 5200 | personnel | 5 | 3500 | personnel | 2 | 3900 | sales | 3 | 4800 | sales | 1 | 5000 | sales | 4 | 4800 |
Задача: написать запрос, который возвращает ту же таблицу, но с новым столбцом, в котором указана средняя зарплата по департаменту. Мы бы ожидали таблицу в таком виде:
depname | empno | salary | avg_salary | -----------+-------+--------+------------+ develop | 11 | 5200 | 5020 | develop | 7 | 4200 | 5020 | develop | 9 | 4500 | 5020 | develop | 8 | 6000 | 5020 | develop | 10 | 5200 | 5020 | personnel | 5 | 3500 | 3700 | personnel | 2 | 3900 | 3700 | sales | 3 | 4800 | 4867 | sales | 1 | 5000 | 4867 | sales | 4 | 4800 | 4867 |
Решение:
SELECT
*,
/*
* AVG() is a Postgres command, but other SQL flavors like BigQuery use
* AVERAGE()
*/
ROUND(AVG(salary),0) OVER (PARTITION BY depname) avg_salary
FROM
salariesЧасть 2
Задача: напишите запрос, который добавляет столбец с позицией каждого сотрудника в табели на основе его зарплаты в своём отделе, где сотрудник с самой высокой зарплатой получает позицию 1. Мы бы ожидали таблицу в таком виде:
depname | empno | salary | salary_rank | -----------+-------+--------+-------------+ develop | 11 | 5200 | 2 | develop | 7 | 4200 | 5 | develop | 9 | 4500 | 4 | develop | 8 | 6000 | 1 | develop | 10 | 5200 | 2 | personnel | 5 | 3500 | 2 | personnel | 2 | 3900 | 1 | sales | 3 | 4800 | 2 | sales | 1 | 5000 | 1 | sales | 4 | 4800 | 2 |
Решение:
SELECT
*,
RANK() OVER(PARTITION BY depname ORDER BY salary DESC) salary_rank
FROM
salaries Другие задачи средней и высокой сложности
№ 1. Гистограммы
Контекст: Допустим, у нас есть таблица sessions, где каждая строка представляет собой сеанс потоковой передачи видео с длиной в секундах:
| session_id | length_seconds | |------------|----------------| | 1 | 23 | | 2 | 453 | | 3 | 27 | | .. | .. |
Задача: написать запрос, чтобы подсчитать количество сеансов, которые попадают промежутки по пять секунд, т. е. для приведённого выше фрагмента результат будет примерно такой:
| bucket | count | |---------|-------| | 20-25 | 2 | | 450-455 | 1 |
Максимальная оценка засчитывается за надлежащие метки строк («5-10» и т. д.)
Решение:
WITH bin_label AS
(SELECT
session_id,
FLOOR(length_seconds/5) as bin_label
FROM
sessions
)
SELECT
CONCATENTATE(STR(bin_label*5), '-', STR(bin_label*5+5)) bucket,
COUNT(DISTINCT session_id) count
GROUP BY
bin_label
ORDER BY
bin_label ASC № 2. Перекрёстное соединение (несколько частей)
Часть 1
Контекст: допустим, у нас есть таблица state_streams, где в каждой строке указано название штата и общее количество часов потоковой передачи с видеохостинга:
| state | total_streams | |-------|---------------| | NC | 34569 | | SC | 33999 | | CA | 98324 | | MA | 19345 | | .. | .. |
(На самом деле в агрегированных таблицах такого типа обычно есть ещё столбец даты, но для этой задачи мы его исключим)
Задача: написать запрос, чтобы получить пары штатов с общим количеством потоков в пределах тысячи друг от друга. Для приведённого выше фрагмента мы хотели бы увидеть что-то вроде:
| state_a | state_b | |---------|---------| | NC | SC | | SC | NC |
Решение:
SELECT
a.state as state_a,
b.state as state_b
FROM
state_streams a
CROSS JOIN
state_streams b
WHERE
ABS(a.total_streams - b.total_streams) < 1000
AND
a.state <> b.state Для информации, перекрёстные соединения также можно писать без явного указания соединения:
SELECT
a.state as state_a,
b.state as state_b
FROM
state_streams a, state_streams b
WHERE
ABS(a.total_streams - b.total_streams) < 1000
AND
a.state <> b.state Часть 2
Примечание: этот скорее бонусный вопрос, чем реально важный шаблон SQL. Можете его пропустить!
Задача: как можно изменить SQL из предыдущего решения, чтобы удалить дубликаты? Например, на примере той же таблицы, чтобы пара NC и SC появилась только один раз, а не два.
Решение:
SELECT
a.state as state_a,
b.state as state_b
FROM
state_streams a, state_streams b
WHERE
ABS(a.total_streams - b.total_streams) < 1000
AND
a.state > b.state № 3. Продвинутые расчёты
Благодарность: эта задача адаптирована из обсуждения по вопросу, который я задал на StackOverflow (мой ник zthomas.nc).
Примечание: вероятно, это более сложная задача, чем вам предложат на реальном собеседовании. Воспринимайте её скорее как головоломку — или можете пропустить её!
Контекст: допустим, у нас есть таблица table такого вида, где одному и тому же пользователю user могут соответствовать разные значения класса class:
| user | class | |------|-------| | 1 | a | | 1 | b | | 1 | b | | 2 | b | | 3 | a |
Задача: предположим, что существует только два возможных значения для класса. Напишите запрос для подсчёта количества пользователей в каждом классе. При этом пользователи с обеими метками a и b должны относиться к классу b.
Для нашего образца получится такой результат:
| class | count | |-------|-------| | a | 1 | | b | 2 |
Решение:
WITH usr_b_sum AS
(
SELECT
user,
SUM(CASE WHEN class = 'b' THEN 1 ELSE 0 END) num_b
FROM
table
GROUP BY
user
),
usr_class_label AS
(
SELECT
user,
CASE WHEN num_b > 0 THEN 'b' ELSE 'a' END class
FROM
usr_b_sum
)
SELECT
class,
COUNT(DISTINCT user) count
FROM
usr_class_label
GROUP BY
class
ORDER BY
class ASCАльтернативное решение использует инструкции SELECT в операторах SELECT и UNION:
SELECT
"a" class,
COUNT(DISTINCT user_id) -
(SELECT COUNT(DISTINCT user_id) FROM table WHERE class = 'b') count
UNION
SELECT
"b" class,
(SELECT COUNT(DISTINCT user_id) FROM table WHERE class = 'b') count