10 Most Asked SQL Interview Questions
10 Most Asked SQL Interview Questions will help you understand the type of questions asked in SQL interviews in the Analytics domain.
Below are the 10 Most Asked SQL Interview Questions.
Here are a few FREE and paid resources that will help you in making career in Analytics
The Data Monk – We have 2000+ solved interview questions, you can explore all of these for free
The Data Monk Youtube channel
We have ~200 Videos created only based on the most asked topics in SQL, Python, ML, Statistics, Case Stud,ies and other topics. Do subscribe and watch 1-2 video daily.
Link to Channel – The Data Monk
The Data Monk E-book
Full Stack Analytics Professional – Full Stack Analytics Professional – 2200 Most Asked Interview Questions
Data Analyst – 1100+ Most Asked Data Analyst Interview Questions
Business Analyst – 1250+ Most Asked Business Analyst Interview Questions
Data Scientist – 23 e-books covering all the ML Algorithms Interview Questions
The Data Monk Instagram Channel – Where we regularly post about the most asked definitions and questions
Question 1: Suppose you have a database with two tables:
- Customers
- Columns: CustomerID (Primary Key), FirstName, LastName, Email
- Orders
- Columns: OrderID (Primary Key), CustomerID (Foreign Key referencing Customers), OrderDate, TotalAmount
Write an SQL query to retrieve the names and total amounts spent by customers who have made at least two orders, along with the number of orders they’ve made.
Answer 1:
SELECT
c.FirstName,
c.LastName,
COUNT(o.CustomerID) AS NumberOfOrders,
SUM(o.TotalAmount) AS TotalAmountSpent
FROM
Customers c
JOIN
Orders o ON c.CustomerID = o.CustomerID
GROUP BY
c.CustomerID
HAVING
COUNT(o.CustomerID) >= 2;Question 2: Consider a table named Products:
- Columns: ProductID (Primary Key), ProductName, Category, Price
Write an SQL query to find the average price of products in each category, along with the total number of products in that category.
Answer 2:
SELECT
Category,
AVG(Price) AS AveragePrice,
COUNT(*) AS TotalProducts
FROM
Products
GROUP BY
Category;Question 3: You have a table named Employees:
- Columns: EmployeeID (Primary Key), FirstName, LastName, Department, Salary
Write an SQL query to find the top 5 earning employees in each department.
Answer 3:
SELECT
e1.Department,
e1.FirstName,
e1.LastName,
e1.Salary
FROM
Employees e1
WHERE
5 > (
SELECT COUNT(DISTINCT e2.Salary)
FROM Employees e2
WHERE e2.Department = e1.Department AND e2.Salary > e1.Salary
);Question 4 :
Suppose you have a table named Employees with columns: EmployeeID, FirstName, LastName, and Department. Write an SQL query to retrieve the employees who have the same first names and are in the same department.
Answer 4:
SELECT e1.EmployeeID, e1.FirstName, e1.LastName, e1.Department
FROM Employees e1
JOIN Employees e2 ON e1.FirstName = e2.FirstName AND e1.EmployeeID <> e2.EmployeeID
AND e1.Department = e2.Department;
Question 5:
Given a table named Orders with columns: OrderID, OrderDate, and TotalAmount, write an SQL query to find the date with the highest total amount of orders.
Answer 5:
SELECT OrderDate, SUM(TotalAmount) AS Total
FROM Orders
GROUP BY OrderDate
ORDER BY Total DESC
LIMIT 1;Question 6:
Consider a table named Books with columns: BookID, Title, AuthorID, and PublicationYear, and a table named Authors with columns: AuthorID, FirstName, and LastName. Write an SQL query to retrieve the top 3 authors with the highest average publication years.
Answer 6:
SELECT a.AuthorID, a.FirstName, a.LastName, AVG(b.PublicationYear) AS AvgPublicationYear
FROM Authors a
JOIN Books b ON a.AuthorID = b.AuthorID
GROUP BY a.AuthorID, a.FirstName, a.LastName
ORDER BY AvgPublicationYear DESC
LIMIT 3;Question 7:
Suppose you have a table named Transactions with columns: TransactionID, UserID, TransactionDate, and Amount. Write an SQL query to find the UserID of the user who made the highest total amount of transactions.
Answer 7:
SELECT UserID
FROM Transactions
GROUP BY UserID
ORDER BY SUM(Amount)
DESC LIMIT 1;Question 8:
Given a table named Logs with columns: LogID, UserID, and Activity, write an SQL query to find the UserID that has performed all possible activities.
Answer 8:
SELECT UserID
FROM Logs
GROUP BY UserID
HAVING COUNT(DISTINCT Activity) =
(
SELECT COUNT(DISTINCT Activity) FROM Logs
);Question 9: What are the Constraints in SQL?
Constraints are used to specify the rules concerning data in the table. It can be applied for single or multiple fields in an SQL table during the creation of the table or after creating using the ALTER TABLE command. The constraints are:
NOT NULL – Restricts NULL value from being inserted into a column.
CHECK – Verifies that all values in a field satisfy a condition.
DEFAULT – Automatically assigns a default value if no value has been specified for the field.
UNIQUE – Ensures unique values are inserted into the field.
INDEX – Indexes a field providing faster retrieval of records.
PRIMARY KEY – Uniquely identifies each record in a table.
FOREIGN KEY – Ensures referential integrity for a record in another table.
Question10: How do the SQL commands flow at the back end?
Order of execution for an SQL query
1) FROM, including JOINs
2) WHERE
3) GROUP BY
4) HAVING
5) WINDOW Functions
6) SELECT
7) DISTINCT
8) UNION
9) ORDER BY
10) LIMIT AND OFFSET
Case Study for Analytics Interview
Case Study Statement – Suppose you work for Amazon, suggest top 20 KPIs that you should have on a CXO level dashboard
Sales and Revenue Metrics:
- Total Revenue: The total amount of money generated from all sales on the platform.
- Gross Merchandise Volume (GMV): The total value of goods sold through the platform, including Amazon’s and third-party sellers’ sales.
- Average Order Value (AOV): The average amount spent by customers in each order.
- Conversion Rate: The percentage of website visitors who make a purchase.
- Customer Lifetime Value (CLTV): The predicted total value a customer will generate over their lifetime as an Amazon customer.
Operational Metrics:
6. Order Fulfillment Rate: The percentage of orders fulfilled on time and accurately.
- Inventory Turnover: The rate at which inventory is sold and replaced within a specific period.
- Fulfillment Center Utilization: The capacity utilization of Amazon’s fulfillment centers.
- Return Rate: The percentage of orders returned by customers.
Customer Experience Metrics:
10. Customer Satisfaction (CSAT): Customer satisfaction rating based on post-purchase surveys.
- Net Promoter Score (NPS): A measure of customer loyalty and willingness to recommend Amazon to others.
- Customer Retention Rate: The percentage of customers who make repeat purchases.
Marketplace Metrics:
13. Number of Sellers: The total number of third-party sellers on Amazon’s marketplace.
- Third-Party Seller Sales: The percentage of overall sales generated by third-party sellers.
- Seller Feedback Rating: Ratings and feedback given by customers to third-party sellers.
Digital Metrics:
16. Amazon Web Services (AWS) Revenue: Revenue generated from Amazon’s cloud computing platform.
- Amazon Prime Membership Growth: The growth rate of Amazon Prime subscriptions.
- Digital Content Consumption: Metrics related to e-books, video streaming, and other digital content.
Logistics and Delivery Metrics:
19. Delivery Speed: Average time taken to deliver products to customers.
- Fulfillment Costs: Costs associated with warehousing, packaging, and shipping.
