12+
Оглавление - 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. Список таблиц и связей