Script-ITOG.sql - master - PozdnyakovaLN/Example3

Script-ITOG.sql
229 строк12 KB
PozdnyakovaLN
update: Script-ITOG.sql
28 май 2026, 14:30
28 май 2026, 14:303a468c8
7004--Итоговый модуль по курсу «SQL и получение данных» (Минимум 110 баллов, 85 баллов данная работа)

--Задания:
--1. Получите количество проектов, подписанных в 2023 году. В результат вывести одно значение количества. (5 баллов)

select count(project_id) as count_project
from project p 
where sign_date::date between '01-01-2023' and '31-12-2023'

--2. Получите общий возраст сотрудников, нанятых в 2022 году
--Результат вывести одним значением в виде "... years ... months ... days"
--Использование более 2х функций для работы с типом данных дата и время будет являться ошибкой. (5 баллов)

select sum(age(birthdate::date)) as general_age_emp
from employee e 
join person p on p.person_id = e.person_id
where hire_date::date between '01-01-2022' and '31-12-2022'

--3. Получите сотрудников, у которых фамилия начинается на М, всего в фамилии 8 букв и который работает дольше других.
--Если таких сотрудников несколько, выведите одного случайного.
--В результат выведите два столбца, в первом должны быть имя и фамилия через пробел, во втором дата найма. (10 баллов)

--Комментарий преподавателя: Отсутствует получение сотрудника у которого в фамилии 8 символов.

select 
   concat(first_name, ' ', last_name) as employee_name,
   hire_date 
from employee e
join person p on e.person_id = p.person_id
where last_name like 'М_______' 
order by hire_date asc 
limit 1

--4. Получите среднее значение полных лет сотрудников, которые уволены и не задействованы на проектах.
--В результат вывести одно среднее значение. Если получаете null, то в результат нужно вывести 0. (15 баллов)

select 
   coalesce (round(avg(extract(year from age(current_date, p.birthdate))), 2), 0) as average_age  
from employee e
join person p on e.person_id = p.person_id
where dismissal_date is not null and employee_id not in (
   select unnest(array_append(employees_id, project_manager_id))
   from project p)
    
--5. Чему равна сумма полученных платежей от контрагентов из Жуковский, Россия.
--В результат вывести одно значение суммы. (15 баллов)
  
--Комментарий преподавателя: Минус 5 баллов. Вопрос о полученных платежах, а не всех.
  
select coalesce (sum (amount), 0) as total_amount 
from project_payment pp 
join project p on p.project_id = pp.project_id 
join customer c on c.customer_id = p.customer_id 
join address a on a.address_id = c.address_id 
join city c2 on c2.city_id = a.city_id 
join country c3 on c3.country_id =c2.country_id 
where city_name = 'Жуковский' and country_name = 'Россия' 
and fact_transaction_timestamp is not null


--6. Пусть руководитель проекта получает премию в 1% от стоимости завершенных проектов.
--Если взять завершенные проекты, какой руководитель проекта получит самый большой бонус?
--В результат нужно вывести идентификатор руководителя проекта, его ФИО и размер бонуса.
--Если таких руководителей несколько, предусмотреть вывод всех. (15 баллов)

--Комментарий преподавателя: Минус 10 баллов. Вопрос об общей премии, а не по каждому проекту в отдельности.

select 
   p.project_manager_id,
   pm.first_name || ' ' || pm.last_name as manager_name,
   sum(p.project_cost) * 0.01 as total_bonus
from project p
join person pm on p.project_manager_id = pm.person_id
where p.status = 'Завершен'
group by p.project_manager_id, pm.first_name, pm.last_name
having sum(p.project_cost) * 0.01 = (
   select max(bonus)
   from (
      select sum(project_cost) * 0.01 as bonus
      from project
      where status = 'Завершен'
      group by project_manager_id) as bonuses)
order by total_bonus desc

--7. Получите накопительный итог планируемых авансовых платежей на каждый месяц в отдельности.
--Выведите в результат те даты планируемых платежей, которые идут после преодаления накопительной 
--суммой значения в 30 000 000 (20 баллов)
--Пример:
--дата		накопление
--2022-06-14	28362946.20
--2022-06-20	29633316.30
--2022-06-23	34237017.30
--2022-06-24	46248120.30
--В результат должна попасть дата 2022-06-23

  with monthly_cumulative as (
  select 
    plan_payment_date as date_month,
    sum(amount) over (partition by date_trunc('month', plan_payment_date) order by plan_payment_date) as cumulative_sum
  from project_payment
  where payment_type = 'Авансовый'
)
select to_char (date_month, 'YYYY-MM-DD'), cumulative_sum
from (
  select *, row_number () over (partition by date_trunc ('month', date_month) order by date_month) as rnk
  from monthly_cumulative
  where cumulative_sum > 30000000
) as ranked
where rnk = 1
order by date_month
 
  --8. Используя рекурсию посчитайте сумму фактических окладов сотрудников из структурного подразделения с id равным 17 
