SQL Project Orders

In this article, we will explore five SQL questions from the book 1000+ SQL Interview Questions & Answers by Zero Analyst. These questions cover various topics, including data analysis, case statements, and window functions. We will provide detailed explanations and solutions for each question.

Q.1: Find the total revenue generated by each product category for Flipkart

Problem Statement

Find the total revenue generated by each product category for Flipkart.

Solution

To solve this problem, we need to join the products table with the orders table on the product_id column. We then group the result by the category column and calculate the total revenue for each category.

SELECT 
	p.category,
	SUM(p.price * o.quantity)
FROM orders as o
JOIN
products as p
ON p.product_id = o.product_id
GROUP BY p.category

Explanation

In this solution, we first join the orders table with the products table on the product_id column. This allows us to access the price column from the products table. We then group the result by the category column and calculate the total revenue for each category by multiplying the price with the quantity and summing the result.

Example Use Case

Suppose we have the following data in the products table:

product_id	product_name	category	price
1	Smartphone	Electronics	15000.00
2	Shoes	Footwear	2000.00
3	Laptop	Electronics	50000.00
4	T-shirt	Clothing	500.00
5	Headphones	Electronics	1500.00

And the following data in the orders table:

order_id	product_id	quantity
1	1	3
2	2	4
3	3	2
4	4	10
5	5	5

Running the above query will return the following result:

category	total_revenue
Electronics	135000.00
Footwear	8000.00
Clothing	5000.00

Q.2: Write an SQL query to find the name of the product with the highest price in each country

Problem Statement

Write an SQL query to find the name of the product with the highest price in each country.

Solution

To solve this problem, we need to join the products table with the suppliers table on the supplier_id column. We then use the ROW_NUMBER() function to rank the products by price in each country and select the product with the highest price.

WITH ranked_table
AS
(SELECT 
	s.country,
	p.product_name,
	p.price,
	ROW_NUMBER() OVER(PARTITION BY s.country ORDER BY p.price desc) as rn
FROM products as p
JOIN
suppliers as s
ON s.supplier_id = p.supplier_id
)
SELECT * FROM ranked_table
WHERE rn = 1

Explanation

In this solution, we first join the products table with the suppliers table on the supplier_id column. This allows us to access the country column from the suppliers table. We then use the ROW_NUMBER() function to rank the products by price in each country. The PARTITION BY clause is used to partition the result by country, and the ORDER BY clause is used to order the products by price in each country. We then select the product with the highest price by selecting the row with rn = 1.

Example Use Case

Suppose we have the following data in the suppliers table:

supplier_id	supplier_name	country
501	alan	India
502	rex	US
503	dodo	India
504	rahul	US
505	zara	Canada
506	max	Canada

And the following data in the products table:

product_id	product_name	supplier_id	price
201	iPhone 14	501	1299
202	iPhone 8	502	999
204	iPhone 13	502	1199
203	iPhone 11	503	1199
205	iPhone 12	502	1199
206	iPhone 14	501	1399
214	iPhone 15	503	1499
207	iPhone 15	505	1499
208	iPhone 15	504	1499
209	iPhone 12	502	1299
210	iPhone 13	502	1199
211	iPhone 11	501	1099
212	iPhone 14	503	1399
213	iPhone 8	502	1099
222	Samsung Galaxy S21	504	1699
223	Samsung Galaxy S20	505	1899
224	Google Pixel 6	501	899
225	Google Pixel 5	502	799
226	OnePlus 9 Pro	503	1699
227	OnePlus 9	502	1999
228	Xiaomi Mi 11	501	899
229	Xiaomi Mi 10	504	699
230	Huawei P40 Pro	505	1099
231	Huawei P30	502	1299
232	Sony Xperia 1 III	503	1199
233	Sony Xperia 5 III	501	999
234	LG Velvet	505	1899
235	LG G8 ThinQ	504	799
236	Motorola Edge Plus	502	1099
237	Motorola One 5G	501	799
238	ASUS ROG Phone 5	503	1999
239	ASUS ZenFone 8	504	999
240	Nokia 8.3 5G	502	899
241	Nokia 7.2	501	699
242	BlackBerry Key2	504	1899
243	BlackBerry Motion	502	799
244	HTC U12 Plus	501	899
245	HTC Desire 20 Pro	505	699
246	Lenovo Legion Phone Duel	503	1499
247	Lenovo K12 Note	504	1499
248	ZTE Axon 30 Ultra	501	1299
249	ZTE Blade 20	502	1599
250	Oppo Find X3 Pro	503	1999

Running the above query will return the following result:

country	product_name	price
India	iPhone 14	1399
US	iPhone 15	1499
Canada	Samsung Galaxy S20	1899

