sales_by_store_view.sql

CREATE OR REPLACE VIEW sales.sales_by_store AS
SELECT (c.city || ','::text) || cy.country AS store,
    (m.first_name || ' '::text) || m.last_name AS manager,
    sum(p.amount) AS total_sales
FROM sales.payment p
    JOIN sales.rental r ON p.rental_id = r.rental_id
    JOIN stores.inventory i ON r.inventory_id = i.inventory_id
    JOIN stores.store s ON i.store_id = s.store_id
    JOIN common.address a ON s.address_id = a.address_id
    JOIN common.city c ON a.city_id = c.city_id
    JOIN common.country cy ON c.country_id = cy.country_id
    JOIN staff.staff m ON s.manager_staff_id = m.staff_id
GROUP BY cy.country, c.city, s.store_id,
    m.first_name, m.last_name
ORDER BY cy.country, c.city;