SQL Brain Teasers: Can You Solve These Complex Queries

Given a table Sales with columns Region, Country, Product, and Amount, write a query that returns the total amount of sales by region, country, and product, as well as the grand total for all sales. Use the ROLLUP operation to include subtotal rows for each region and country, and a grand total row for all sales.

For example, if the table contains the following data:

| Region | Country | Product | Amount |
| — — — — -| — — — — -| — — — — -| — — — — |
| Europe | France | A | 1000 |
| Europe | France | B | 2000 |
| Europe | Germany | A | 1500 |
| Europe | Germany | B | 2500 |
| America | USA | A | 1200 |
| America | USA | B | 1800 |
| America | Canada | A | 800 |
| America | Canada | B | 1200 |

The query should return the following result:

| Region | Country | Product | Total Amount |
| — — — — -| — — — — -| — — — — -| — — — — — — — |
| America | Canada | A | 800 |
| America | Canada | B | 1200 |
| America | USA | A | 1200 |
| America | USA | B | 1800 |
| America | NULL | NULL | 5000 |
| Europe | France | A | 1000 |
| Europe | France | B | 2000 |
| Europe | Germany | A | 1500 |
| Europe | Germany | B | 2500 |
| Europe | NULL | NULL | 7000 |
| NULL | NULL | NULL | 12000 |

SELECT
    IFNULL(Departments.DepartmentName, 'All Departments') AS Department,
    COUNT(Employees.EmployeeID) AS NumEmployees,
    AVG(EmployeeSalaries.Salary) AS AvgSalary
FROM
    Employees
    INNER JOIN EmployeeSalaries ON Employees.EmployeeID = EmployeeSalaries.EmployeeID
    INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID
WHERE
    DATEDIFF(CURDATE(), EmployeeSalaries.StartDate) >= 1095 AND (EmployeeSalaries.EndDate IS NULL OR DATEDIFF(EmployeeSalaries.EndDate, EmployeeSalaries.StartDate) >= 1095)
GROUP BY
    Departments.DepartmentID WITH ROLLUP
HAVING
    COUNT(Employees.EmployeeID) >= 10
ORDER BY
    AvgSalary DESC
LIMIT
    3;

Given a table Orders with columns OrderID, CustomerID, OrderDate, and TotalAmount, write a query that returns the top 10 customers who have made the largest increase in total amount spent from the first month they made a purchase to the last month available in the table. The result should include the customer name, their first month of purchase, their last month of purchase, and the difference in total amount spent between those two months.

WITH FirstPurchase AS (
    SELECT CustomerID, MIN(OrderDate) AS FirstMonth, SUM(TotalAmount) AS FirstTotal
    FROM Orders
    GROUP BY CustomerID
), LastPurchase AS (
    SELECT CustomerID, MAX(OrderDate) AS LastMonth, SUM(TotalAmount) AS LastTotal
    FROM Orders
    GROUP BY CustomerID
)
SELECT c.CustomerName, fp.FirstMonth, lp.LastMonth, lp.LastTotal - fp.FirstTotal AS TotalIncrease
FROM Customers c
INNER JOIN FirstPurchase fp ON c.CustomerID = fp.CustomerID
INNER JOIN LastPurchase lp ON c.CustomerID = lp.CustomerID
ORDER BY TotalIncrease DESC
LIMIT 10;

Given two tables, Employees and EmployeeSalaries, with the following columns: Employees: EmployeeID, FirstName, LastName, DepartmentID EmployeeSalaries: EmployeeID, Salary, StartDate, EndDate Write a query that returns the top 3 departments with the highest average salary of employees who have worked in that department for at least 3 years. Only include employees whose EndDate is null or greater than 3 years from their StartDate.

SELECT TOP 3 
    e.DepartmentID, 
    AVG(es.Salary) AS AverageSalary
FROM 
    Employees e 
    JOIN EmployeeSalaries es ON e.EmployeeID = es.EmployeeID 
WHERE 
    es.EndDate IS NULL OR es.EndDate > DATEADD(YEAR, 3, es.StartDate)
GROUP BY 
    e.DepartmentID 
HAVING 
    COUNT(CASE WHEN es.EndDate IS NULL THEN 1 WHEN es.EndDate > DATEADD(YEAR, 3, es.StartDate) THEN 1 ELSE NULL END) >= 3
ORDER BY 
    AVG(es.Salary) DESC;

Given a table Transactions with columns TransactionId, AccountId, TransactionDate, Amount, and Type, write a query that returns the account ids and their corresponding total balance (sum of amounts) as of the latest transaction date for each account. Note that a Type value of ‘debit’ indicates a negative transaction and a Type value of ‘credit’ indicates a positive transaction.

For example, if the table contains the following data:

| TransactionId | AccountId | TransactionDate | Amount | Type |
| — — — — — — — -| — — — — — -| — — — — — — — — | — — — — | — — — — |
| 1 | 1 | 2022–01–01 | 100 | credit |
| 2 | 2 | 2022–01–01 | 200 | credit |
| 3 | 1 | 2022–01–02 | -50 | debit |
| 4 | 2 | 2022–01–03 | -75 | debit |
| 5 | 3 | 2022–01–03 | 300 | credit |

The query should return the following:

| AccountId | Total Balance |
| — — — — — -| — — — — — — — |
| 1 | 50 |
| 2 | 125 |
| 3 | 300 |

