Data Analytics Projects - SQL, Excel, Tableau, Python & Power BI Interview Resources

SQL Project Series #3 E-Commerce Sales Analysis – Intermediate SQL Business Questions Let's solve more real-world business problems using SQL. Business Questions 11. Find Repeat Customers SELECT customer_id, COUNT(order_id) AS total_orders FROM orders GROUP BY customer_id HAVING COUNT(order_id) > 1; 12. Find Customers Who Never Placed an Order SELECT c.customer_id, c.customer_name FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_id IS NULL; 13. Find Inactive Customers (No Orders in the Last 90 Days) SELECT c.customer_id, c.customer_name FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.customer_name HAVING MAX(o.order_date) < CURRENT_DATE - INTERVAL '90 days' OR MAX(o.order_date) IS NULL; 14. Find the Best-Selling Product Category SELECT p.category, SUM(oi.quantity) AS units_sold FROM products p JOIN order_items oi ON p.product_id = oi.product_id GROUP BY p.category ORDER BY units_sold DESC LIMIT 1; 15. Find the Highest Revenue Product SELECT p.product_name, SUM(oi.quantity * oi.unit_price) AS revenue FROM products p JOIN order_items oi ON p.product_id = oi.product_id GROUP BY p.product_name ORDER BY revenue DESC LIMIT 1; 16. Find the Lowest Revenue Product SELECT p.product_name, SUM(oi.quantity * oi.unit_price) AS revenue FROM products p JOIN order_items oi ON p.product_id = oi.product_id GROUP BY p.product_name ORDER BY revenue LIMIT 1; 17. Calculate Average Products per Order SELECT ROUND(AVG(product_count), 2) AS avg_products_per_order FROM ( SELECT order_id, SUM(quantity) AS product_count FROM order_items GROUP BY order_id ) t; 18. Find Orders Worth More Than 10,000 SELECT order_id, SUM(quantity * unit_price) AS order_value FROM order_items GROUP BY order_id HAVING SUM(quantity * unit_price) > 10000; 19. Find Customers… Leggi tutto →

29. Find Monthly Revenue Growth WITH monthly_sales AS (     SELECT         DATE_TRUNC('month', o.order_date) AS month,         SUM(oi.quantity * oi.unit_price) AS revenue     FROM orders o     JOIN order_items oi ON o.order_id = oi.order_id     GROUP BY DATE_TRUNC('month', o.order_date) ) SELECT     month,     revenue,     LAG(revenue) OVER (ORDER BY month) AS previous_month_revenue,     ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month), 2) AS growth_percentage FROM monthly_sales; 30. Find the Highest Value Order for Each Customer WITH order_values AS (     SELECT         o.customer_id,         o.order_id,         SUM(oi.quantity * oi.unit_price) AS order_value     FROM orders o     JOIN order_items oi ON o.order_id = oi.order_id     GROUP BY o.customer_id, o.order_id ) SELECT * FROM (     SELECT *,            ROW_NUMBER() OVER (                PARTITION BY customer_id ORDER BY order_value DESC            ) AS rn     FROM order_values ) t WHERE rn = 1; Window Functions Covered  • Ranking: ROW_NUMBER(), RANK(), DENSE_RANK()  • Navigation: LAG(), LEAD()  • Aggregates: SUM() OVER()  • Analytics: Running Totals, Revenue Contribution, Month-over-Month Growth  💡 Double Tap ❤️ For More

🚀 SQL Project Series #5 E-Commerce Sales Analysis – Advanced Business Analytics In this part, we'll solve real-world business problems that Data Analysts encounter while working with customer, sales, and product data. 31. Calculate Customer Lifetime Value (CLV) SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS customer_lifetime_value FROM orders o JOIN order_items oi ON o.order_id = oi.order_id GROUP BY o.customer_id ORDER BY customer_lifetime_value DESC; 32. Calculate Repeat Purchase Rate WITH customer_orders AS ( SELECT customer_id, COUNT(*) AS total_orders FROM orders GROUP BY customer_id ) SELECT ROUND( 100.0 * COUNT(CASE WHEN total_orders > 1 THEN 1 END) / COUNT(*), 2 ) AS repeat_purchase_rate FROM customer_orders; 33. Find New vs Returning Customers WITH first_order AS ( SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id ) SELECT CASE WHEN o.order_date = f.first_order_date THEN 'New Customer' ELSE 'Returning Customer' END AS customer_type, COUNT(*) AS total_orders FROM orders o JOIN first_order f ON o.customer_id = f.customer_id GROUP BY customer_type; 34. Find Customer Retention by Month WITH monthly_orders AS ( SELECT DISTINCT customer_id, DATE_TRUNC('month', order_date) AS order_month FROM orders ) SELECT order_month, COUNT(DISTINCT customer_id) AS active_customers FROM monthly_orders GROUP BY order_month ORDER BY order_month; 35. Find Customers Who Purchased from Multiple Categories SELECT o.customer_id, COUNT(DISTINCT p.category) AS categories_purchased FROM orders o JOIN order_items oi ON o.order_id = oi.order_id JOIN products p ON oi.product_id = p.product_id GROUP BY o.customer_id HAVING COUNT(DISTINCT p.category) > 1; 36. Find the Most Frequently Purchased Product Pair SELECT oi1.product_id AS product₁, … Leggi tutto →

