Subquery Example Challenge

Quiz

However, we will need to do two aggregations and two subqueries to make this happen.

Let’s find the number of times each channel is used by each account.
So we will need to count the number of rows by Account and Channel. This count will be our first aggregation needed.

With my first attempt, I made a mistake of counting the channels. This counts the channel, regardless of the channel name per account.

SELECT a.name, COUNT(w.channel)
FROM accounts a
JOIN web_events w
ON a.id = w.account_id
GROUP BY a.name
ORDER BY 2 DESC;

The correction I need to make is to include the channel name per account, then count per channel name, per account.

Here’s the correct query:

SELECT a.name, w.channel, COUNT(*)
FROM accounts a
JOIN web_events w
ON a.id = w.account_id
GROUP BY a.name, w.channel
ORDER BY 3 DESC;

name	channel	count
Leucadia National	direct	52
Colgate-Palmolive	direct	51
New York Life Insurance	direct	51
Philip Morris International	direct	49
ADP	direct	48
BlackRock	direct	48
FirstEnergy	direct	48
Charter Communications	direct	48
AutoNation	direct	48
American Family Insurance Group	direct	47

The solution:

SELECT a.name, w.channel, COUNT(*)
FROM accounts a
JOIN web_events w
ON a.id = w.account_id
GROUP BY 1, 2
ORDER BY 1, 3 DESC;

Here it was sorted by the account name first, then the number of counts per channel per account.