Условие

Пять SQL-задач для junior-аналитика в EdTech:

Из orders(order_id, course_id, user_id, order_date, price) — выручка по каждому курсу за последний месяц.
Из users(user_id, name, email, registration_date) — кол-во новых пользователей в день за последние 7 дней.
Из courses(course_id, course_name) и ratings(rating_id, course_id, user_id, rating) — название курса + средняя оценка для курсов с числом оценок > 10.
Из user_courses(user_id, course_id, start_date, end_date) — кол-во активных пользователей на дату X (хотя бы один current course).
Из events(event_id, user_id, event_type, event_date) — пользователи, купившие курс (course_purchase), но не завершившие (course_complete) в течение 30 дней.

Решение

1. Выручка по курсу за месяц

SELECT course_id,
       SUM(price) AS revenue
FROM orders
WHERE order_date >= NOW() - INTERVAL '1 month'
GROUP BY course_id
ORDER BY revenue DESC;

2. Новые пользователи по дням

SELECT DATE(registration_date) AS reg_day,
       COUNT(*)                AS new_users
FROM users
WHERE registration_date >= NOW() - INTERVAL '7 day'
GROUP BY reg_day
ORDER BY reg_day;

3. Курс + средняя оценка (рейтингов > 10)

SELECT c.course_name,
       AVG(r.rating) AS avg_rating,
       COUNT(*)       AS n_ratings
FROM ratings r
JOIN courses c ON c.course_id = r.course_id
GROUP BY c.course_name
HAVING COUNT(*) > 10
ORDER BY avg_rating DESC;

4. Активные пользователи на дату

SELECT COUNT(DISTINCT user_id) AS active
FROM user_courses
WHERE DATE '2024-05-15' BETWEEN start_date AND end_date;

5. Купили, но не завершили за 30 дней

WITH purchases AS (
  SELECT user_id, course_id_or_event AS course_id, MIN(event_date) AS bought_at
  FROM events
  WHERE event_type = 'course_purchase'
  GROUP BY user_id, course_id
),
completes AS (
  SELECT user_id, course_id, MIN(event_date) AS completed_at
  FROM events
  WHERE event_type = 'course_complete'
  GROUP BY user_id, course_id
)
SELECT COUNT(DISTINCT p.user_id) AS users_not_completed
FROM purchases p
LEFT JOIN completes c
  ON c.user_id = p.user_id AND c.course_id = p.course_id
WHERE c.completed_at IS NULL
  OR c.completed_at > p.bought_at + INTERVAL '30 day';

Подводные камни

«Последний месяц» — calendar month или 30 дней? Уточняйте.
HAVING COUNT(*) > 10 — строго больше или ≥ 10? «Больше 10».
Активные пользователи на дату: BETWEEN start_date AND end_date — закрытый интервал.
Не завершили за 30 дней = completed_at > bought_at + 30 ИЛИ completed_at IS NULL. Часто забывают второй случай.
events.course_id не указано в задании — нужно либо предположить, что есть, либо использовать event_id JOIN. Уточните.
Дубли purchase: если пользователь купил курс дважды — берётся MIN(event_date).

Эталонный ответ

(1) GROUP BY course + SUM(price) за месяц. (2) GROUP BY DATE(reg) за 7 дней. (3) JOIN + GROUP BY + HAVING COUNT > 10. (4) BETWEEN start AND end на дату. (5) LEFT JOIN purchases ↔ completes с условием completed > purchase + 30 OR NULL.

Skypro Junior — набор SQL-задач для онлайн-образования