---

SET search_path = study;

-- 3.1.1. Для каждого месяца 2024 года выведите количество проектов

WITH months AS (
    SELECT 'январь' AS month_nm, 1 AS month_nus
    UNION ALL SELECT 'февраль', 2
    UNION ALL SELECT 'март', 3
    UNION ALL SELECT 'апрель', 4
    UNION ALL SELECT 'май', 5
    UNION ALL SELECT 'июнь', 6
    UNION ALL SELECT 'июль', 7
    UNION ALL SELECT 'август', 8
    UNION ALL SELECT 'сентябрь', 9
    UNION ALL SELECT 'октябрь', 10
    UNION ALL SELECT 'ноябрь', 11
    UNION ALL SELECT 'декабрь', 12
)
SELECT m.month_nus,
       m.month_nm,
       COUNT(p.id) AS project_cnt
FROM months m
LEFT JOIN projects p
       ON m.month_nus = EXTRACT(MONTH FROM p.start_date)
      AND EXTRACT(YEAR FROM p.start_date) = 2024
GROUP BY m.month_nus, m.month_nm
ORDER BY m.month_nus;


-- 3.2.1. Расставьте порядковый номер проекта для каждого дня

SELECT id,
       project_name,
       start_date,
       ROW_NUMBER() OVER (
           PARTITION BY DATE_TRUNC('month', start_date)
           ORDER BY EXTRACT(DAY FROM start_date), id
       ) AS rn
FROM projects
ORDER BY start_date, rn;


-- 3.2.2. Измените запрос с применением функций RANK(), DENSE_RANK(), NTILE()

SELECT id,
       project_name,
       start_date,
       ROW_NUMBER() OVER w AS rn,
       RANK() OVER w AS rnk,
       DENSE_RANK() OVER w AS dense_rnk,
       NTILE(3) OVER w AS ntile_3
FROM projects
WINDOW w AS (
    PARTITION BY DATE_TRUNC('month', start_date)
    ORDER BY EXTRACT(DAY FROM start_date)
)
ORDER BY start_date, rn;


-- 3.2.3. Напишите аналог запроса из п. 3.1.1 с применением оконной функции

WITH months AS (
    SELECT 'январь' AS month_nm, 1 AS month_nus
    UNION ALL SELECT 'февраль', 2
    UNION ALL SELECT 'март', 3
    UNION ALL SELECT 'апрель', 4
    UNION ALL SELECT 'май', 5
    UNION ALL SELECT 'июнь', 6
    UNION ALL SELECT 'июль', 7
    UNION ALL SELECT 'август', 8
    UNION ALL SELECT 'сентябрь', 9
    UNION ALL SELECT 'октябрь', 10
    UNION ALL SELECT 'ноябрь', 11
    UNION ALL SELECT 'декабрь', 12
)
SELECT DISTINCT
       m.month_nus,
       m.month_nm,
       COUNT(p.id) OVER (
           PARTITION BY m.month_nus
       ) AS project_cnt
FROM months m
LEFT JOIN projects p
       ON m.month_nus = EXTRACT(MONTH FROM p.start_date)
      AND EXTRACT(YEAR FROM p.start_date) = 2024
ORDER BY m.month_nus;


-- 3.2.4. Напишите оконную функцию для присвоения номера каждому студенту в группе с сортировкой по фамилии и имени

SELECT s.group_number,
       ROW_NUMBER() OVER (
           PARTITION BY s.group_number
           ORDER BY s.last_name, s.first_name
       ) AS num_in_group,
       s.last_name,
       s.first_name
FROM students s
ORDER BY s.group_number, num_in_group;


-- 3.2.5. Найдите в каждом месяце начало работы над проектами указанной команды

SELECT DISTINCT
       c.team,
       DATE_TRUNC('month', p.start_date)::date AS month_start,
       FIRST_VALUE(p.project_name) OVER w AS first_project,
       FIRST_VALUE(p.start_date) OVER w AS work_start,
       COUNT(*) OVER (
           PARTITION BY DATE_TRUNC('month', p.start_date)
       ) AS projects_in_month
