source: database/Advanced Database Developement/Views/Store performance overview.txt@ 33517cc

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;