SQL для аналитика. 50 задач с решениями на PostgreSQL

Оглавление

Группа авторов. SQL для аналитика. 50 задач с решениями на PostgreSQL

SQL для аналитика. 50 задач с решениями на PostgreSQL

Введение

Часть 1. Установка. Повторение SQL

Глава 1. Устанавливаем PostgreSQL и pgAdmin

Готовим рабочее место

Подготовка к установке

Установка PostgreSQL

Первое знакомство с pgAdmin

Что в итоге

Глава 2. Устанавливаем схему. Что есть в нашей схеме

Что скачиваем

Устанавливаем схему через pgAdmin

Проверка установки

Что есть в нашей схеме

Вопросы и ответы. А вдруг что-то пошло не так

Что в итоге

Глава 3. Повторяем базовый SQL (CREATE, INSERT, SELECT, UPDATE, DELETE)

Описание задачи

Основные SQL-конструкции

Основные типы данных в PostgreSQL

SQL-запросы с объяснением

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Глава 4. Повторяем продвинутый SQL (GROUP BY, JOIN, IN, BETWEEN)

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Глава 5. Повторяем UNION, INTERSECT, EXCEPT

Описание задачи

SQL-запросы с объяснением

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Часть 2. Продуктовая аналитика

Глава 6. CTE. Конверсия из регистрации в покупку

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Глава 7. JOIN, NOT EXISTS. Пользователи без покупок

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Глава 8. GROUP BY, AVG. Средний чек по категориям

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Глава 9. GROUP BY, COUNT. Повторные покупки

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Вопросы и ответы

* Задание со звёздочкой

Глава 10. GROUP BY, SUM. LTV пользователя

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Что в итоге

Глава 11. DATE_TRUNC, SUM, кросс-таблицы. Выручка по месяцам

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Глава 12. Аналитические функции. Топ-5 товаров

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Глава 13. Аналитические функции. Retention 7 дней

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Часть 3. Событийная аналитика. (7 задач)

Глава 14. COUNT (DISTINCT), CASE, CTE. Воронка событий

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Глава 15. LAG, EXTRACT. Среднее время между enter и buy

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Вопросы и ответы

* Задание со звёздочкой

Глава 16. JOIN, GROUP BY. Ошибки по странам

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Глава 17. ROW_NUMBER, LAG. Сессии пользователей

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Что в итоге

* Задание со звёздочкой

Глава 18. EXISTS, подзапросы. Пользователи с ошибкой, но потом купили

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Глава 19. DATE_TRUNC, COUNT (DISTINCT). DAU по дням

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Что в итоге

* Задание со звёздочкой

Глава 20. CASE. Bounce rate (одно действие за сессию)

Описание задачи

Основные SQL-конструкции

Что в итоге

* Задание со звёздочкой

Часть 4. Аналитика пользователей

Глава 21. DATE_TRUNC, COUNT, GROUP BY, сумма с накоплением. Новые пользователи по дням

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Что в итоге

* Задание со звёздочкой

Глава 22. COUNT, GROUP BY, LIMIT. Страна с самой высокой активностью

Описание задачи

Основные SQL-конструкции

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Глава 23. NOT EXISTS, INTERVAL, CURRENT_DATE. Неактивные пользователи

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Что в итоге

* Задание со звёздочкой

Глава 24. LEFT JOIN, IS NULL. Пользователи, которые зарегистрировались, но не заходили

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Глава 25. COUNT, GROUP BY, LIMIT. Пользователи с наибольшим количеством заказов

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Часть 5. Текстовая аналитика. (6 задач)

Глава 26. LIKE, ILIKE, регулярные выражения. Поиск заданных комментариев

Описание задачи

Аналогия из жизни

Основные SQL-конструкции

SQL-запросы с объяснением

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Глава 27. to_tsvector, to_tsquery. Поиск словосочетаний в комментариях

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Глава 28. ts_rank. Релевантность отзывов

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Что в итоге

* Задание со звёздочкой

Глава 29. AVG, GROUP BY, HAVING. Ищем товары с самым низким рейтингом

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Глава 30. GROUP BY rating. Распределение оценок

