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 код

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.