-- Create a temporary table to simulate a dataframe
CREATE TEMP TABLE practice_dataframe (
id SERIAL PRIMARY KEY,
name VARCHAR(50),
age INT,
city VARCHAR(50),
salary DECIMAL(10, 2)
);

-- Insert sample data into the dataframe
INSERT INTO practice_dataframe (name, age, city, salary) VALUES
('Alice', 25, 'New York', 60000.00),
('Bob', 30, 'Los Angeles', 70000.00),
('Charlie', 35, 'Chicago', 80000.00),
('David', 40, 'Houston', 90000.00),
('Emma', 45, 'San Francisco', 100000.00);

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