1. Find the Second Highest Salary in a Table

Find the Top 3 Salaries for Each Department
SELECT department, name, salary
FROM (
SELECT
department,
name,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
) ranked
WHERE rank <= 3;
calculate the Difference Between Consecutive Rows
SELECT date, value,
value - LAG(value) OVER (ORDER BY date) AS difference
FROM your_table;
Find the Nth Highest Salary Without Using Subqueries

SELECT DISTINCT TOP 1 e1.salary

FROM employees e1, employees e2
WHERE N - 1 = (SELECT COUNT(DISTINCT e2.salary)
FROM employees e2
WHERE e2.salary > e1.salary);
ind the Most Common Element in a Column

SELECT TOP 1 column_name, COUNT(*) as frequency

FROM table_name
GROUP BY column_name
ORDER BY frequency DESC;
Calculate a Cumulative Sum Within Groups
SELECT id, group_id, value,
SUM(value) OVER (PARTITION BY group_id ORDER BY id) AS cumulative_sum
FROM your_table;
Transpose Rows into Columns

SELECT
MAX(CASE WHEN attribute = 'A' THEN value END) AS A,

MAX(CASE WHEN attribute = 'B' THEN value END) AS B,
MAX(CASE WHEN attribute = 'C' THEN value END) AS C
FROM your_table;
Find the Maximum Consecutive Days of Sales

SELECT MIN(date) AS start_date,

MAX(date) AS end_date,
COUNT(*) AS consecutive_days
FROM (
SELECT date,
DATEADD(day, -ROW_NUMBER() OVER (ORDER BY date), date) AS grp
FROM sales
) sub
GROUP BY grp;

ind the Top N Records Based on a Condition
SELECT TOP N *
FROM your_table
WHERE condition_column = 'condition_value'
ORDER BY order_column;

Certainly! Here are some middle-level SQL puzzle interview questions for data analysts:

1. Puzzle: Find the second highest salary from the "Employees" table.
Expected Solution:
SELECT MAX(Salary) AS SecondHighestSalary
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees)

2. Puzzle: Identify duplicate records in a table called "Customers" based on the "Email" column.

Expected Solution:
SELECT Email, COUNT(*) AS DuplicateCount
FROM Customers
GROUP BY Email
HAVING COUNT(*) > 1

3. Puzzle: Calculate the cumulative sum of a column called "Revenue" in a table named "Sales", ordered by date.

Expected Solution:
SELECT Date, Revenue, SUM(Revenue) OVER (ORDER BY Date) AS CumulativeSum
FROM Sales

4. Puzzle: Find the top 3 product categories with the highest average sales in a table named "Products".

Expected Solution:
SELECT Category, AVG(Sales) AS AvgSales
FROM Products
GROUP BY Category
ORDER BY AvgSales DESC
LIMIT 3

5. Puzzle: Calculate the percentage of total sales contributed by each product category in a table named "Sales".

Expected Solution:
SELECT Category, SUM(Sales) / (SELECT SUM(Sales) FROM Sales) * 100 AS Percentage
FROM Sales
GROUP BY Category

6. Puzzle: Compute the difference between the maximum and minimum order quantities for each product in a table named "Orders".

Expected Solution:
SELECT Product, MAX(Quantity) - MIN(Quantity) AS QuantityDifference
FROM Orders
GROUP BY Product

7. Puzzle: Perform a self-join on a table named "Employees" to find pairs of employees who have the same manager.

Expected Solution:
SELECT e1.EmployeeID, e2.EmployeeID
FROM Employees e1 JOIN Employees e2
ON e1.ManagerID = e2.ManagerID AND e1.EmployeeID <> e2.EmployeeID

8. Puzzle: Calculate the average order value for each customer in a table named "Orders".

Expected Solution:
SELECT Customer, AVG(OrderValue) AS AverageOrderValue
FROM Orders
GROUP BY Customer