Changes between Version 2 and Version 3 of AdvancedDatabaseDevelopment

Timestamp:

02/20/26 02:23:48 (4 days ago)

Author:

221181

Comment:

--

AdvancedDatabaseDevelopment

@@ -65 +65 @@
 
 == 2.Views ==
+
+=== 2.1 vw_sales_by_day_of_week - Кој ден од неделата се продава најмногу? ===
+
+Прикажува колку продажби направил секој вработен, колку приход донел и неговото место во рангирањето.
+
+{{{#!sql
+CREATE OR REPLACE VIEW vw_sales_by_day_of_week AS
+SELECT
+    EXTRACT(ISODOW FROM s.date_time)::INT AS day_number,
+    CASE EXTRACT(ISODOW FROM s.date_time)::INT
+        WHEN 1 THEN 'Monday'
+        WHEN 2 THEN 'Tuesday'
+        WHEN 3 THEN 'Wednesday'
+        WHEN 4 THEN 'Thursday'
+        WHEN 5 THEN 'Friday'
+        WHEN 6 THEN 'Saturday'
+        WHEN 7 THEN 'Sunday'
+    END AS day_name,
+    COUNT(DISTINCT s.sale_id) AS total_sales,
+    SUM(s.total_amount)       AS total_revenue,
+    ROUND(AVG(s.total_amount), 2) AS avg_sale_value,
+    SUM(si.quantity)          AS total_items_sold
+FROM sale s
+JOIN sale_item si ON s.sale_id = si.sale_id
+GROUP BY EXTRACT(ISODOW FROM s.date_time)
+ORDER BY day_number;
+}}}
+
+=== 2.2 vw_employee_sales_ranking - Кој продавач продава повеќе? ===
+
+Кои денови се зафатени, а кои мирни за планирање на персоналот.
+
+{{{#!sql
+CREATE OR REPLACE VIEW vw_employee_sales_ranking AS
+SELECT
+    u.user_id,
+    u.full_name,
+    u.role,
+    COUNT(DISTINCT s.sale_id) AS total_sales,
+    COALESCE(SUM(s.total_amount), 0) AS total_revenue,
+    ROUND(COALESCE(AVG(s.total_amount), 0), 2) AS avg_sale_value,
+    COUNT(DISTINCT s.customer_id) AS unique_customers,
+    RANK() OVER (ORDER BY COALESCE(SUM(s.total_amount), 0) DESC)
+        AS revenue_rank
+FROM users u
+LEFT JOIN sale s ON u.user_id = s.user_id
+GROUP BY u.user_id, u.full_name, u.role
+HAVING COUNT(s.sale_id) > 0
+ORDER BY total_revenue DESC;
+}}}