--и всех дочерних подразделений. В результат вывести одно значение суммы. (15 баллов)

with recursive cte1 as (
  select *, 0 as level
	from company_structure cs
	where unit_id = 17
	union 
	select cs.*, level + 1 as level
	from cte1 
	join company_structure cs on cte1.unit_id = cs.parent_id),
cte2 as (
  select*
  from employee_position ep
  join position p on ep.position_id = p.position_id
  where p.unit_id in (select unit_id from cte1))
   select
   sum(salary * rate) as total_salary
from cte2 

--9. Задание выполняется одним запросом.
--Сделайте сквозную нумерацию фактических платежей по проектам на каждый год в отдельности в порядке даты платежей.
--Получите платежи, сквозной номер которых кратен 5.
--Выведите скользящее среднее размеров платежей с шагом 2 строки назад и 2 строки вперед от текущей.
--Получите сумму скользящих средних значений.
--Получите сумму стоимости проектов на каждый год.
--Выведите в результат значение года (годов) и сумму проектов, где сумма проектов меньше, чем сумма скользящих средних значений. (30 баллов)

--Комментарий преподавателя: Отсутствует работа с фактическими платежами.
--В задании не сказано о получении скользящих средних на каждый проект в отдельности.
--Отсутствует получение суммы стоимости проектов на каждый год.
--Каждая таблица должна быть использована не более 1 раза.
--Ложная группировка в отсутствие агрегации.

with cte1 as (
  select
    extract (year from pp.fact_transaction_timestamp) as payment_year,
    pp.amount,
    row_number() over (
      partition by extract(year from pp.fact_transaction_timestamp)
      order by pp.fact_transaction_timestamp) as seq_num,
    avg(pp.amount) over (
      partition by extract(year from pp.fact_transaction_timestamp)
      order by pp.fact_transaction_timestamp
      rows between 2 preceding and 2 following) as moving_avg
  from project_payment pp
  where pp.payment_type is not null),
cte2 as (
  select
    payment_year,
    sum(moving_avg) as sum_moving_avg
  from cte1
  where seq_num % 5 = 0
  group by payment_year),
cte3 as (
  select
    extract(year from p.sign_date) as project_year,
    sum(p.project_cost) as total_project_cost
  from project p
  group by extract(year from p.sign_date))
select
  cte3.project_year as year,
  cte3.total_project_cost as project_cost_sum
from cte3 
join cte2 on cte3.project_year = cte2.payment_year
where cte3.total_project_cost < cte2.sum_moving_avg
order by cte3.project_year

--10. Создайте материализованное представление, которое будет хранить отчет следующей структуры:
--идентификатор проекта
--название проекта
--дата последней фактической оплаты по проекту
--размер последней фактической оплаты
--ФИО руководителей проектов
--Названия контрагентов
--В виде строки названия типов работ по каждому контрагенту (20 баллов)

--Комментарий преподавателя: Минус 10 баллов.
--Отсутствует работа с фактическими платежами.
--Каждая таблица должна быть использована не более 1 раза.

create materialized view project_table as
with cte1 as (
  select
    project_id,
    fact_transaction_timestamp as payment_date,
    amount as payment_amount,
    row_number() over (partition by project_id order by fact_transaction_timestamp desc) as rn
  from project_payment
  where payment_type is not null),
cte2 as (
  select
    p.project_id,
    c.customer_name,
    string_agg(tw.type_of_work_name, ', ') as work_types
  from project p
  join customer c on p.customer_id = c.customer_id
  join customer_type_of_work ctw on c.customer_id = ctw.customer_id
  join type_of_work tw on ctw.type_of_work_id = tw.type_of_work_id
  group by p.project_id, c.customer_name),
cte3 as (
  select
    e.employee_id as manager_id,
    full_fio
  from employee e
  join person per on e.person_id = per.person_id)
select
  p.project_id,
  p.project_name,
  cte1.payment_date,
  cte1.payment_amount,
  cte3.full_fio as manager_fio,
  cte2.customer_name,
  cte2.work_types
from project p
join cte1 on p.project_id = cte1.project_id and cte1.rn = 1
join cte2 on p.project_id = cte2.project_id
join cte3 on p.project_manager_id = cte3.manager_id