Нормализација и подобрувања на дизајнот: SQL queries.sql

--Најново членство по член
SELECT c.clenid, c.ime, c.prezime, p.ime AS paket, m.start_datum
FROM clenovi c
JOIN LATERAL (
  SELECT m.*
  FROM clenstvo m
  WHERE m.clenid = c.clenid
  ORDER BY m.start_datum DESC
  LIMIT 1
) m ON true
JOIN paketi p ON p.paketid = m.paketid
WHERE m.start_datum <= current_date;
--Активни членства на даден ден со времетраење

--Топ пакети по број на активни членства денес
WITH period AS (
  SELECT m.clenid, m.paketid, m.start_datum,
         (m.start_datum + (tv.vremetraenjedenovi || ' days')::interval)::date AS end_datum
  FROM clenstvo m
  JOIN tip_vremetranje tv ON tv.vremetraenjeid = m.vremetraenjeid
)
SELECT c.clenid, c.ime, c.prezime, p.ime AS paket, period.start_datum, period.end_datum
FROM period
JOIN clenovi c ON c.clenid = period.clenid
JOIN paketi  p ON p.paketid = period.paketid
WHERE period.start_datum <= current_date
  AND current_date <  period.end_datum;

 
 WITH active AS (
  SELECT m.paketid
  FROM clenstvo m
  JOIN tip_vremetranje tv ON tv.vremetraenjeid = m.vremetraenjeid
  WHERE m.start_datum <= current_date
    AND current_date < (m.start_datum + (tv.vremetraenjedenovi || ' days')::interval)::date
)
SELECT p.ime AS paket, COUNT(*) AS broj_aktivni
FROM active a
JOIN paketi p ON p.paketid = a.paketid
GROUP BY p.ime
ORDER BY broj_aktivni DESC;

--Членови без посета во последни 30 дена
SELECT c.clenid, c.ime, c.prezime
FROM clenovi c
LEFT JOIN LATERAL (
  SELECT 1
  FROM poseti po
  WHERE po.clenid = c.clenid
    AND po.datum >= current_date - INTERVAL '30 days'
  LIMIT 1
) x ON true
WHERE x IS NULL;
--Последна посета по локација и член
SELECT c.clenid, c.ime, l.ime AS lokacija, max(po.datum) AS last_visit
FROM poseti po
JOIN clenovi c ON c.clenid = po.clenid
JOIN lokacii l ON l.lokacijaid = po.lokacijaid
GROUP BY c.clenid, c.ime, l.ime
ORDER BY last_visit DESC;