Saturday, 25 March 2023

sql interview question

 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;

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

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

No comments:

Post a Comment