1. find the 2nd largest value
select max(salary) as second_highest_salary
from employees
where salary < (select max(salary) from employees);
2. find total number and average value
select department_id, count(*) as total_employees, avg(salary) as average_salary
from employees
group by department_id;
3. find the employees who have the same job title as their manager
select e.employee_name, e.job_title
from employees e
join managers m on e.manager_id = m.manager_id
where e.job_title = m.job_title;
from employees e
join managers m on e.manager_id = m.manager_id
where e.job_title = m.job_title;
4. find the total revenue generated per month for a given year (e.g., 2022)
select extract(month from order_date) as month, sum(revenue) as total_revenue
from orders
where extract(year from order_date) = 2022
group by extract(month from order_date)
order by month
from orders
where extract(year from order_date) = 2022
group by extract(month from order_date)
order by month
5. 万能公式
select t1.field_1, aggregate(t2.field_2) as metric
from table_1 t1
join table_2 t2 on t1.field_3 = t2.field_4
group by t1.field_1 //group field should be same as in select, otherwise make no sense
order by metric
limit 3
