Quiz – Subqueries & Temporary Tables

  1. Provide the name of the sales_rep in each region with the largest amount of total_amt_usd sales.

Step 1 – Get the MAX total_amt_usd per region:

SELECT r.name RegionName, MAX(o.total_amt_usd) TotalAmt
FROM region r
JOIN sales_reps s
ON r.id = s.region_id
JOIN accounts a 
ON s.id = a.sales_rep_id
JOIN orders o
ON o.account_id = a.id
GROUP BY 1
regionnametotalamt
Midwest48675.90
Southeast95005.82
Northeast93106.81
West232207.07

I get the max amt_usd per region, but how do I get the name of those reps with this total sales?

I am trying another approach below. I added the Region ID, so I can connect it with the Sales Reps table.

SELECT r.id, r.name RegionName, MAX(o.total_amt_usd) TotalAmt
FROM region r
JOIN sales_reps s
ON r.id = s.region_id
JOIN accounts a 
ON s.id = a.sales_rep_id
JOIN orders o
ON o.account_id = a.id
GROUP BY 1,2
idregionnametotalamt
3Southeast95005.82
4West232207.07
2Midwest48675.90
1Northeast93106.81

Step 2 – Get the sales_rep name with total_amt_usd shown on my first table.

WITH t1 AS(SELECT r.id RegionID, r.name    
     RegionName, MAX(o.total_amt_usd)  
     TotalAmt
     FROM region r
     JOIN sales_reps s
     ON r.id = s.region_id
     JOIN accounts a 
     ON s.id = a.sales_rep_id
     JOIN orders o
     ON o.account_id = a.id
     GROUP BY 1,2) 
SELECT s.name, t1.RegionName, t1.TotalAmt
FROM sales_reps s
JOIN t1
ON s.region_id = t1.RegionID
JOIN accounts a
ON s.id = a.sales_rep_id
JOIN orders o
ON a.id = o.account_id
WHERE o.total_amt_usd = t1.TotalAmt
nameregionnametotalamt
Necole VictoryNortheast93106.81
Cliff MeintsMidwest48675.90
Dorotha SeawellSoutheast95005.82
Dawna AgnewWest232207.07

I should be able to simplify this… How???

The larger the sample size and the more diverse your dataset is, the more confident you’ll be in your results.

  1. For the region with the largest (sum) of sales total_amt_usd, how many total (count) orders were placed?

Step 1 – I took the largest sum of sales, and from which region:

SELECT r.name, SUM(total_amt_usd)
     FROM region r
     JOIN sales_reps s
     ON r.id = s.region_id
     JOIN accounts a 
     ON s.id = a.sales_rep_id
     JOIN orders o
     ON o.account_id = a.id
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;
namesum
Northeast7744405.36

Step 2 – I took the total number of orders from the Region with the largest total amount in usd.

What did I do here? – I created another table showing the Region Name, calculate the total order, of the region with largest total amt usd from the first table (t1)

WITH t1 AS (SELECT r.name RegionName,     
     SUM(total_amt_usd) SumAmt
     FROM region r
     JOIN sales_reps s
     ON r.id = s.region_id
     JOIN accounts a 
     ON s.id = a.sales_rep_id
     JOIN orders o
     ON o.account_id = a.id
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1)
SELECT t1.RegionName, t1.SumAmt, COUNT(o.total) Total
FROM t1
JOIN region r
ON r.name = t1.RegionName
JOIN sales_reps s
ON r.id = s.region_id
JOIN accounts a
ON s.id = a.sales_rep_id
JOIN orders o
ON a.id = o.account_id
GROUP BY 1, 2
regionnamesumamttotal
Northeast7744405.362357

I am correct for this one!! 😊 

2 For the region with the largest sales total_amt_usd, how many total orders were placed?

The first query I wrote was to pull the total_amt_usd for each region.

SELECT r.name region_name, SUM(o.total_amt_usd) total_amt
FROM sales_reps s
JOIN accounts a
ON a.sales_rep_id = s.id
JOIN orders o
ON o.account_id = a.id
JOIN region r
ON r.id = s.region_id
GROUP BY r.name;

Then we just want the region with the max amount from this table. There are two ways I considered getting this amount. One was to pull the max using a subquery. Another way is to order descending and just pull the top value.

SELECT MAX(total_amt)
FROM (SELECT r.name region_name, SUM(o.total_amt_usd) total_amt
             FROM sales_reps s
             JOIN accounts a
             ON a.sales_rep_id = s.id
             JOIN orders o
             ON o.account_id = a.id
             JOIN region r
             ON r.id = s.region_id
             GROUP BY r.name) sub;