Описание задачи

Основные SQL-конструкции

Что в итоге

* Задание со звёздочкой

Глава 31. LENGTH, ORDER BY LIMIT. Топ самых длинных отзывов

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Часть 6. Работаем с датами. (5 задач)

Глава 32. DATE_TRUNC. Пользователи по месяцам регистрации

Описание задачи

Аналогия из жизни

Основные SQL-конструкции

SQL-запросы с объяснением

Что в итоге

* Задание со звёздочкой

Глава 33. CURRENT_DATE, INTERVAL. Заказы за последнюю неделю

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

SELECT

Что в итоге

* Задание со звёздочкой

Глава 34. DATE (dt_tm). Самая активная дата по логам

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Глава 35. AVG, ROWS BETWEEN. Скользящее среднее выручки

Описание задачи

Основные SQL-конструкции

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Глава 36. MIN, GROUP BY. Покупки в первый день после регистрации

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Часть 7. JSONB

Глава 37. JSONB ->>, WHERE, GROUP BY. Вытащить page из log_data

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

SELECT

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Глава 38. jsonb_build_object. События с referrer = google

Описание задачи

Аналогия из жизни

Основные SQL-конструкции

Глава 39. GROUP BY, ORDER BY, LIMIT. Топ-3 страниц входа

SELECT

Что в итоге

* Задание со звёздочкой

Глава 40. jsonb_set, UPDATE. Обновляем поле page в JSONB

Описание задачи

SQL-запросы с объяснением

SELECT

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Часть 8. Продвинутые техники

Глава 41. LATERAL, Self JOIN. Топ-3 категорий и пары категорий

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Глава 42. Сложный INSERT. Создать заказ со списком товаров

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Глава 43. MERGE. Обновить остатки или вставить новый

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Глава 44. LAG. Предыдущее действие пользователя

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Глава 45. LEAD. Следующее действие пользователя

Описание задачи

Основные SQL-конструкции

Зачем это нужно?

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Часть 9. Доставка и платежи

Глава 46. AVG, DATE, вычитание дат. Среднее время доставки

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Глава 47. EXTRACT, CASE, WHERE. Заказы с доставкой более 5 дней

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Что в итоге

* Задание со звёздочкой

Глава 48. JOIN, GROUP BY, COUNT, ORDER BY. Распределение способов оплаты по странам

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Глава 49. DATE_TRUNC, ROUND. Средний платёж по месяцам

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Глава 50. COUNT (DISTINCT), HAVING. Пользователи, платившие разными способами

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Вопросы и ответы

Что в итоге

* Задание со звёздочкой

Часть 10. Корзина

Глава 51. COUNT (DISTINCT), JOIN, CTE, CASE. Конверсия из add_to_cart в buy

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

* Задание со звёздочкой

Глава 52. LAG, EXTRACT, AVG. Среднее время между add_to_cart и buy

Описание задачи

Основные SQL-конструкции

SQL-запросы с объяснением

Глава 53. Повторение материала. Итоговые задачи

Описание задач

Задача 1. Анализ пользовательского пути от регистрации до первой покупки

Что в итоге

Глава 53. Повторение материала. Итоговые задачи

Описание задач

Задача 1. Анализ пользовательского пути от регистрации до первой покупки

Задача 2. Топ-5 товаров по выручке с их отзывами

Задача 3. Когортный анализ удержания пользователей по месяцам

Эпилог

Приложение 1. Ответы к заданиям со звёздочкой

Приложение 2. Шпаргалка по основным командам SQL

Приложение 3. Шпаргалка по типам данных PostgreSQL

Приложение 4. Шпаргалка по оконным функциям

Приложение 5. Шпаргалка по регулярным выражениям

Приложение 6. Полезные расширения PostgreSQL

Приложение 7. Дополнительные материалы

Приложение 8. Список таблиц и связей

Отрывок из книги

моим коллегам-аналитикам

Тем, кто каждый день борется с хаосом данных

.....

Редактируем данные.

Сделаем всех пользователей из Москвы активными.

.....