complex_sql.md

QueryCraft — Complex SQL Statements for Engine Testing

---

🎬 Sakila Database

### 1. Top 5 Actors by Total Film Revenue (JOIN + GROUP BY + Subquery)



### 2. Monthly Revenue Trend Per Store (Window Function + DATE_FORMAT)



### 3. Customers Who Rented Every Film in a Specific Category (NOT EXISTS + Correlated Subquery)



### 4. Film Rental Ranking Within Each Category (RANK + CTE)



### 5. Customers with Above-Average Spending Per City (HAVING + Nested AVG)

---

🌍 World Database

### 1. Countries with Life Expectancy Above Their Continent Average (CTE + AVG Window)



### 2. Top 3 Most Spoken Languages Per Continent (RANK + Window Function)



### 3. Countries Whose Capital is Their Largest City (JOIN + Subquery Filter)



### 4. GDP Per Capita vs Literacy Rate Correlation View (CASE + Computed Columns)



### 5. Regions with Most Official Languages and Population Coverage (GROUP BY + HAVING + COUNT)

---

🚗 ClassicModels Database

### 1. Sales Rep Performance: Revenue vs Target with Rank (CTE + Window Function)



### 2. Products Never Ordered (LEFT JOIN + IS NULL)



### 3. Monthly Order Revenue with Running Total (Window Function + DATE_FORMAT)



### 4. Customers Who Spent More Than Their Credit Limit (Subquery + HAVING)



### 5. Product Line Revenue Share with Percentage (CTE + RATIO_TO_REPORT Simulation)

---

🧑‍💼 Employees Database

### 1. Top 5 Highest Paid Employees Per Department (CTE + DENSE_RANK)



### 2. Average Salary Growth Per Department Over the Years (GROUP BY + Window)



### 3. Employees with Multiple Title Changes (Subquery + COUNT + HAVING)



### 4. Managers Who Earned Less Than Their Department's Average Employee Salary (Nested Subquery)



### 5. Gender Pay Gap Per Department (CASE + AVG + Pivot Simulation)