Execute the following script in Query Editor to demonstrate the use of multiple CTE-s in a name frequency distribution query.

use AdventureWorks

go

with cteLastNameFreq

as

(select LastName as [LastNames],

            count(*) as [LastNameFrequency]

        from Person.Contact

        where LastName like 'S%'

        group by LastName),

cteFirstNameFreq

as

(select FirstName as [FirstNames],

            count(*) as [FirstNameFrequency]

        from Person.Contact

        where LastName like 'S%'

        group by FirstName)

select

[Name] = rtrim(FirstName)+' '+ rtrim(LastName),

Email = left(EmailAddress, 30),

f.FirstNameFrequency,

l.LastNameFrequency

from Person.Contact c

      inner join cteFirstNameFreq as f

            on c.FirstName = f.FirstNames

      inner join cteLastNameFreq as l

            on c.LastName = l.LastNames

where LastName like 'S%'

order by [Name]

go