Есть проект-приложение, в котором пользователи могут принимать заказы на работу в каких-то подключенных туда же проектах и выполнять там поставленные задачи за денежное вознаграждение. Есть в БД таблица с пользователями User
- user_id:bigint – id клиента уникальный
- login:text
- password_h:text
- nickname:text
- name:text
- created_dt:timestamp – дата регистрации в системе
- email:text
Есть таблица с настройками пользователя для приложения. Т.к. мы решили хранить всю историю изменения параметров настроек, то, когда пользователь что-то меняет (например, язык приложения) – создается новая строка, включающая измененный параметр. Но разработчики ошиблись, и решили сначала, что пользователя в настройках определяет его login, а потом взяли и стали класть туда user_id. Но поле login уже переименовать не получилось. User_settings
- creation_date:timestamp – дата добавления новых параметров настройки для пользователя
- login:text (на самом деле тут user_id)
- language:char(3) – язык приложения
- timezone:int – часовой пояс пользователя
- background_color:text – настройка для виджетов пользователя
Есть таблица с выполненными пользователем работами, где хранится информация о том, какие задачи были выполнены, когда начаты, когда закончены, проект, в которых задача ставилась и сколько денег пользователю за нее полагается. User_task
- task_id:bigint – уникальный идентификатор задачи в другой таблице
- user_id:bigint – идентификатор пользователя
- start_date:timestamp – дата начала выполнения задачи пользователем
- finish_date:timestamp – дата окончания выполнения задачи пользователем (может быть NULL, если задача еще не завершена)
- project_name:text – проект, в котором выполнялась задача (на самом деле тут только ссылка на проект, но для простоты задачи пусть лежит название)
- reward_rub:numeric – вознаграждение в рублях за эту задачу.
- Итак, есть задача: Необходимо раз в сутки собрать отчет, в котором будет указано для каждого пользователя:
Кто (login и user_id) сколько задач выполнил в текущем месяце (count_complited), с учетом только полных дней, сколько суммарно затратил времени (total_time), сколько в среднем тратил время на 1 задачу(avg_time), в скольких разных проектах поучаствовал (project_count), общая сумма вознаграждения(total_ reward), которая ему причитается за все выполненные задачи в текущем месяце, а также суммы дополнительно по каждому проекту одной строкой «Проект1: x.xx руб.; Проект2: y.yy руб.» в указанном формате (extended_info, можно не строкой, но так, что бы только 1 объектом все возвращалось).
Так же подтянуть в эту таблицу предпочитаемый background_color из последних настроек пользователя (чтобы отправляя потом данные клиенту, сразу было понятно, какой фон рисовать в письме) и email из настроек. Добавить количество задач, находящихся в обработке на данный момент (current_task_count).
Поля результирующей таблицы (собрать sql-скриптом).
User_id, login, email, count_complited, total_time, avg_time, project_count, total_ reward, extended_info, background_color, current_task_count. Время в таблице User_task указано в UTC.