Нужно sql командами получить среднее количество покупок в месяц для каждого курса за 2018 год

Question

Нужно написать код, который выведет среднее количество покупок для каждого курса, учитывая диапазон месяцев, в течение которого были продажи. Например, Веб-разработчик c 0 до PRO продавался с 1го до 8го месяца, в течение этого периода совершилось 6 продаж, то есть среднее кол-во продаж 0,75.

В исходной таблице PurchaseList поля student_name, course_name, subscription_date(дата регистрации студента и покупки курса такого формата 2018-01-02 00:00:00).

Написала код, получающий все покупки по месяцам (имя курса - месяц) Нужен только месяц, так как считаем только за один год.

import java.sql.*;

public class Main {

    private static String url = "jdbc:mysql://localhost:3306/skillbox";
    private static String user = "root";
    private static String pass = "testtest";

    public static void main(String[] args) throws SQLException {
        Connection connection1 = DriverManager.getConnection(url, user, pass);
        Statement statement1 = connection1.createStatement();
        ResultSet resultSet1 = statement1.executeQuery("SELECT course_name, MONTH(subscription_date) subscription_date FROM PurchaseList");
        while (resultSet1.next()) {
            String result = resultSet1.getString("course_name") + " - " + resultSet1.getString("subscription_date");
            System.out.println(result);

        }
}

Веб-разработчик c 0 до PRO - 1
Веб-разработчик c 0 до PRO - 5
Веб-разработчик c 0 до PRO - 2
Веб-разработчик c 0 до PRO - 8
Веб-разработчик c 0 до PRO - 7
Веб-разработчик c 0 до PRO - 4
Мобильный разработчик с нуля - 1
Мобильный разработчик с нуля - 1
Мобильный разработчик с нуля - 5
Мобильный разработчик с нуля - 2
Мобильный разработчик с нуля - 8
Мобильный разработчик с нуля - 7
Мобильный разработчик с нуля - 4
Java-разработчик - 1
Java-разработчик - 5
Java-разработчик - 2
Java-разработчик - 8
Java-разработчик - 7

А нужно

Веб-разработчик c 0 до PRO - 0,75 (6/8)
Мобильный разработчик с нуля - 0,875 (7/8) и т.д.

Понимаю, что нужно для каждого множества одинаковых имён выделить MIN и MAX в subscription_date и разделить количество повторений имён на (MAX-MIN). Подскажите, как красиво это написать?

Answer 1

Сделала!!

import java.sql.*;

public class Main {

    private static String url = "jdbc:mysql://localhost:3306/skillbox";
    private static String user = "root";
    private static String pass = "testtest";

    public static void main(String[] args) throws SQLException {
        Connection connection1 = DriverManager.getConnection(url, user, pass);
        Statement statement1 = connection1.createStatement();
        ResultSet resultSet2 = statement1.executeQuery("SELECT course_name, COUNT(course_name)/(MAX(MONTH(subscription_date)) - MIN(MONTH(subscription_date))) AS Coefficient FROM PurchaseList GROUP BY course_name");
        while (resultSet2.next()) {
            String result = resultSet2.getString("course_name") + " - " + resultSet2.getString("Coefficient");
            System.out.println(result);

        }
        resultSet2.close();
        statement1.close();
        connection1.close();
    }
}

Результат:

Веб-разработчик c 0 до PRO - 0.8571
Мобильный разработчик с нуля - 1.0000
Java-разработчик - 0.8571
PHP-разработчик с 0 до PRO - 0.8571
Python-разработчик с нуля - 0.8571
Frontend-разработчик - 0.8571
Мобильный разработчик PRO - 0.8571
Программист 1С-Битрикс - 0.8571
С#-разработчик с нуля - 0.8571
Data Scientist с 0 до PRO - 0.8571
Аналитик данных на Python - 0.8571
Веб-дизайн с нуля до PRO - 0.8571
Рекламная графика - 0.8571
UX-дизайн - 0.8571
Анимация интерфейсов - 0.8571
Дизайн интерьеров с нуля - 0.8571
Графический дизайнер с нуля до PRO - 0.8571
Дизайн мобильных приложений - 0.7500
Промо-сайты от Red Collar - 0.7500
Скетчинг для дизайнеров - 0.7500
Photoshop c 0 до PRO - 0.7500
Sound-design - 0.7500
Cinema4D - 0.7500
Sketch - 0.7500
Figma - 0.7500
Интернет-маркетолог от Ingate - 0.7500
SMM-маркетолог от А до Я - 0.7500
Performance-маркетинг - 0.7500
Интернет-маркетолог от А до Я - 0.7500
Продвижение в Instagram - 0.7500
Сквозная аналитика - 0.8571
Таргетолог от А до Я - 0.8571
UX-аналитик - 0.8571
Управление репутацией - 0.8571
Как делать контент для YouTube - 0.8571
SEO-специалист - 0.8571
Вирусный маркетинг - 0.8571
CRM-маркетолог - 0.8571
Как открыть веб-студию - 0.8571
Как открыть салон красоты - 0.8571
Управление командами - 0.8571
Управление Digital-проектами - 0.8571
Excel - 0.8571
Продюсер онлайн-курсов - 0.8571
Управление продуктом - 0.8571

Process finished with exit code 0

Answer 2

SELECT
    name, COUNT(name) / ((MAX(month(date)-MIN(month(date))) + 1) coeff
FROM purchaselist
GROUP BY name

"+1" чтобы не потерялся один из месяцев.

БЛОГ НА HUSL

Нужно sql командами получить среднее количество покупок в месяц для каждого курса за 2018 год

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