Finally, we want to pull the total orders for the region with this amount:

SELECT r.name, COUNT(o.total) total_orders
FROM sales_reps s
JOIN accounts a
ON a.sales_rep_id = s.id
JOIN orders o
ON o.account_id = a.id
JOIN region r
ON r.id = s.region_id
GROUP BY r.name
HAVING SUM(o.total_amt_usd) = (
      SELECT MAX(total_amt)
      FROM (SELECT r.name region_name, SUM(o.total_amt_usd) total_amt
              FROM sales_reps s
              JOIN accounts a
              ON a.sales_rep_id = s.id
              JOIN orders o
              ON o.account_id = a.id
              JOIN region r
              ON r.id = s.region_id
              GROUP BY r.name) sub);

This provides the Northeast with 2357 orders.

  1. How many accounts had more total purchases than the account name which has bought the most standard_qty paper throughout their lifetime as a customer?
    • I am looking for the number of accounts here (COUNT())
    • N = COUNT (ACCOUNTS(SUM(total) > ACCOUNT(MAX(standard_qty))

Step 1 – I took a list of account names with their total orders

SELECT a.name AcctName, SUM(o.total) TotalOrders
FROM accounts a
JOIN orders o
ON a.id = o.account_id
GROUP BY 1
acctnametotalorders
Boeing10358
Western Digital39187
Sysco40535
Southern8003
Altria Group21887
Energy Transfer Equity2994
Aramark3121

Step 2 – I make a list of accounts with their total orders of standard_qty, and sorted it as highest number of orders first

SELECT a.name AcctName, SUM(o.standard_qty) Total_Standard
FROM accounts a 
JOIN orders o
ON a.id = o.account_id
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1

From here, I know that the account name with max number of standard orders is:

acctnametotal_standard
Core-Mark Holding41617

Step 3 – I need to compare the two tables, with a third table that shows the accounts with larger TotalOrders from table 1 than total_standard from table 2

How will I connect them? What’s common?

This is my first attempt:

WITH t1 AS (SELECT a.name AcctName, SUM(o.total)   
            TotalOrders
            FROM accounts a
            JOIN orders o
            ON a.id = o.account_id
            GROUP BY 1), 
t2 AS (SELECT a.name AcctName, SUM(o.standard_qty)
       Total_Standard
       FROM accounts a 
       JOIN orders o
       ON a.id = o.account_id
       GROUP BY 1
       ORDER BY 2 DESC
       LIMIT 1)
SELECT COUNT(a.name)
FROM t2, accounts a
JOIN t1
ON t1.AcctName = a.name
WHERE t1.TotalOrders > t2.Total_Standard

Here’s the answer I got:

count
6

I need to double check… here’s the query I used to test:

SELECT a.name AcctName, SUM(o.total)   
            TotalOrders
            FROM accounts a
            JOIN orders o
            ON a.id = o.account_id
            GROUP BY 1
            ORDER BY 2 DESC
            LIMIT 7

The answer was 6 count, and so I limited the results to 7, so I could see if it really has 6 count, and that the 7th is not eligible:

acctnametotalorders
EOG Resources56410
Mosaic49246
IBM47506
Core-Mark Holding44750
General Dynamics43730
Leucadia National42358
Arrow Electronics40904

My test matches, so it seems like I am correct. 😊 

My answer here is incorrect. 😞 I don’t know why!!!

How many accounts had more total purchases than the account name which has bought the most standard_qty paper throughout their lifetime as a customer?

First, we want to find the account that had the most standard_qty paper. The query here pulls that account, as well as the total amount:

SELECT a.name account_name, SUM(o.standard_qty) total_std, SUM(o.total) total
FROM accounts a
JOIN orders o
ON o.account_id = a.id
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;

Now, I want to use this to pull all the accounts with more total sales:

SELECT a.name
FROM orders o
JOIN accounts a
ON a.id = o.account_id
GROUP BY 1
HAVING SUM(o.total) > (SELECT total 
                   FROM (SELECT a.name act_name, SUM(o.standard_qty) tot_std, SUM(o.total) total
                         FROM accounts a
                         JOIN orders o
                         ON o.account_id = a.id
                         GROUP BY 1
                         ORDER BY 2 DESC
                         LIMIT 1) sub);

This is now a list of all the accounts with more total orders. We can get the count with just another simple subquery.

SELECT COUNT(*)
FROM (SELECT a.name
       FROM orders o
       JOIN accounts a
       ON a.id = o.account_id
       GROUP BY 1
       HAVING SUM(o.total) > (SELECT total 
                   FROM (SELECT a.name act_name, SUM(o.standard_qty) tot_std, SUM(o.total) total
                         FROM accounts a
                         JOIN orders o
                         ON o.account_id = a.id
                         GROUP BY 1
                         ORDER BY 2 DESC
                         LIMIT 1) inner_tab)
             ) counter_tab;

The final table from the solution:

count
3
  1. For the customer that spent the most (in total over their lifetime as a customer) total_amt_usd, how many web_events did they have for each channel?
    • N = COUNT(web_events)/channel(ACCOUNT(MAX(SUM(total_amt_usd)
    • Who is the customer/account that spent the most in total? SUM USD
    • How many web_events/channel?

Step 1 – Who is the account that spent the most in total orders?

SELECT a.name AcctName, SUM(o.total_amt_usd) Total_Orders_USD
FROM accounts a
JOIN orders o
ON a.id = o.account_id
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1
acctnametotal_orders_usd
EOG Resources382873.30

Step 2 – Get the count of web events per channel

SELECT a.name AcctName, w.channel WebChannel, COUNT(*) Total_Events
FROM accounts a
JOIN web_events w
ON a.id = w.account_ID
GROUP BY 1, 2
ORDER BY 1
acctnamewebchanneltotal_events
3Mdirect19
3Madwords2
3Mfacebook2
Abbott Laboratoriesdirect20
Abbott Laboratoriesbanner1
Abbott Laboratoriesorganic1
AbbViedirect2
AbbViebanner1
AbbViefacebook1
AbbVieadwords1
ADPadwords8

Step 3 – I now need to combine these two tables, with my third table only showing the account from table 1.

WITH t1 AS (SELECT a.name AcctName,
            SUM(o.total_amt_usd) Total_Orders_USD
            FROM accounts a
            JOIN orders o
            ON a.id = o.account_id
            GROUP BY 1
            ORDER BY 2 DESC
            LIMIT 1),
t2 AS (SELECT a.name AcctName, w.channel WebChannel,
       COUNT(*) Total_Events
       FROM accounts a
       JOIN web_events w
       ON a.id = w.account_ID
       GROUP BY 1, 2
       ORDER BY 1)
SELECT t1.AcctName, t2.WebChannel, t2.Total_Events
FROM t1
JOIN t2
ON t1.AcctName = t2.AcctName
ORDER BY 3 DESC
acctnamewebchanneltotal_events
EOG Resourcesdirect44
EOG Resourcesorganic13
EOG Resourcesadwords12
EOG Resourcesfacebook11
EOG Resourcestwitter5
EOG Resourcesbanner4

I get the same company as my result in my first table. I will check my answer:

SELECT a.name, w.channel, COUNT(w.channel)
FROM accounts a
JOIN web_events w
ON a.id = w.account_id
WHERE a.name = 'EOG Resources'
GROUP BY 1, 2
ORDER BY 3 desc
namechannelcount
EOG Resourcesdirect44
EOG Resourcesorganic13
EOG Resourcesadwords12
EOG Resourcesfacebook11
EOG Resourcestwitter5
EOG Resourcesbanner4

Seems like my answer is correct.

My answer is correct! 😊 

4 For the customer that spent the most (in total over their lifetime as a customer) total_amt_usd, how many web_events did they have for each channel?

Here, we first want to pull the customer with the most spent in lifetime value.

SELECT a.id, a.name, SUM(o.total_amt_usd) tot_spent
FROM orders o
JOIN accounts a
ON a.id = o.account_id
GROUP BY a.id, a.name
ORDER BY 3 DESC
LIMIT 1;

Now, we want to look at the number of events on each channel this company had, which we can match with just the id.

SELECT a.name, w.channel, COUNT(*)
FROM accounts a
JOIN web_events w
ON a.id = w.account_id AND a.id =  (SELECT id
                     FROM (SELECT a.id, a.name, SUM(o.total_amt_usd) tot_spent
                           FROM orders o
                           JOIN accounts a
                           ON a.id = o.account_id
                           GROUP BY a.id, a.name
                           ORDER BY 3 DESC
                           LIMIT 1) inner_table)
GROUP BY 1, 2
ORDER BY 3 DESC;

I added an ORDER BY for no real reason, and the account name to assure I was only pulling from one account.

  1. What is the lifetime average amount spent in terms of total_amt_usd for the top 10 total spending accounts?
    • get total amt usd of each account
    • get the top 10
    • average their total amt spent

STEP 1 – Get the total amount spent in USD per account

SELECT a.name AcctName, SUM(o.total_amt_usd) Total_USD
FROM accounts a
JOIN orders o
ON a.id = o.account_id
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10
acctnametotal_usd
EOG Resources382873.30
Mosaic345618.59
IBM326819.48
General Dynamics300694.79
Republic Services293861.14
Leucadia National291047.25
Arrow Electronics281018.36
Sysco278575.64
Supervalu275288.30
Archer Daniels Midland272672.84

Step 2 – Get the average of the total_amt_usd from my first table:

WITH t1 AS (SELECT a.name AcctName,
            SUM(o.total_amt_usd) Total_USD
            FROM accounts a
            JOIN orders o
            ON a.id = o.account_id
            GROUP BY 1
            ORDER BY 2 DESC
            LIMIT 10)
SELECT AVG(t1.Total_USD) AVG_USD
FROM t1
avg_usd
304846.969000000000

My answer is correct! 😊 

5 What is the lifetime average amount spent in terms of total_amt_usd for the top 10 total spending accounts?

First, we just want to find the top 10 accounts in terms of highest total_amt_usd.

SELECT a.id, a.name, SUM(o.total_amt_usd) tot_spent
FROM orders o
JOIN accounts a
ON a.id = o.account_id
GROUP BY a.id, a.name
ORDER BY 3 DESC
LIMIT 10;

Now, we just want the average of these 10 amounts.

SELECT AVG(tot_spent)
FROM (SELECT a.id, a.name, SUM(o.total_amt_usd) tot_spent
      FROM orders o
      JOIN accounts a
      ON a.id = o.account_id
      GROUP BY a.id, a.name
      ORDER BY 3 DESC
       LIMIT 10) temp;
  1. What is the lifetime average amount spent in terms of total_amt_usd, including only the companies that spent more per order, on average, than the average of all orders?
    1. Get the average of all orders
    2. Average orders per account
    3. Compare 1 and 2, then get the list of those 2>1
    4. Then average the orders of the companies from 3

Let’s do this!

Step 1 – Get the average of all orders

SELECT AVG(o.total_amt_usd) All_Avg
FROM orders o
all_avg
3348.0196513310185185

Step 2 – Get the average amount of total orders per account

SELECT a.name AcctName, AVG(o.total_amt_usd) Acct_Order_Avg
FROM accounts a
JOIN orders o
ON a.id = o.account_id
GROUP BY 1
ORDER BY 2 DESC

Step 3 – Now compare these two tables, and get a third table with a list of accounts that are Acct_Order_Avg > All_Avg

WITH t1 AS (SELECT a.name AcctName,
            AVG(o.total_amt_usd) Acct_Order_Avg
            FROM accounts a
            JOIN orders o
            ON a.id = o.account_id
            GROUP BY 1
            ORDER BY 2 DESC)
SELECT t1.AcctName, t1.Acct_Order_Avg
FROM t1
WHERE t1.Acct_Order_Avg > (SELECT AVG(o.total_amt_usd)
                           All_Avg
                           FROM orders o)

I placed the table on Spreadsheet, so I can compute for the AVERAGE:

Step 4 – Get the average of total orders in usd of those account names from my third table.

WITH t1 AS (SELECT a.name AcctName,
            AVG(o.total_amt_usd) Acct_Order_Avg
            FROM accounts a
            JOIN orders o
            ON a.id = o.account_id
            GROUP BY 1
            ORDER BY 2 DESC)
SELECT AVG(t1.Acct_Order_Avg)
FROM t1
WHERE t1.Acct_Order_Avg > (SELECT AVG(o.total_amt_usd)
                           All_Avg
                           FROM orders o)
avg
4721.1397439971747168

My answer matches with the calculation from the spreadsheet. I wanted to do a mini dance now. 😊 

My answer is correct! 😊 

6 What is the lifetime average amount spent in terms of total_amt_usd, including only the companies that spent more per order, on average, than the average of all orders.

First, we want to pull the average of all accounts in terms of total_amt_usd:

SELECT AVG(o.total_amt_usd) avg_all
FROM orders o

Then, we want to only pull the accounts with more than this average amount.

SELECT o.account_id, AVG(o.total_amt_usd)
FROM orders o
GROUP BY 1
HAVING AVG(o.total_amt_usd) > (SELECT AVG(o.total_amt_usd) avg_all
                               FROM orders o);

Finally, we just want the average of these values.

SELECT AVG(avg_amt)
FROM (SELECT o.account_id, AVG(o.total_amt_usd) avg_amt
    FROM orders o
    GROUP BY 1
    HAVING AVG(o.total_amt_usd) > (SELECT AVG(o.total_amt_usd) avg_all
                                   FROM orders o)) temp_table;

I got 4 out of 6 – this is not bad!! 😊 

This entry was posted in Study Notes and tagged . Bookmark the permalink.

Leave a Reply