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

-- 1) Страна, где Казань
SELECT co.name
FROM cities ci JOIN regions r ON r.id=ci.region_id
JOIN countries co ON co.id=r.country_id
WHERE ci.name = 'Казань';
 
-- 2) Города в Московской области
SELECT COUNT(*) FROM cities ci JOIN regions r ON r.id=ci.region_id
WHERE r.name = 'Московская область';
 
-- 3) Уборки снега 12.2020–02.2021
SELECT COUNT(*) FROM events e JOIN types t ON t.id=e.type_id
WHERE t.name = 'Уборка снега'
  AND e.date BETWEEN DATE '2020-12-01' AND DATE '2021-02-28';
 
-- 4) Городское население региона
SELECT r.name, SUM(ci.population) AS urban_pop
FROM cities ci JOIN regions r ON r.id=ci.region_id
GROUP BY r.name;
 
-- 5) Уборки снега в Москве за 3 года
SELECT COUNT(*) FROM events e
JOIN cities ci ON ci.id = e.city_id
JOIN types t ON t.id = e.type_id
WHERE ci.name='Москва' AND t.name='Уборка снега'
  AND e.date >= CURRENT_DATE - INTERVAL '3 years';
 
-- 6) Средние траты по типу события в СПб за 5 лет
SELECT t.name AS type, AVG(e.costs) AS avg_cost
FROM events e
JOIN cities ci ON ci.id=e.city_id
JOIN types t   ON t.id=e.type_id
WHERE ci.name='Санкт-Петербург'
  AND e.date >= CURRENT_DATE - INTERVAL '5 years'
GROUP BY t.name;
 
-- 7) Среднее время между одинаковыми событиями по городам
WITH ord AS (
  SELECT
    e.city_id, e.type_id, e.date,
    LAG(e.date) OVER (PARTITION BY e.city_id, e.type_id ORDER BY e.date) AS prev_date
  FROM events e
)
SELECT ci.name, t.name,
       AVG(EXTRACT(EPOCH FROM (date - prev_date))/86400) AS avg_days_between
FROM ord
JOIN cities ci ON ci.id=ord.city_id
JOIN types t   ON t.id=ord.type_id
WHERE prev_date IS NOT NULL
GROUP BY ci.name, t.name;
 
-- 8) Стоимость трат на 1 человека в год по региону × тип события
WITH yearly AS (
  SELECT r.id AS region_id, t.name AS type, SUM(e.costs) AS total,
         COUNT(DISTINCT EXTRACT(YEAR FROM e.date)) AS years
  FROM events e
  JOIN cities ci ON ci.id=e.city_id
  JOIN regions r ON r.id=ci.region_id
  JOIN types t   ON t.id=e.type_id
  GROUP BY r.id, t.name
),
pop AS (
  SELECT region_id, SUM(population) AS pop FROM cities GROUP BY region_id
)
SELECT r.name AS region, y.type,
       y.total / y.years / NULLIF(p.pop, 0) AS cost_per_person_per_year
FROM yearly y
JOIN pop p     ON p.region_id = y.region_id
JOIN regions r ON r.id = y.region_id;