Пособие для учителей по треку «Анализ данных»

1-решение:
SELECT distinct email,firstname,lastname,e.name
FROM Customer a,Invoice b,InvoiceLine c,Track d,Genre e
WHERE a.Customerid=b.Customerid AND b.Invoiceid=c.Invoiceid
AND c.Trackid=d.Trackid AND d.Genreid=e.Genreid AND e.name = 'Rock' ORDER BY email;
2-решение:
SELECT Customer.Email, Customer.FirstName, Customer.LastName, Genre.Name
FROM Customer
INNER JOIN Invoice ON Customer.CustomerId=Invoice.CustomerId
INNER JOIN InvoiceLine ON Invoice.InvoiceId=InvoiceLine.InvoiceId
INNER JOIN Track ON InvoiceLine.TrackId=Track.TrackId
INNER JOIN Genre ON Genre.GenreId=Track.GenreId
WHERE Genre.Name="Rock"
GROUP BY Customer.CustomerId
ORDER BY Customer.Email


5-задание. Напишите запрос, который определяет покупателя, который больше всего потратил на музыку для каждой страны. Напишите запрос, который возвращает страну с указанием основных клиентов и их потраченных средств. Для стран, где наибольшая сумма встречается много раз, укажите всех клиентов, которые потратили эту сумму.
Вам нужно будет использовать только таблицы «Клиент» и «Счет-фактура»..


1-решение:
WITH tab1 AS
(
SELECT c.CustomerId, c.FirstName, c.LastName, c.Country, SUM(i.Total) TotalSpent
FROM Customer c
JOIN Invoice i ON c.CustomerId = i.CustomerId
GROUP BY c.CustomerId
)
SELECT tab1.*
FROM tab1
JOIN
(
SELECT CustomerId, FirstName, LastName, Country, MAX(TotalSpent) AS TotalSpent
FROM tab1
GROUP BY Country
) tab2
ON tab1.Country = tab2.Country
WHERE tab1.TotalSpent = tab2.TotalSpent
ORDER BY Country;
2-решение:
WITH t1 AS
(
SELECT Customer.Country, SUM(Invoice.Total) TotalSpent, Customer.FirstName,
Customer.LastName, Customer.CustomerId
FROM Customer
JOIN Invoice ON Customer.CustomerId = Invoice.CustomerId
GROUP BY Customer.CustomerId
)

SELECT t1.CustomerId, t1.FirstName, t1.LastName, t1.Country, t1.TotalSpent

FROM t1
INNER JOIN (
SELECT t1.Country, MAX(t1.TotalSpent) as max_ts FROM t1 GROUP BY Country) AS t2
ON t1.Country=t2.Country
WHERE t1.TotalSpent=t2.max_ts
ORDER BY t1.Country


Комментарий: Разберем вопрос. Важно определить клиента, который потратил больше всего денег для каждой страны. Для этого требуется найти клиента, который тратит больше всего денег, как в вопросе 4, но если клиентов много, мы должны включить их всех. Мы используем команду WITH, потому что используем несколько таблиц. После SELECT мы возвращаем запрошенную информацию, которая является именем клиента. Поскольку мы используем таблицы Customer и Invoice одновременно, мы используем команду JOIN. Группируйте по странам и сортируйте по ORDER BY.