Changes between Initial Version and Version 1 of AdvancedReports

Timestamp:

02/14/24 20:20:33 (22 months ago)

Author:

191572

Comment:

--

AdvancedReports

@@ +1 @@
+-- Zarabotka otstvarena preku narackite od aplikacijata za sekoj restoran na mesecno nivo
+SELECT r.ime, DATE_PART('Month', n.datum) AS mesec, DATE_PART('Year', n.datum) AS godina, SUM(iznos) AS zarabotka
+FROM restoran r
+JOIN menu_item m ON r.id=m.restoran_id
+JOIN naracka_sodrzi_menu_item nsm ON m.id=nsm.menu_item_id
+JOIN naracka n ON nsm.naracka_id=n.id
+JOIN naplata np ON n.id=np.naracka_id
+GROUP BY r.ime, DATE_PART('Month', n.datum), DATE_PART('Year', n.datum);
+
+
+-- Kolicina na prodadeni parcinja za sekoj proizvod ponuden vo restoranite, podredeni po popularnost na godisno nivo (najnaracuvaniot proizvod prv)
+SELECT m.ime, DATE_PART('Year', n.datum) AS godina, SUM(nsm.quantity) AS kolicina
+FROM menu_item m
+JOIN naracka_sodrzi_menu_item nsm ON m.id=nsm.menu_item_id
+JOIN naracka n ON nsm.naracka_id=n.id
+GROUP BY m.ime, DATE_PART('Year', n.datum)
+ORDER BY SUM(nsm.quantity) DESC;
+
+
+-- Broj na naracki za sekoj restoran za sekoj mesec
+SELECT r.ime, DATE_PART('Month', n.datum) AS mesec, DATE_PART('Year', n.datum) as godina, count(nsm.naracka_id) AS broj_na_naracki
+FROM restoran r
+JOIN menu_item m ON r.id=m.restoran_id
+JOIN naracka_sodrzi_menu_item nsm ON m.id=nsm.menu_item_id
+JOIN naracka n on nsm.naracka_id=n.id
+GROUP BY r.ime, DATE_PART('Month', n.datum), DATE_PART('Year', n.datum);
+
+-- Pregled na broj na naracki po tip na naplata za sekoj restoran na mesecno nivo
+SELECT r.ime, np.nacin_na_plakjane, DATE_PART('Month', n.datum) AS mesec, DATE_PART('Year', n.datum) as godina, COUNT(DISTINCT n.id) AS broj_na_naracki
+FROM restoran r
+JOIN menu_item m ON r.id=m.restoran_id
+JOIN naracka_sodrzi_menu_item nsm ON m.id=nsm.menu_item_id
+JOIN naracka n on nsm.naracka_id=n.id
+JOIN naplata np ON n.id=np.naracka_id
+GROUP BY r.ime, np.nacin_na_plakjane, DATE_PART('Month', n.datum), DATE_PART('Year', n.datum);