FROM projects p
JOIN team c ON c.idkom = p.idkom
WHERE p.idkom = 1
WINDOW w AS (
    PARTITION BY DATE_TRUNC('month', p.start_date)
    ORDER BY p.start_date, p.id
)
ORDER BY month_start;


-- 3.2.6. Найдите разницу между стоимостью проекта и средней стоимостью всех проектов

SELECT id,
       project_name,
       price::numeric AS price,
       ROUND(AVG(price::numeric) OVER (), 2) AS avg_price,
       ROUND(
           price::numeric - AVG(price::numeric) OVER (),
           2
       ) AS diff_from_avg
FROM projects
ORDER BY diff_from_avg DESC;


-- 3.2.7. Рассчитайте ранг проекта по баллам и накопительную сумму баллов в рамках трека с использованием поименованного окна

WITH project_score AS (
    SELECT p.id,
           p.project_name,
           p.project_type,
           COALESCE(SUM(t.score), 0) AS total_score
    FROM projects p
    LEFT JOIN task t ON t.idproject = p.id
    GROUP BY p.id, p.project_name, p.project_type
)
SELECT project_type AS track,
       project_name,
       total_score,
       RANK() OVER w AS rank_in_track,
       SUM(total_score) OVER (
           w ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS cum_score_in_track
FROM project_score
WINDOW w AS (
    PARTITION BY project_type
    ORDER BY total_score DESC
)
ORDER BY track, rank_in_track;


-- 3.2.8. Рассчитайте нарастающую сумму баллов проектов с использованием фреймов ROWS и RANGE

WITH project_score AS (
    SELECT p.id,
           p.project_name,
           COALESCE(SUM(t.score), 0) AS total_score
    FROM projects p
    LEFT JOIN task t ON t.idproject = p.id
    GROUP BY p.id, p.project_name
)
SELECT project_name,
       total_score,
       SUM(total_score) OVER (
           ORDER BY total_score, id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS cum_rows,
       SUM(total_score) OVER (
           ORDER BY total_score
           RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS cum_range
FROM project_score
ORDER BY total_score, id;


-- 3.2.9. Найдите накопительную сумму стоимости проектов для каждого статуса

SELECT status,
       project_name,
       start_date,
       price::numeric AS price,
       SUM(price::numeric) OVER (
           PARTITION BY status
           ORDER BY start_date, id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS cum_price
FROM projects
ORDER BY status, start_date, id;


-- 3.2.10. Рассчитайте скользящее среднее оценок проектов за текущий и два предыдущих проекта по дате начала

WITH project_score AS (
    SELECT p.id,
           p.project_name,
           p.start_date,
           AVG(t.score::numeric) AS avg_score
    FROM projects p
    LEFT JOIN task t ON t.idproject = p.id
    GROUP BY p.id, p.project_name, p.start_date
)
SELECT project_name,
       start_date,
       avg_score,
       ROUND(
           AVG(avg_score) OVER (
               ORDER BY start_date, id
               ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
           ),
           2
       ) AS moving_avg_3
FROM project_score
ORDER BY start_date, id;


-- 3.2.12. Найдите сумму стоимости и среднюю оценку проектов за последние три месяца относительно даты начала проекта

WITH project_score AS (
    SELECT p.id,
           p.project_name,
           p.start_date,
           p.price,
           AVG(t.score::numeric) AS avg_score
    FROM projects p
    LEFT JOIN task t ON t.idproject = p.id
    GROUP BY p.id, p.project_name, p.start_date, p.price
)
SELECT project_name,
       start_date,
       price,
       avg_score,
       SUM(price) OVER w3 AS sum_price_3m,
       ROUND(AVG(avg_score) OVER w3, 2) AS avg_score_3m,
       COUNT(*) OVER w3 AS projects_3m
FROM project_score
WINDOW w3 AS (
    ORDER BY start_date
    RANGE BETWEEN INTERVAL '3 months' PRECEDING AND CURRENT ROW
)
ORDER BY start_date;