rose learns.to('code');

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

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

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

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

2. 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;

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

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;

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;

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);

3. 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

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:

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:

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:

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

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;

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);

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;

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?
   • 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

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

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

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

Seems like my answer is correct.

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;

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;

5. 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

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

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;

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;

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?
   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

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)

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

SELECT AVG(o.total_amt_usd) avg_all
FROM orders o

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);

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!! 😊