Практическое задание

Эта задача — шаг 15 курса «SQL на SQLite»

SQLite: общее табличное выражение (CTE)

средне~7 мин

В базе /root/pay.db есть таблица payments(user TEXT, amount INTEGER) с платежами. Нужно найти «VIP»-пользователей, у которых суммарная сумма платежей строго больше 100. Составь запрос с общим табличным выражением (WITH ... AS (...)): сначала во вспомогательной таблице посчитай сумму по каждому пользователю, затем отбери из неё тех, у кого сумма > 100. Запиши имена в /root/vip.txt (по одному в строке).

Запрос с CTE удобнее писать в несколько строк: набери его в файле /root/vip.sql во вкладке «Редактор» и выполни в sqlite3.

Что потренируешь:
  • cte
  • with
  • агрегация
  • group by
  • фильтрация по агрегату

Разбор темы

Общее табличное выражение (CTE) — это именованный подзапрос, объявленный через WITH <имя> AS (...) до основного запроса; к нему потом обращаются по имени, как к обычной таблице. Такой приём удобен, когда нужно отфильтровать данные по агрегату: в WHERE агрегатные функции использовать нельзя, поэтому сумму сначала считают во вложенном шаге (GROUP BY + агрегат с псевдонимом), а внешний запрос уже фильтрует готовый результат. Альтернатива CTE для фильтра по агрегату — HAVING, но WITH делает многоступенчатую логику читабельнее.

Что проверяется

  1. в отчёте ровно VIP-пользователи
  2. запрос с WITH лежит в /root/vip.sql

Как это выглядит