Q.3: Case Statement

Problem Statement

For each employee, determine if they are eligible for a salary increase based on their years of service. If an employee has been with the company for 5 years or more, they are eligible for a '10% Increase'. If they have been with the company for less than 5 years but more than 2 years, they are eligible for a '5% Increase'. Employees with less than 2 years are not eligible for any increase.

Solution

To solve this problem, we can use a case statement to determine the eligibility of each employee for a salary increase.

SELECT *,
	CASE
		WHEN 
		EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM hiredate) >= 5 THEN '10% raise'
		WHEN 
			EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM hiredate) BETWEEN 2 AND 4
			THEN '5%'
		ELSE 'No raise'
	END
		
FROM employees;

Explanation

In this solution, we use a case statement to determine the eligibility of each employee for a salary increase. The case statement checks the number of years the employee has been with the company and returns a string indicating whether they are eligible for a '10% raise', a '5% raise', or 'No raise'.

Example Use Case

Suppose we have the following data in the employees table:

EmployeeID	EmployeeName	HireDate	Salary
1	John Doe	2018-03-01	50000
2	Alice Smith	2021-06-15	45000
3	Bob Brown	201

Q.4: Write a query to find the total number of rides each user has taken

Problem Statement

Write a query to find the total number of rides each user has taken.

Solution

To solve this problem, we can use the GROUP BY clause to group the result by the user_id column and use the COUNT function to count the number of rides for each user.

SELECT 
	user_id,
	COUNT(ride_id),
	SUM(fare) as total_spent
FROM rides
GROUP BY 1

Explanation

In this solution, we use the GROUP BY clause to group the result by the user_id column. This allows us to count the number of rides for each user. We also use the SUM function to calculate the total amount spent by each user.

Example Use Case

Suppose we have the following data in the rides table:

ride_id	user_id	ride_date	ride_distance	fare
1	101	2023-01-01 08:00:00	5.0	15.50
2	102	2023-01-01 09:00:00	10.0	25.00
3	101	2023-01-02 10:00:00	3.5	10.00
4	103	2023-01-02 11:00:00	7.0	18.50
5	101	2023-01-03 12:00:00	4.0	12.00

Running the above query will return the following result:

user_id	total_rides	total_spent
101	3	37.50
102	1	25.00
103	1	18.50

Q.5: Find employees who attended work for three consecutive days

Problem Statement

Find employees who attended work for three consecutive days.

Solution

To solve this problem, we can use the LAG and LEAD functions to check if an employee attended work on the previous and next day.

SELECT 
	DISTINCT employee_id
FROM
(SELECT 
employee_id,
attendance_date as cur_date,
LAG(attendance_date) OVER(PARTITION BY employee_id ORDER BY attendance_date) prev_date,
LEAD(attendance_date) OVER(PARTITION BY employee_id ORDER BY attendance_date) next_date
FROM attendance
) a
WHERE 
	cur_date = prev_date + INTERVAL '1 day'
	AND
	cur_date = next_date - INTERVAL '1 day'

Explanation

In this solution, we use the LAG and LEAD functions to check if an employee attended work on the previous and next day. We then use the WHERE clause to filter the result to only include employees who attended work for three consecutive days.

Example Use Case

Suppose we have the following data in the attendance table:

employee_id	attendance_date
1	2025-01-10
1	2025-01-11
1	2025-01-12
2	2025-01-10
2	2025-01-12
2	2025-01-13
3	2025-01-10
3	2025-01-11
3	2025-01-12
3	2025-01-13

Running the above query will return the following result:

employee_id
1
3

Conclusion

In this article, we have covered five SQL questions from the book 1000+ SQL Interview Questions & Answers by Zero Analyst. We have provided detailed explanations and solutions for each question, including data analysis, case statements, and window functions. We hope that this article has been helpful in understanding and solving these types of SQL problems.

Frequently Asked Questions (FAQs)

Q: What is the difference between the GROUP BY clause and the PARTITION BY clause?

A: The GROUP BY clause is used to group the result by one or more columns, while the PARTITION BY clause is used to partition the result by one or more columns.

Q: How do I use the LAG and LEAD functions in SQL?

A: The LAG function returns the value of a column from a previous row, while the LEAD function returns the value of a column from a next row. You can use these functions to check if an employee attended work on the previous and next day.

Q: What is the difference between the SUM function and the COUNT function?

A: The SUM function is used to calculate the sum of a column, while the COUNT function is used to count the number of rows in a table.

Q: How do I use the CASE statement in SQL?

A: The CASE statement is used to evaluate a condition and return a value based on that condition. You can use this statement to determine the eligibility of an employee for a salary increase.