Changes between Initial Version and Version 1 of View8

Timestamp:

08/21/26 09:05:25 (26 hours ago)

Author:

235018

Comment:

--

View8

@@ +1 @@
+= Store performance overview
+
+=== Description
+This view provides a combined overview of the overall performance of each store by bringing together store information, financial reports, sales data, and customer ratings.
+
+The view is intended for:
+
+- monitoring store performance
+- management dashboards
+- comparing stores
+- financial analysis
+- evaluating business growth
+
+==== Tables covered by the view:
+
+- Report
+- Store
+- exchanges_data
+
+==== SQL код
+{{{#!sql
+CREATE OR REPLACE VIEW vw_store_performance AS
+SELECT
+    s.store_id,
+    s.name AS store_name,
+    s.date_of_founding,
+    s.rating,
+
+    COUNT(DISTINCT r.date) AS number_of_reports,
+    COALESCE(SUM(r.profit), 0) AS total_reported_profit,
+    COALESCE(MAX(r.overall_profit), 0) AS overall_profit,
+
+    COALESCE(SUM(ed.sales), 0) AS total_sales,
+    COALESCE(SUM(ed.damages), 0) AS total_damages,
+    COALESCE(SUM(ed.monthly_profit), 0) AS total_monthly_profit
+
+FROM store s
+LEFT JOIN report r
+    ON r.store_id = s.store_id
+LEFT JOIN exchanges_data ed
+    ON ed.store_id = r.store_id
+    AND ed.date = r.date
+
+GROUP BY
+    s.store_id,
+    s.name,
+    s.date_of_founding,
+    s.rating;
+
+}}}
+
+==== Logic explanation
+**1.** The `store` table provides the basic store information and current rating.
+
+**2.** `report` provides information about store profits over different reporting periods.
+
+**3.** `exchanges_data` provides sales, damages, and monthly profit.
+
+**4.** COUNT determines how many reports exist for each store.
+
+**5.** SUM calculates the total reported profit, sales, damages, and monthly profit.
+
+**6.** MAX retrieves the highest recorded overall_profit.
+
+**7.** LEFT JOIN ensures that stores without reports or exchange records are still displayed.
+
+**8.** GROUP BY combines the information into one row per store.
+
+==== Reason for view 
+This view is useful because:
+
+- It provides a centralized overview of store performance
+- It combines financial and operational information
+- It makes comparison between stores easier
+- It simplifies management and financial reporting
+- It provides useful aggregated information for dashboards and analytics
+
+Without this view, management queries would need to repeatedly join and aggregate store, report, and exchanges_data, making store-performance analysis more complex and increasing the amount of SQL logic required by the application.