= 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.