Subquery Example Challenge

Quiz

• What is the top channel used by each account to market products?
• How often was that same channel used?

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

1. Let’s find the number of times each channel is used by each account.
2. 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;

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.