Проблемы с запросом SQL

Question

Всем привет. Дали тестовое задание по SQL, выглядит оно так:

Вам дана таблица data.csv со следующей схемой:
clientId - уникальный идентификатор клиента
sessionId - уникальный идентификатор сессии
time - время создания сессии
revenue - выручка от транзакций в сессии
transactions - количество транзакций в сессии
source - источник трафика
medium - канал трафика
operatingSystem - операционная система клиента в сессии

Вашей задачей является получить сводную таблицу относительно новых полей channel и device со схемой:
channel - поле, которое сочетает в себе источник трафика (source) и канал (medium) с видом “{source} / {medium}”
device - поле, которое принимает три возможных значения: 
mobile (если операционная система = 'Android' | 'iOS' | 'Windows Phone') 
desktop (если операционная система = 'Windows' | 'Mac OS X' | 'Linux' | 'Ubuntu' | 'Chrome OS')
other в остальных случаях
trans_count - (transaction count) количество транзакций
avg_time_b_trans1 - (average time between transactions) среднее время между транзакциями клиентов 
sum_revenue - (summation of revenue) суммарная выручка
avg_revenue2 - (average revenue) средняя выручка по сессиям

Результирующая таблица должна быть отсортирована по полю trans_count.
Ответом на задание является SQL запрос на диалекте Standard SQL, который позволяет получить указанную таблицу. Вы можете проверить правильность выполнения задания, сверив ваш результат с таблицей result.csv.


1 Время между транзакциями рассчитывается как количество часов между любой сессией клиента с транзакцией и его первой предыдущей сессией (по времени), в которой тоже была транзакция. Затем это время усредняется для каждого клиента по channel. 
2 Средняя выручка сначала рассчитывается индивидуально для каждого клиента по channel.

Файлы выложил на диск - https://disk.yandex.ru/d/P1AaGZvJM_sYhQ

Сразу скажу, что SQL я почти не владею. Начал его изучать буквально вчера, и поэтому есть трудности, а именно:

Не получается вывести avg_revenue - пытаюсь совершать какие-то вложенные запросы, но выдается ошибка Подзапрос возвращает более одной записи. Так что я определенно что-то делаю неправильно, но не знаю что
В принципе понятия не имею, как вывести avg_time_b_trans1, пока даже нет идей

Если возможно, прошу помощи по обоим пунктам. Или хотябы только по первому

Мой код:

ALTER TABLE data_1 ADD COLUMN channel VARCHAR(60), ADD COLUMN device VARCHAR(8);

UPDATE data_1
SET channel = CONCAT(source, ' / ', medium),
    device = IF(operatingSystem IN('Android', 'iOS', 'Windows Phone'), 'mobile', IF(operatingSystem IN('Windows', 'Mac OS X', 'Linux', 'Ubuntu', 'Chrome OS'), 'desktop', 'other'))
;

SELECT channel, device,
       SUM(transactions) AS trans_count,
       ROUND(SUM(revenue), 2) AS sum_revenue,
       AVG((SELECT AVG(revenue) /* Здесь пытаюсь посчитать среднее средних по clientId, channel и device, но происходит ошибка */
            FROM data_1
            GROUP BY clientId, channel, device)) AS avg_revenue
FROM data_1
GROUP BY channel, device
ORDER BY trans_count DESC
LIMIT 20

БЛОГ НА HUSL

Проблемы с запросом SQL

Ответы (0 шт):