correlated subquery & top & view & derived table & group by, Sql Server

Language: Layout:

-- correlated subquery & top & view & derived table & group by

---Enjoy Coding by alex ogrinja

/*
Table of contents
- create and insert into tables,
- select *, 
- Return sellers who have in their portfolio transactions greater than or equal to the highest average of portfolios (in 3 steps).
*/

--->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

create table orders
(ord_no int
, purch_amt float
, ord_date date
, customer_id int
, salesman_id int);

insert into orders
values
(70001,      150.5  ,     '2012-10-05',  3005      ,   5002),
(70002  ,     65.26     ,  '2012-10-05' , 3002   ,      5001),
(70004   ,    110.5    ,   '2012-08-17'  ,3009  ,       5003),
(70007   ,    948.5   ,    '2012-09-10',  3005 ,        5002),
(70005    ,   2400.6 ,     '2012-07-27',  3007     ,    5001),
(70008     ,  5760       , '2012-09-10' , 3002      ,   5001),
(70010,       1983.43   ,  '2012-10-10',  3004    ,     5006),
(70003  ,     2480.4   ,   '2012-10-10' , 3009   ,      5003),
(70012   ,    250.45  ,    '2012-06-27' , 3008  ,       5002),
(70011    ,   75.29  ,     '2012-08-17' , 3003 ,        5007),
(70013     ,  3045.6,      '2012-04-25'  ,3002,         5001);

--->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
go
select * from orders o
order by o.salesman_id, o.customer_id;
go
--->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

-- Scenario: Return sellers who have in their portfolio transactions greater than or equal to the highest average of portfolios.

-- Step 1: Using syntax "group by" return the max avg of the salesmen portofolios, and saved as view "avg_max_porto".
create view avg_max_porto as
select top 1 o.salesman_id, avg(o.purch_amt) avg_tranzac_porto from orders o
group by o.salesman_id
order by avg(o.purch_amt) desc;
go
-- Step 2: Cut off the view "avg_max_porto" at max avg portofolios 1 figure.
select a.avg_tranzac_porto from avg_max_porto a
go
-- Step 3: Getting the expected result.
select * from orders o1
where 
(select a.avg_tranzac_porto from avg_max_porto a) 
<= (select avg(o2.purch_amt) from orders o2 where o1.salesman_id= o2.salesman_id)
order by o1.customer_id;

---Enjoy Coding by alex ogrinja

View schema
Execution time: 0,05 sec, rows selected: 16, rows affected: 11, absolute service time: 0,25 sec, absolute service time: 0,26 sec