5⃣ frequently Asked SQL Interview Questions with Answers in data analyst interviews

📍1. Write a SQL query to find the average purchase amount for each customer. Assume you have two tables: Customers (CustomerID, Name) and Orders (OrderID, CustomerID, Amount).

SELECT c.CustomerID, c. Name, AVG(o.Amount) AS AveragePurchase
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c. Name;

📍2. Write a query to find the employee with the minimum salary in each department from a table Employees with columns EmployeeID, Name, DepartmentID, and Salary.

SELECT e1.DepartmentID, e1.EmployeeID, e1 .Name, e1.Salary
FROM Employees e1
WHERE Salary = (SELECT MIN(Salary) FROM Employees e2 WHERE e2.DepartmentID = e1.DepartmentID);

📍3. Write a SQL query to find all products that have never been sold. Assume you have a table Products (ProductID, ProductName) and a table Sales (SaleID, ProductID, Quantity).

SELECT p.ProductID, p.ProductName
FROM Products p
LEFT JOIN Sales s ON p.ProductID = s.ProductID
WHERE s.ProductID IS NULL;

📍4. Given a table Orders with columns OrderID, CustomerID, OrderDate, and a table OrderItems with columns OrderID, ItemID, Quantity, write a query to find the customer with the highest total order quantity.

SELECT o.CustomerID, SUM(oi.Quantity) AS TotalQuantity
FROM Orders o
JOIN OrderItems oi ON o.OrderID = oi.OrderID
GROUP BY o.CustomerID
ORDER BY TotalQuantity DESC
LIMIT 1;

📍5. Write a SQL query to find the earliest order date for each customer from a table Orders (OrderID, CustomerID, OrderDate).

SELECT CustomerID, MIN(OrderDate) AS EarliestOrderDate
FROM Orders
GROUP BY CustomerID;

🤔Are you looking for some new project ideas to include in your Portfolio❓

👉 Here are 3 unique ideas for you:

1️⃣ Summer Olympics
Dataset : https://www.kaggle.com/datasets/divyansh22/summer-olympics-medals

2️⃣ Food Nutrition
Dataset : https://www.kaggle.com/datasets/utsavdey1410/food-nutrition-dataset/data

3️⃣ Mental health
Dataset : https://www.kaggle.com/datasets/programmerrdai/mental-health-dataset/data

Datasets Name: Leukemia Classification

Datasets Name: Sign Language Detection using Images

Datasets Name: Football Player Detection YOLOv8

Datasets Name: Amazon Reviews Data 2023

Datasets Name: Gold Price & Relevant Metrics

Datasets Name: Ad Click Prediction Dataset

Datasets Name: Health and sleep statistics

🚨30 FREE Dataset Sources for Data Science Projects🔥

Data Simplifier: https://datasimplifier.com/best-data-analyst-projects-for-freshers/

US Government Dataset: https://www.data.gov/

Open Government Data (OGD) Platform India: https://data.gov.in/

The World Bank Open Data: https://data.worldbank.org/

Data World: https://data.world/

BFI - Industry Data and Insights: https://www.bfi.org.uk/data-statistics

The Humanitarian Data Exchange (HDX): https://data.humdata.org/

Data at World Health Organization (WHO): https://www.who.int/data

FBI’s Crime Data Explorer: https://crime-data-explorer.fr.cloud.gov/

AWS Open Data Registry: https://registry.opendata.aws/

FiveThirtyEight: https://data.fivethirtyeight.com/

IMDb Datasets: https://www.imdb.com/interfaces/

Kaggle: https://www.kaggle.com/datasets

UCI Machine Learning Repository: https://archive.ics.uci.edu/ml/index.php

Google Dataset Search: https://datasetsearch.research.google.com/

Nasdaq Data Link: https://data.nasdaq.com/

Recommender Systems and Personalization Datasets: https://cseweb.ucsd.edu/~jmcauley/datasets.html

Reddit - Datasets: https://www.reddit.com/r/datasets/

Open Data Network by Socrata: https://www.opendatanetwork.com/

Climate Data Online by NOAA: https://www.ncdc.noaa.gov/cdo-web/

Azure Open Datasets: https://azure.microsoft.com/en-us/services/open-datasets/

IEEE Data Port: https://ieee-dataport.org/

Wikipedia: Database: https://dumps.wikimedia.org/

BuzzFeed News: https://github.com/BuzzFeedNews/everything

Academic Torrents: https://academictorrents.com/

Yelp Open Dataset: https://www.yelp.com/dataset

The NLP Index by Quantum Stat: https://index.quantumstat.com/

Computer Vision Online: http://www.computervisiononline.com/dataset

Visual Data Discovery: https://www.visualdata.io/

Roboflow Public Datasets: https://public.roboflow.com/

Computer Vision Group, TUM: https://vision.in.tum.de/data/datasets

Data Analytics Projects for Beginners 👇


Web Scraping
https://github.com/shreyaswankhede/IMDb-Web-Scraping-and-Sentiment-Analysis

Product Price Scraping and Analysis
https://github.com/CodesdaLu/Web-Scrapping

News Scraping
https://github.com/rohit-yadav/scraping-news-arti Les

IPL Analysis
https://github.com/Yashmenaria1/IPL-Data-Exploration

Customer Churn Prediction
https://github.com/Pradnya1208/Telecom-Customer-Churn-prediction

Employee’s Performance for HR Analytics
https://www.kaggle.com/code/rajatraj0502/employee-s-performance-for-hr-analytics

Food Price Prediction
https://github.com/VectorInstitute/foodprice-forecasting

Join for more: https://news.1rj.ru/str/sqlproject