Monthly sales and profit per store

Description

This view provides a monthly overview of sales and profit for each store by combining information from the REPORT, STORE, and exchanges_data tables.

The view is intended for:

• monitoring monthly store performance
• financial reports
• comparing stores
• analyzing sales and profit trends

Tables covered by the view:

• Report
• Store
• exchanges_data

SQL код

CREATE OR REPLACE VIEW vw_monthly_sales_profit AS
SELECT
    s.store_id,
    s.name AS store_name,
    r.date,
    r.month_and_year,
    r.profit,
    r.overall_profit,
    ed.monthly_profit,
    ed.sales,
    ed.damages

FROM store s
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;

Logic explanation

1. The store information is retrieved from store.

2. The report table provides the monthly profit and overall profit.

3. The exchanges_data table provides monthly profit, sales, and damages.

4. LEFT JOIN is used for exchanges_data so that a report is still displayed even if no corresponding data exchange record exists.

5. Each row represents the financial information for a particular store and reporting date.

Reason for view

This view is useful because:

• It centralizes monthly financial information
• It simplifies financial reporting queries
• It allows easy comparison of store performance
• It provides sales, profit, and damages in one result
• It reduces repeated JOIN operations in the application

Without this view, when doing any analitical tasks system will have to make multiple joints to get the results.