SELECT AccountId, SUM(CASE WHEN Type = 'credit' THEN Amount ELSE -Amount END) AS 'Total Balance'
FROM Transactions
WHERE TransactionDate = (SELECT MAX(TransactionDate) FROM Transactions)
GROUP BY AccountId;

Given two tables, Customers and Orders, with the following columns: Customers: CustomerID, CustomerName, City Orders: OrderID, CustomerID, OrderDate, TotalAmount Write a query that returns the top 5 cities with the highest total amount spent by customers in that city. The result should include the city name and the total amount spent.

SELECT c.City, SUM(o.TotalAmount) as TotalAmount
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.City
ORDER BY TotalAmount DESC
LIMIT 5;

Given a table Products with columns ProductId, ProductName, Category, and Price, write a query that returns the top 3 categories with the highest average price of products. Exclude any categories with less than 5 products.

SELECT Category, AVG(Price) AS AvgPrice
FROM Products
GROUP BY Category
HAVING COUNT(*) >= 5
ORDER BY AvgPrice DESC
LIMIT 3;

Write a query that returns the top 10 customers who have spent the most money on the website in the last month, I would follow these steps:

1. Identify the tables in the database that contain the necessary information, such as customer orders and order amounts.
2. Join these tables together to create a comprehensive view of the data.
3. Filter the data to only include orders from the last month.
4. Group the data by customer and calculate the total amount spent by each customer.
5. Sort the results in descending order by total amount spent.
6. Limit the results to the top 10 customers.

SELECT customer_id, SUM(order_amount) AS total_spent
FROM orders
WHERE order_date >= DATEADD(month, -1, GETDATE())
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 10;

This query uses the SUM function to calculate the total amount spent by each customer, and the GROUP BY clause to group the data by customer. The WHERE clause filters the data to only include orders from the last month, and the ORDER BY clause sorts the results in descending order by total amount spent. Finally, the LIMIT clause limits the results to the top 10 customers.

Your company has a database containing customer information, including their orders and shipping addresses. You are asked to write a query that returns the number of unique customers who have placed an order in each state. How would you do this?

To write a query that returns the number of unique customers who have placed an order in each state, I would follow these steps:

1. Identify the tables in the database that contain the necessary information, such as the orders table and the customer table.
2. Join these tables together to create a comprehensive view of the data.
3. Group the data by state and customer, and count the number of unique customers for each state.
4. Sort the results by state name.

Here’s an example SQL query that would achieve this task:

SELECT c.state, COUNT(DISTINCT o.customer_id) AS num_customers
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id
GROUP BY c.state
ORDER BY c.state;

This query joins the orders table with the customers table on the customer_id column, creating a comprehensive view of the data. The `COUNT(DISTINCT)` function is used to count the number of unique customers for each state. The `GROUP BY` clause groups the data by state and customer, while the `ORDER BY` clause sorts the results by state name.

You work for a financial institution and are asked to write a query that calculates the average balance of customer accounts by age group. How would you approach this task?

SELECT 
  CASE 
    WHEN age < 18 THEN 'Under 18'
    WHEN age >= 18 AND age < 25 THEN '18-24'
    WHEN age >= 25 AND age < 35 THEN '25-34'
    WHEN age >= 35 AND age < 45 THEN '35-44'
    WHEN age >= 45 AND age < 55 THEN '45-54'
    ELSE '55+'
  END AS age_group,
  AVG(balance) AS avg_balance
FROM accounts
JOIN customers
ON accounts.customer_id = customers.customer_id
GROUP BY age_group
ORDER BY age_group;

In this query, I use a CASE statement to group customers into different age groups based on their age. Then, I join the accounts and customers tables on the customer_id column, group the customers by their age group, and calculate the average account balance for each group using the AVG function. Finally, I order the results by age group. This query will return the average account balance for each age group.

To return the average salary for each job title, broken down by department, I would write a SQL query that joins the employees, job_titles, and departments tables, groups the data by job title and department, and calculates the average salary for each group. Here's an example of how the query could look

SELECT 
  departments.department_name,
  job_titles.job_title,
  AVG(employees.salary) AS avg_salary
FROM employees
JOIN job_titles ON employees.job_title_id = job_titles.job_title_id
JOIN departments ON employees.department_id = departments.department_id
GROUP BY departments.department_name, job_titles.job_title
ORDER BY departments.department_name, job_titles.job_title;

In this query, I join the employees, job_titles, and departments tables on their respective ID columns, group the data by department name and job title, and calculate the average salary for each group using the AVG function. Finally, I order the results by department name and job title. This query will return the average salary for each job title, broken down by department.

Given a table Orders with columns OrderId, CustomerId, OrderDate, and TotalAmount, write a query that returns the top 5 customers who have made the largest increase in total amount spent between the current month and the previous month.

Assume that the current month is the month in which the query is run, and the previous month is the month immediately preceding the current month. Also, assume that the table contains data for all months, and not just the current or previous month.

SELECT o1.CustomerId, SUM(o1.TotalAmount) - SUM(o2.TotalAmount) AS AmountIncrease
FROM Orders o1
INNER JOIN Orders o2 ON o1.CustomerId = o2.CustomerId
WHERE MONTH(o1.OrderDate) = MONTH(CURRENT_DATE())
AND MONTH(o2.OrderDate) = MONTH(CURRENT_DATE()) - 1
AND YEAR(o1.OrderDate) = YEAR(CURRENT_DATE())
AND YEAR(o2.OrderDate) = YEAR(CURRENT_DATE())
GROUP BY o1.CustomerId
ORDER BY AmountIncrease DESC
LIMIT 5;