📊 Essential SQL Concepts Every Data Analyst Must Know 🚀 SQL is the most important skill for Data Analysts. Almost every analytics job requires working with databases to extract, filter, analyze, and summarize data. Understanding the following SQL concepts will help you write efficient queries and solve real business problems with data. 1️⃣ SELECT Statement (Data Retrieval) What it is: Retrieves data from a table. SELECT name, salary FROM employees; Use cases: Retrieving specific columns, viewing datasets, extracting required information. 2️⃣ WHERE Clause (Filtering Data) What it is: Filters rows based on specific conditions. SELECT * FROM orders WHERE order_amount > 500; Common conditions: =, >, <, >=, <=, BETWEEN, IN, LIKE 3️⃣ ORDER BY (Sorting Data) What it is: Sorts query results in ascending or descending order. SELECT name, salary FROM employees ORDER BY salary DESC; Sorting options: ASC (default), DESC 4️⃣ GROUP BY (Aggregation) What it is: Groups rows with same values into summary rows. SELECT department, COUNT(*) FROM employees GROUP BY department; Use cases: Sales per region, customers per country, orders per product category. 5️⃣ Aggregate Functions What they do: Perform calculations on multiple rows. SELECT AVG(salary) FROM employees; Common functions: COUNT(), SUM(), AVG(), MIN(), MAX() 6️⃣ HAVING Clause What it is: Filters grouped data after aggregation. SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5; Key difference: WHERE filters rows before grouping, HAVING filters groups after aggregation. 7️⃣ SQL JOINS (Combining Tables) What they do: Combine tables. -- INNER JOIN SELECT orders.order_id, customers.customer_name FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id; -- LEFT JOIN SELECT customers.customer_name, orders.order_id FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id; Common types: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JO… Leggi tutto →

🚀 SQL Project Series #6: Banking Transaction Analysis 🏦 Learn how banks use SQL to analyze customer transactions, monitor account activity, detect fraud, and generate business insights. 🎯 Business Objectives ✅ Analyze customer transactions ✅ Calculate account balances ✅ Identify high-value customers ✅ Detect suspicious transactions ✅ Monitor transaction trends ✅ Analyze deposits vs withdrawals ✅ Measure customer activity ✅ Identify dormant accounts ✅ Generate banking KPIs 📂 Step 1: Create Database CREATE DATABASE banking_db; USE banking_db; 📂 Step 2: Create Customers Table CREATE TABLE customers ( customer_id INT PRIMARY KEY, customer_name VARCHAR(100), gender VARCHAR(10), city VARCHAR(50), account_open_date DATE ); 📂 Step 3: Create Accounts Table CREATE TABLE accounts ( account_id INT PRIMARY KEY, customer_id INT, account_type VARCHAR(30), branch_name VARCHAR(100), opening_balance DECIMAL(12,2), FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); 📂 Step 4: Create Transactions Table CREATE TABLE transactions ( transaction_id INT PRIMARY KEY, account_id INT, transaction_date TIMESTAMP, transaction_type VARCHAR(20), amount DECIMAL(12,2), FOREIGN KEY (account_id) REFERENCES accounts(account_id) ); 📂 Step 5: Insert Sample Customers INSERT INTO customers VALUES (1,'Rahul Sharma','Male','Mumbai','2023-01-10'), (2,'Priya Verma','Female','Delhi','2023-02-18'), (3,'Amit Patel','Male','Pune','2023-04-12'), (4,'Sneha Joshi','Female','Bangalore','2023-05-22'), (5,'Rohan Gupta','Male','Hyderabad','2023-08-01'); 📂 Step 6: Insert Sample Accounts INSERT INTO accounts VALUES (101,1,'Savings','Mumbai',50000), (102,2,'Savings','Delhi',35000), (103,3,'Current','Pune',100000), (104,4,'Savings','Bangalore',45000), (105,5,'Current','Hyderabad',75000); 📂 Step 7: Insert Sample Transactions INSERT INTO transactions VALUES (1001,101,'2025-01… Leggi tutto →