1-laboratoriya topshirig’i

1-yechim:

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-yechim:

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