These questions cover what data analysts would face at an entry-level to the intermediate range for SQL. Depending on the organization and role, the requirement for SQL knowledge may increase. However, these will cover the main concepts from joins, subqueries, grouping, alias, and filtering. You can practice all these SQL queries on our free platform: Test Your SQL Query on This Platform
1. Find the customer ID with the number of orders sorted in descending order (Table Used: Orders)
select customerid, count(orderid) as order_count from orders group by customerid order by order_count desc
2a. Find the customer ID with the maximum number of orders (Table Used: Orders)
select customerid, count(orderid) as order_count from orders group by customerid order by order_count desc limit 1
2b. Find the customer ID with the maximum number of orders (Table Used: Orders) – Alternative Solution – Using Subqueries
select customerid, count(orderid) as order_count from orders group by customerid having order_count in ( select max(order_count) from( (select customerid, count(orderid) as order_count from orders group by customerid) a))
3. Find the customer Name and Customer ID with the number of orders sorted in descending order (Table Used: Orders and Customer)
select a.customername, a.customerid, count(distinct b.orderID) as order_count from customers as a inner join orders b on a.customerid = b.customerid group by a.customername, a.customerid order by order_count desc
4. Find the Customer Name, Customer ID with the number of products they purchased in a single order transaction sorted by descending order.
Select C.CustomerName,C.CustomerID,B.OrderID,count(distinct A.ProductID) as Number_of_Product from orderdetails as A inner join orders as B on A.OrderID=B.OrderID inner join customers as C on C.CustomerID=B.CustomerID group by C.CustomerName,C.CustomerID,B.OrderID order by Number_of_Product desc
5. Find the Average Orders per Total Customer for every employee ID and filter the ones which have a value greater than 1.8
Select EmployeeID, count(distinct OrderID)/count(distinct CustomerID) as Avg from orders group by EmployeeID having Avg>1.8 order by Avg desc
6. Find the number of Orders by each EmployeeID on a month-by-month basis and place a (Premium Customer) flag on customers which have more than 5 orders on a Month-by-month basis.
Select EmployeeID, year(OrderDate) as Year, month(OrderDate) as Month, count(OrderID) as OrderCount, case when count(OrderID)>5 then 1 else 0 end as Premium_Customer from orders group by EmployeeID, year(OrderDate),month(OrderDate) order by Year, Month, OrderCount desc
7. Use Inner join to filter Employees that are born post-1960
select b.* from (Select EmployeeID from employees where year(BirthDate)>1960) a inner join (Select EmployeeID, year(OrderDate) as Year, month(OrderDate) as Month, count(OrderID) as OrderCount, case when count(OrderID)>5 then 1 else 0 end as Premium_Customer from orders group by EmployeeID, year(OrderDate),month(OrderDate) order by Year, Month, OrderCount desc) b on a.EmployeeID=b.EmployeeID
8. Compare Sales of Different Products by developing a Pairwise Table, for example, the first record should be one product’s sales compared with other productSales.
select *, abs(Sales_2-Sales_1) as Gap from (Select ProductID as Product1, sum(Quantity) as Sales_1 from orderdetails group by ProductID) as a cross join (Select ProductID as Product2, sum(Quantity) as Sales_2 from orderdetails group by ProductID) as b where Product1>Product2
9. In the previous question find the pair of products with the highest difference in sales
You can watch the live code and explanation of all these queries here: