Subquery Example Challenge
Date: July 31, 2022
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.
- 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;
name | count |
---|---|
Ecolab | 101 |
Charter Communications | 96 |
AutoNation | 94 |
Colgate-Palmolive | 93 |
FirstEnergy | 91 |
Marathon Petroleum | 90 |
TJX | 89 |
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.
name | channel | count |
---|---|---|
3M | direct | 19 |
3M | 2 | |
3M | adwords | 2 |
Abbott Laboratories | direct | 20 |
Abbott Laboratories | organic | 1 |
Abbott Laboratories | banner | 1 |
AbbVie | direct | 2 |
Leave a Reply