# Advanced Reports

## Најуспешни книги според продажба и приход

### Data requirements description

Потребно е да се добие аналитички извештај кој ќе прикаже кои книги имаат најголема продажба и најголем остварен приход врз основа на реализираните нарачки.

Извештајот ги користи податоците од книгите, ставките во нарачките и нарачките. За секоја продадена книга се пресметуваат вкупниот број на продадени примероци, вкупниот приход, бројот на реализирани нарачки и просечниот приход од ставките поврзани со книгата.

Овој извештај може да се користи за анализа на успешноста на книгите на подолг временски период и за следење на продажбата и приходите во BiblioPremium.

### Solution SQL

SELECT
    k.kniga_id,
    k.naslov,
    k.isbn,
    SUM(s.kolicina) AS vkupno_prodadeni,
    SUM(s.kolicina * s.edinechna_cena) AS vkupen_prihod,
    COUNT(DISTINCT n.naracka_id) AS broj_naracki,
    ROUND(
        AVG(s.kolicina * s.edinechna_cena),
        2
    ) AS prosechen_prihod_od_stavka
FROM project.knigi k
JOIN project.sodrzi s
    ON k.kniga_id = s.kniga_id
JOIN project.naracki n
    ON s.naracka_id = n.naracka_id
WHERE n.status = 'ZAVRSENA'
GROUP BY
    k.kniga_id,
    k.naslov,
    k.isbn
HAVING SUM(s.kolicina) > 0
ORDER BY
    vkupen_prihod DESC;

### Solution Relational Algebra

R1 ← KNIGI ⋈ KNIGI.kniga_id = SODRZI.kniga_id SODRZI

R2 ← R1 ⋈ SODRZI.naracka_id = NARACKI.naracka_id
      σ(status = 'ZAVRSENA')(NARACKI)

R3 ← γ
      kniga_id, naslov, isbn;
      SUM(kolicina) → vkupno_prodadeni,
      SUM(kolicina × edinechna_cena) → vkupen_prihod,
      COUNT(DISTINCT naracka_id) → broj_naracki,
      AVG(kolicina × edinechna_cena) → prosechen_prihod_od_stavka
      (R2)

R4 ← σ(vkupno_prodadeni > 0)(R3)

Резултат ←
π[
    kniga_id,
    naslov,
    isbn,
    vkupno_prodadeni,
    vkupen_prihod,
    broj_naracki,
    prosechen_prihod_od_stavka
](R4)

Во SQL резултатот дополнително се подредува според вкупниот приход во опаѓачки редослед. Ова подредување е за прикажување на резултатот и не е дел од класичната релациска алгебра, бидејќи релациската алгебра не дефинира редослед на редовите.

---

## Проценка на ризик од исцрпување на залихата

### Data requirements description

Потребно е да се добие аналитички извештај кој ќе прикаже кои книги имаат најголем ризик од исцрпување на залихата врз основа на моменталната залиха и досегашната продажба преку реализирани нарачки.

За секоја книга се прикажуваат моменталната залиха, вкупниот број на продадени примероци, бројот на реализирани нарачки, просечната продажба по нарачка и проценетиот број на нарачки до исцрпување на моменталната залиха.

Овој извештај може да се користи за следење на залихите и за навремено идентификување на книгите за кои е потребно дополнување на залихата.

### Solution SQL

SELECT
    k.kniga_id,
    k.naslov,
    k.kolicina_na_zaliha AS momentalna_zaliha,

    SUM(s.kolicina) AS vkupno_prodadeni,

    COUNT(DISTINCT n.naracka_id) AS broj_naracki,

    ROUND(
        SUM(s.kolicina)::numeric /
        NULLIF(COUNT(DISTINCT n.naracka_id), 0),
        2
    ) AS prosecna_prodazba_po_naracka,

    ROUND(
        k.kolicina_na_zaliha::numeric /
        NULLIF(
            SUM(s.kolicina)::numeric /
            NULLIF(COUNT(DISTINCT n.naracka_id), 0),
            0
        ),
        2
    ) AS proceneti_naracki_do_istekuvanje

FROM project.knigi k

JOIN project.sodrzi s
    ON k.kniga_id = s.kniga_id

JOIN project.naracki n
    ON s.naracka_id = n.naracka_id
    AND n.status = 'ZAVRSENA'

GROUP BY
    k.kniga_id,
    k.naslov,
    k.kolicina_na_zaliha

HAVING SUM(s.kolicina) > 0

ORDER BY
    proceneti_naracki_do_istekuvanje ASC;

### Solution Relational Algebra

R1 ← KNIGI ⋈ KNIGI.kniga_id = SODRZI.kniga_id SODRZI

R2 ← R1 ⋈ SODRZI.naracka_id = NARACKI.naracka_id
      σ(status = 'ZAVRSENA')(NARACKI)

R3 ← γ
      kniga_id, naslov, kolicina_na_zaliha;
      SUM(kolicina) → vkupno_prodadeni,
      COUNT(DISTINCT naracka_id) → broj_naracki
      (R2)

R4 ← σ(vkupno_prodadeni > 0)(R3)

R5 ←
π[
    kniga_id,
    naslov,
    kolicina_na_zaliha,
    vkupno_prodadeni,
    broj_naracki,
    (
        vkupno_prodadeni / broj_naracki
    ) → prosecna_prodazba_po_naracka,
    (
        kolicina_na_zaliha /
        (vkupno_prodadeni / broj_naracki)
    ) → proceneti_naracki_do_istekuvanje
](R4)

Во SQL резултатот се подредува според проценетиот број на нарачки до исцрпување на залихата во растечки редослед. На тој начин книгите со помала проценета вредност се прикажуваат први. Подредувањето служи за прикажување на резултатот и не е дел од класичната релациска алгебра.

---

## Discussion

Двата аналитички извештаи користат податоци од повеќе релации и овозможуваат анализа на долгорочното работење на BiblioPremium.

Првиот извештај ја анализира успешноста на книгите преку бројот на продадени примероци, бројот на реализирани нарачки и остварениот приход.

Вториот извештај ја анализира состојбата со залихите и овозможува идентификување на книгите кај кои постои поголем ризик од исцрпување на залихата.

И двата извештаи користат само реализирани нарачки со статус ZAVRSENA, со што незавршените нарачки не влијаат врз аналитичките резултати.

Извештаите можат да се користат како основа за периодични аналитички прегледи на продажбата, приходите и залихите во BiblioPremium.