---
# 🏢 `company_db` SQL Practice – Questions & Answer Key
**Schema:** `company_db`
**Tables:** `employees`, `departments`, `projects`, `customers`, `orders`
**MySQL version:** 8.0+
---
## 1️⃣ Salary vs Department Average
**Question:**
For each employee, show their salary and how much it differs from the average salary of their department.
**Hint:**
Use a window function with `PARTITION BY department`.
**SQL:**
```sql
SELECT
emp_id,
first_name,
last_name,
department,
salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary,
salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;
```
---
## 2️⃣ Top 2 Highest-Paid Employees per Department
**Question:**
Find the top 2 highest-paid employees in each department.
**Hint:**
Use `DENSE_RANK()` or `RANK()` with a partition.
**SQL:**
```sql
SELECT *
FROM (
SELECT
emp_id,
first_name,
last_name,
department,
salary,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees
) ranked
WHERE salary_rank <= 2;
```
---
## 3️⃣ Departments Exceeding Budget
**Question:**
Which departments have total employee salaries exceeding their department budget?
**Hint:**
Join `employees` to `departments` and use `HAVING`.
**SQL:**
```sql
SELECT
d.dept_name,
d.budget,
SUM(e.salary) AS total_salary
FROM departments d
JOIN employees e
ON d.dept_name = e.department
GROUP BY d.dept_name, d.budget
HAVING SUM(e.salary) > d.budget;
```
---
## 4️⃣ Overdue Projects
**Question:**
List all projects whose `end_date` has passed but are not marked as DONE. Show how many days they are overdue.
**Hint:**
Use `CURDATE()` and `DATEDIFF()`.
**SQL:**
```sql
SELECT
project_name,
end_date,
status,
DATEDIFF(CURDATE(), end_date) AS days_overdue
FROM projects
WHERE end_date < CURDATE()
AND status <> 'DONE';
```
---
## 5️⃣ Payroll Contribution Percentage
**Question:**
For each employee, calculate their percentage contribution to their department’s total payroll.
**Hint:**
Divide salary by SUM(salary) over a partition.
**SQL:**
```sql
SELECT
emp_id,
first_name,
last_name,
department,
salary,
ROUND(salary / SUM(salary) OVER (PARTITION BY department) * 100, 2) AS payroll_percentage
FROM employees;
```
---
## 6️⃣ High-Value Customers
**Question:**
For each customer, show total orders, total spend, and average order value. Only include customers whose total spend is above the overall average customer spend.
**Hint:**
Aggregation + subquery for global average.
**SQL:**
```sql
SELECT
customer_name,
COUNT(*) AS total_orders,
SUM(total) AS total_spent,
AVG(total) AS avg_order_value
FROM orders
GROUP BY customer_name
HAVING SUM(total) >
(
SELECT AVG(customer_total)
FROM (
SELECT SUM(total) AS customer_total
FROM orders
GROUP BY customer_name
) t
);
```
---
## 7️⃣ Monthly Revenue + MoM Change
**Question:**
Show total revenue by month and month-over-month revenue change.
**Hint:**
Aggregate by month, then use `LAG()`.
**SQL:**
```sql
SELECT
month,
revenue,
revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change
FROM (
SELECT
DATE_FORMAT(order_date, '%Y-%m') AS month,
SUM(total) AS revenue
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
) monthly;
```
---
## 8️⃣ Department Workload vs Pay
**Question:**
For each department, show employee count, project count, average salary, and rank departments by projects per employee.
**Hint:**
LEFT JOIN employees and projects; avoid divide-by-zero.
**SQL:**
```sql
SELECT
d.dept_name,
COUNT(DISTINCT e.emp_id) AS employee_count,
COUNT(DISTINCT p.project_id) AS project_count,
ROUND(AVG(e.salary), 2) AS avg_salary,
ROUND(
COUNT(DISTINCT p.project_id) / NULLIF(COUNT(DISTINCT e.emp_id), 0),
2
) AS projects_per_employee
FROM departments d
LEFT JOIN employees e
ON d.dept_name = e.department
LEFT JOIN projects p
ON d.dept_name = p.department
GROUP BY d.dept_name
ORDER BY projects_per_employee DESC;
```
---
## 9️⃣ Top Customer per City
**Question:**
For each city, find the highest-spending customer and their total spend.
**Hint:**
Aggregate per customer per city, then rank within city.
**SQL:**
```sql
SELECT
city,
name,
total_spent
FROM (
SELECT
c.city,
c.name,
SUM(o.total) AS total_spent,
RANK() OVER (PARTITION BY c.city ORDER BY SUM(o.total) DESC) AS rnk
FROM customers c
JOIN orders o
ON c.name = o.customer_name
GROUP BY c.city, c.name
) ranked
WHERE rnk = 1;
```
---
## 10️⃣ Employee Hiring Trends
**Question:**
Group employees by hire year and show number of hires, average salary, and change compared to previous year.
**Hint:**
Use `YEAR(hire_date)` + `LAG()`.
**SQL:**
```sql
SELECT
hire_year,
hires,
avg_salary,
avg_salary - LAG(avg_salary) OVER (ORDER BY hire_year) AS yoy_salary_change
FROM (
SELECT
YEAR(hire_date) AS hire_year,
COUNT(*) AS hires,
ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY YEAR(hire_date)
) yearly;
```
---
# 🔥 Senior-Level Questions (11–20)
## 11️⃣ Department Salary Compression
**Question:**
Detect departments where `(max salary / min salary) < 1.5`.
**Hint:**
Use `MAX()` and `MIN()` over partitions.
**SQL:**
```sql
SELECT DISTINCT
department
FROM (
SELECT
department,
MAX(salary) OVER (PARTITION BY department) AS max_sal,
MIN(salary) OVER (PARTITION BY department) AS min_sal
FROM employees
) t
WHERE max_sal / min_sal < 1.5;
```
---
## 12️⃣ Top 25% Department Earners
**Question:**
Find employees not top-paid overall but in top 25% of their department.
**Hint:**
Use `PERCENT_RANK()` or `NTILE()`.
**SQL:**
```sql
SELECT *
FROM (
SELECT
emp_id,
first_name,
last_name,
department,
salary,
PERCENT_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS pr
FROM employees
) t
WHERE pr <= 0.25
AND salary < (SELECT MAX(salary) FROM employees);
```
---
## 13️⃣ Department Budget Efficiency
**Question:**
Compute `(total project budget) / (total employee salary)` and rank departments.
**Hint:**
Multiple aggregates + NULL-safe division.
**SQL:**
```sql
SELECT
d.dept_name,
SUM(p.budget) AS total_project_budget,
SUM(e.salary) AS total_salary,
ROUND(SUM(p.budget) / NULLIF(SUM(e.salary),0),2) AS budget_efficiency
FROM departments d
LEFT JOIN employees e ON d.dept_name = e.department
LEFT JOIN projects p ON d.dept_name = p.department
GROUP BY d.dept_name
ORDER BY budget_efficiency DESC;
```
---
## 14️⃣ Salary Anomaly Detection
**Question:**
Employees with salary > 2 standard deviations above department average.
**Hint:**
Use `AVG()` + `STDDEV_POP()` as window functions.
**SQL:**
```sql
SELECT *
FROM (
SELECT *,
AVG(salary) OVER (PARTITION BY department) AS dept_avg,
STDDEV_POP(salary) OVER (PARTITION BY department) AS dept_std
FROM employees
) t
WHERE salary > dept_avg + 2 * dept_std;
```
---
## 15️⃣ Customer Revenue Dependence
**Question:**
Find customers contributing more than 40% of total revenue.
**Hint:**
SUM() over entire dataset vs per-customer sum.
**SQL:**
```sql
SELECT customer_name, SUM(total) AS total_spent,
SUM(total)/SUM(SUM(total)) OVER () AS revenue_share
FROM orders
GROUP BY customer_name
HAVING revenue_share > 0.4;
```
---
## 16️⃣ Project Staffing Imbalance
**Question:**
Flag departments where project budgets high but salaries low (top 50% project budget, bottom 50% salary).
**Hint:**
Use rankings separately on project budget and salaries.
**SQL:**
```sql
WITH dept_stats AS (
SELECT
d.dept_name,
SUM(p.budget) AS total_project_budget,
AVG(e.salary) AS avg_salary
FROM departments d
LEFT JOIN projects p ON d.dept_name = p.department
LEFT JOIN employees e ON d.dept_name = e.department
GROUP BY d.dept_name
),
ranked AS (
SELECT *,
NTILE(2) OVER (ORDER BY total_project_budget DESC) AS budget_rank,
NTILE(2) OVER (ORDER BY avg_salary ASC) AS salary_rank
FROM dept_stats
)
SELECT *
FROM ranked
WHERE budget_rank = 1 AND salary_rank = 1;
```
---
## 17️⃣ Customer Lifetime Value (Running Total)
**Question:**
For each customer, show running total spend and point where 50% of lifetime value is crossed.
**Hint:**
Cumulative SUM() over ordered dates.
**SQL:**
```sql
WITH customer_total AS (
SELECT customer_name, SUM(total) OVER (PARTITION BY customer_name ORDER BY order_date) AS running_total,
SUM(total) OVER (PARTITION BY customer_name) AS lifetime_total,
order_date
FROM orders
)
SELECT *
FROM customer_total
WHERE running_total >= 0.5 * lifetime_total;
```
---
## 18️⃣ Hiring Quality Trend
**Question:**
Compare new-hire average salary vs existing workforce average at hire time.
**Hint:**
Separate cohorts using hire_date and compare averages.
**SQL:**
```sql
SELECT hire_year, AVG(salary) AS new_hire_avg,
AVG(AVG(salary)) OVER (ORDER BY hire_year ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS existing_avg
FROM employees
CROSS JOIN (SELECT YEAR(hire_date) AS hire_year FROM employees) years
WHERE YEAR(hire_date) = hire_year
GROUP BY hire_year;
```
---
## 19️⃣ Revenue Volatility Index
**Question:**
Compute standard deviation of month-over-month revenue changes.
**Hint:**
Use `LAG()` + `STDDEV_POP()`.
**SQL:**
```sql
WITH monthly AS (
SELECT DATE_FORMAT(order_date,'%Y-%m') AS month,
SUM(total) AS revenue
FROM orders
GROUP BY DATE_FORMAT(order_date,'%Y-%m')
),
mom AS (
SELECT month, revenue,
revenue - LAG(revenue) OVER (ORDER BY month) AS revenue_change
FROM monthly
)
SELECT STDDEV_POP(revenue_change) AS revenue_volatility
FROM mom
WHERE revenue_change IS NOT NULL;
```
---
## 20️⃣ Window-Only Challenge (No GROUP BY)
**Question:**
Without using `GROUP BY`, compute total revenue, average order value, and top customer by spend.
**Hint:**
Use full-partition window functions.
**SQL:**
```sql
SELECT DISTINCT
SUM(total) OVER () AS total_revenue,
AVG(total) OVER () AS avg_order_value,
FIRST_VALUE(customer_name) OVER (PARTITION BY 1 ORDER BY SUM(total) OVER (PARTITION BY customer_name) DESC) AS top_customer
FROM orders;
```
---
This Markdown file now contains **all 20 questions**, **hints**, and **ready-to-run SQL answers** for MySQL 8.0+.
---
