--select name
--from practice_dataframe
--where city = (
-- select city
--from practice_dataframe
--where name = 'Bob'
--);

--select id, name, salary
--from practice_dataframe
--where salary > (
-- select avg(salary)
-- from practice_dataframe )


select distinct city
from practice_dataframe
where age >= 30;


select name, age
from practice_dataframe
where age > (
select age
from practice_dataframe
where name = 'Alice'
);


select name, age, salary
from practice_dataframe
where salary > (
select salary
from practice_dataframe
where name = 'David');



select * from practice_dataframe;


select name, city, salary
from practice_dataframe
where salary > (
select avg(salary)
from practice_dataframe
where salary > 80000
);

-- in means in cities who can get

SELECT name
FROM practice_dataframe
WHERE city IN (
SELECT city
FROM practice_dataframe
GROUP BY city
HAVING AVG(salary) > 80000
);


select name, age
from practice_dataframe
where age > (
select avg(age)
from practice_dataframe
where name = 'Charlie' );




SELECT name
FROM practice_dataframe
WHERE age > (
SELECT AVG(age)
FROM practice_dataframe
WHERE city = (
SELECT city
FROM practice_dataframe
WHERE name = 'Charlie'
)
);


WITH CityIndividualCounts AS (
SELECT city, COUNT(*) AS total_individuals
FROM practice_dataframe
GROUP BY city
HAVING COUNT(*) > 2
)

SELECT i.name
FROM practice_dataframe i
JOIN CityIndividualCounts cic ON i.city = cic.city;




SELECT name
FROM practice_dataframe
WHERE age < (
SELECT MAX(age)
FROM practice_dataframe
);

-- cumulative salary order by age
select name, age, salary,
SUM(Salary) over (order by age asc) as cumulative_salary
from practice_dataframe;


--second largest salary
select name, salary
from practice_dataframe
order by salary desc
limit 1 offset 1;

--finding out the difference
with dif_sal as (
select max(salary) as max_salary, min(salary) as min_salary
from practice_dataframe
)

select max_salary, min_salary, (max_salary - min_salary) as difference_sal
from dif_sal;
--within 10 000
SELECT name, salary
FROM practice_dataframe i
WHERE EXISTS (
SELECT 1
FROM practice_dataframe
WHERE salary BETWEEN (SELECT MAX(salary) - 10000 FROM practice_dataframe) AND (SELECT MAX(salary) FROM practice_dataframe)
AND name = i.name
);

.info() number of observations

977667170 22147

by each company