Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- WITH ContinentsAndCurrencyUsage_CTE (ContinentCode,
- CurrencyCode,
- CurrencyUsage)
- AS
- (
- SELECT c.ContinentCode,
- cntr.CurrencyCode,
- COUNT(*) AS [Currency Usage]
- FROM Continents AS c
- INNER JOIN Countries AS cntr
- ON c.ContinentCode = cntr.ContinentCode
- GROUP BY c.ContinentCode, cntr.CurrencyCode
- ),
- CountriesWithMaxCurrencyUsage_CTE (ContinendCode, CurrencyCode, MaxCurrencyUsage)
- AS
- (
- SELECT ContinentsAndCurrencyUsage_CTE.ContinentCode,
- ContinentsAndCurrencyUsage_CTE.CurrencyCode,
- MAX(ContinentsAndCurrencyUsage_CTE.CurrencyUsage) AS [Max currency usage]
- FROM ContinentsAndCurrencyUsage_CTE
- GROUP BY ContinentsAndCurrencyUsage_CTE.ContinentCode, ContinentsAndCurrencyUsage_CTE.CurrencyCode
- HAVING MAX(ContinentsAndCurrencyUsage_CTE.CurrencyUsage) > 1
- ),
- ContinentsWithMaxCurrencyUsage_CTE (ContinentCode, MaxCurrencyUsage)
- AS
- (
- SELECT CountriesWithMaxCurrencyUsage_CTE.ContinendCode,
- MAX(CountriesWithMaxCurrencyUsage_CTE.MaxCurrencyUsage) AS [Max]
- FROM CountriesWithMaxCurrencyUsage_CTE
- GROUP BY CountriesWithMaxCurrencyUsage_CTE.ContinendCode
- )
- SELECT cont.ContinentCode,
- coun.CurrencyCode,
- cont.MaxCurrencyUsage
- FROM ContinentsWithMaxCurrencyUsage_CTE AS cont
- INNER JOIN CountriesWithMaxCurrencyUsage_CTE AS coun
- ON cont.ContinentCode = coun.ContinendCode AND
- cont.MaxCurrencyUsage = coun.MaxCurrencyUsage
- ORDER BY cont.ContinentCode
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement