-- create a table
CREATE TABLE employee (
first varchar(20),
last varchar(20),
age int,
salary int,
location varchar(20)
);

insert into employee values('sachin','sharma',28,10000,'Bangalore');
insert into employee values('shane','Warne',30,20000,'Bangalore');
insert into employee values('rohit','sharma',31,30000,'Hydrabad');
insert into employee values('Shikhar','Dhawan',32,25000,'Hydrabad');
insert into employee values('Rahul','Dravid',31,20000,'Bangalore');
insert into employee values('saurabh','Ganguly',32,15000,'Varanasi');
insert into employee values('kapil','dev',34,10000,'Varanasi');

-- select * from employee;

-- select location, count(*) as total, avg(salary) as avg from employee group by 1;

SELECT '-------------------------' AS '';

-- how many employees from each location and average salary at each location

select 
  first, employee.location, salary, round(avg_sal, 2) avg_salary, head_cnt 
from 
  employee 
  join (
    select 
      count(*) as head_cnt, avg(salary) as avg_sal, location 
    from 
      employee 
    group by 
      location
  ) as temp_table on employee.location = temp_table.location 
order by 
  location;


SELECT '-------------------------' AS '';

with temp_table_cte as (
  select 
    count(*) as head_cnt, avg(salary) as avg_sal, location 
  from 
    employee 
  group by 
    location
) 
select 
  first, employee.location, salary, round(avg_sal, 2) avg_salary, head_cnt 
from 
  employee 
  join temp_table_cte on employee.location = temp_table_cte.location 
order by 
  location;


SELECT '-------------------------' AS '';

SELECT 
  first, location, salary, 
  avg(salary) OVER(PARTITION BY location) as avg_salary, 
  count(location) OVER (PARTITION BY location) as head_cnt 
from 
  employee 
order by 
  location