tgindex

SQL Programming Resources

описание

Find top SQL resources from global universities, cool projects, and learning materials for data analytics. Admin: @coderfun Useful links: heylink.me/DataAnalytics Promotions: @love_data

← к каналу@sqlanalyst
76 647
подписчиков
Охват к подписчикам
1,7%
ERR
Реакции к просмотрам
0,21%
170 на 41 постов
Пересылки к просмотрам
0,43%
349
Постов в день
2,3
всего 42

Где отзываются чаще

доля реакций к просмотрам
  • 15 авг.SELECT d.department_name, COUNT(e.employee_id) AS employee_count FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id GROUP BY d.department_name ORDER BY employee_count DESC; 💡 Example 2: Average Salary by Department SELECT d.department_name, ROUND(AVG(e.salary), 2) AS average_salary FROM employees e JOIN departments d ON e.department_id = d.department_id GROUP BY d.department_name ORDER BY average_salary DESC; 💡 Example 3: Identify Highest-Paid Employees SELECT employee_name, job_title, salary FROM employees ORDER BY salary DESC LIMIT 10; 💡 Example 4: Calculate Attrition Rate SELECT ROUND(100.0 * SUM(CASE WHEN employment_status = 'Resigned' THEN 1 ELSE 0 END) / COUNT(*), 2) AS attrition_rate FROM employees; 💡 Example 5: Calculate Attendance Rate SELECT employee_id, ROUND(100.0 * SUM(CASE WHEN attendance_status = 'Present' THEN 1 ELSE 0 END) / COUNT(*), 2) AS attendance_rate FROM attendance GROUP BY employee_id; 💡 Example 6: Rank Employees by Salary SELECT employee_name, department_id, salary, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank FROM employees; 💡 Example 7: Find Employees Who Were Promoted SELECT e.employee_name, p.old_job_title, p.new_job_title, p.promotion_date FROM employees e JOIN promotions p ON e.employee_id = p.employee_id ORDER BY p.promotion_date; 🎯 Key Insights You Can Derive 🔹 Which departments have the highest headcount? 🔹 Which departments have the highest attrition? 🔹 Which roles have the highest salaries? 🔹 Which employees have been promoted? 🔹 What is the average employee tenure? 🔹 Which departments have attendance problems? 🔹 How quickly is the organization growing? 🔹 Where are the biggest employee retention challenges? 💼 Double Tap ❤️ For More0,50%
  • 7 авг.🚀 SQL Project Series #29 Customer Support & Helpdesk Analytics 🎧 Analyze customers, support tickets, agents, resolutions, and customer satisfaction using SQL to improve support operations, reduce resolution time, and enhance customer experience. 🎯 Business Objectives ✅ Analyze support ticket volume ✅ Monitor agent performance ✅ Measure response and resolution time ✅ Identify recurring customer issues ✅ Analyze customer satisfaction ✅ Track SLA compliance ✅ Improve first-contact resolution ✅ Build executive support dashboards 📂 Step 1: Create Database CREATE DATABASE helpdesk_db; USE helpdesk_db; 📂 Step 2: Create Customers Table CREATE TABLE customers ( customer_id INT PRIMARY KEY, customer_name VARCHAR(100), city VARCHAR(50), signup_date DATE ); 📂 Step 3: Create Support Agents Table CREATE TABLE support_agents ( agent_id INT PRIMARY KEY, agent_name VARCHAR(100), team_name VARCHAR(50), joining_date DATE ); 📂 Step 4: Create Tickets Table CREATE TABLE tickets ( ticket_id INT PRIMARY KEY, customer_id INT, agent_id INT, issue_category VARCHAR(50), priority VARCHAR(20), created_at DATETIME, resolved_at DATETIME, ticket_status VARCHAR(20), satisfaction_rating DECIMAL(2,1), FOREIGN KEY (customer_id) REFERENCES customers(customer_id), FOREIGN KEY (agent_id) REFERENCES support_agents(agent_id) ); 📂 Step 5: Insert Sample Customers INSERT INTO customers VALUES (1,'Rahul Sharma','Mumbai','2025-01-05'), (2,'Priya Verma','Delhi','2025-01-08'), (3,'Amit Patel','Pune','2025-01-10'), (4,'Sneha Joshi','Bangalore','2025-01-12'), (5,'Rohan Gupta','Hyderabad','2025-01-15'); 📂 Step 6: Insert Sample Support Agents INSERT INTO support_agents VALUES (101,'Ankit Mehta','Technical Support','2024-01-10'), (102,'Neha Singh','Billing Support','2024-03-15'), (103,'Vikas Sharma','Customer Success','2024-05-20'), (104,'Pooja Verma','Technical Support','2024-07-01'); 📂 Step 7: Insert Sample Tickets INSERT INTO tickets VALUES (1001,1,101,'Login Issue','High','2025-02-01 10:00:00','2025-02-01 11:30:00','Resolved',4.8), (1002,2,102,'Billing Query','Medium','2025-02-02 09:15:00','2025-02-02 10:00:00','Resolved',4.5), (1003,3,101,'Password Reset','Low','2025-02-02 14:30:00','2025-02-02 14:45:00','Resolved',5.0), (1004,4,103,'Feature Request','Low','2025-02-03 12:00:00',NULL,'Open',NULL), (1005,5,104,'Application Crash','Critical','2025-02-03 16:45:00',NULL,'In Progress',NULL); 🧠 SQL Concepts You'll Practice ✔ DDL & DML ✔ INNER JOIN ✔ LEFT JOIN ✔ Aggregate Functions ✔ GROUP BY ✔ HAVING ✔ CASE WHEN ✔ Date & Time Functions ✔ Common Table Expressions (CTEs) ✔ Window Functions ✔ Ranking Functions 📊 Business KPIs You Can Build 📈 Total Support Tickets 📈 Open Tickets 📈 Resolved Tickets 📈 Pending Tickets 📈 Average Resolution Time 📈 First Response Time 📈 First Contact Resolution Rate 📈 SLA Compliance Rate 📈 Customer Satisfaction Score (CSAT) 📈 Tickets by Priority 📈 Tickets by Category 📈 Tickets by City 📈 Agent Productivity 📈 Tickets Resolved per Agent 📈 Average Rating per Agent 📈 Daily Ticket Volume 📈 Monthly Ticket Trend 📈 Repeat Customer Issues 📈 Escalation Rate 📈 Executive Helpdesk Dashboard 🎯 This project reflects real-world SQL analysis performed by Customer Support Analysts, Operations Analysts, Service Delivery teams, Customer Success teams, and Business Intelligence professionals to improve service quality, optimize support operations, and enhance customer satisfaction. Double Tap ❤️ For More0,45%
  • 10 авг.🧠 SQL Concepts You'll Practice ✔ Joins ✔ GROUP BY ✔ HAVING ✔ CASE WHEN ✔ CTEs ✔ Subqueries ✔ Window Functions ✔ LAG() ✔ LEAD() ✔ Date Functions ✔ Conditional Aggregation 📊 Business KPIs You Can Build 📈 Monthly Recurring Revenue (MRR) 📈 Annual Recurring Revenue (ARR) 📈 Average Revenue Per User (ARPU) 📈 Customer Lifetime Value (CLV) 📈 Monthly Customer Churn Rate 📈 Revenue Churn Rate 📈 Customer Retention Rate 📈 New Customers, New MRR 📈 Expansion MRR, Contraction MRR, Churned MRR 📈 Net Revenue Retention (NRR), Gross Revenue Retention (GRR) 📈 Upgrade Rate, Downgrade Rate 📈 Plan-wise Revenue, Active Subscriptions, Cancelled Subscriptions 📈 Payment Success Rate, Failed Payment Rate 📈 Monthly Revenue Growth, Revenue Contribution by Plan 📈 Customer Segmentation 💡 Example 1: Calculate MRR SELECT SUM(p.monthly_price) AS mrr FROM subscriptions s JOIN plans p ON s.plan_id = p.plan_id WHERE s.subscription_status = 'Active'; 💡 Example 2: Revenue by Plan SELECT p.plan_name, COUNT(s.subscription_id) AS active_subscriptions, SUM(p.monthly_price) AS monthly_revenue FROM subscriptions s JOIN plans p ON s.plan_id = p.plan_id WHERE s.subscription_status = 'Active' GROUP BY p.plan_name ORDER BY monthly_revenue DESC; 💡 Example 3: Identify Churned Customers SELECT customer_id, subscription_id, end_date FROM subscriptions WHERE subscription_status = 'Cancelled'; 💡 Example 4: Calculate Upgrade vs Downgrade Count SELECT change_type, COUNT(*) AS total_changes FROM subscription_changes GROUP BY change_type; 💡 Example 5: Calculate Average Revenue Per Customer SELECT ROUND(SUM(p.monthly_price) / COUNT(DISTINCT s.customer_id), 2) AS arpu FROM subscriptions s JOIN plans p ON s.plan_id = p.plan_id WHERE s.subscription_status = 'Active'; 💡 Example 6: Rank Customers by MRR WITH customer_mrr AS ( SELECT s.customer_id, SUM(p.monthly_price) AS mrr FROM subscriptions s JOIN plans p ON s.plan_id = p.plan_id WHERE s.subscription_status = 'Active' GROUP BY s.customer_id ) SELECT customer_id, mrr, DENSE_RANK() OVER (ORDER BY mrr DESC) AS revenue_rank FROM customer_mrr; 🎯 Key Insights You Can Derive 🔹 Which subscription plan generates the most revenue? 🔹 Which plan has the highest churn? 🔹 How much MRR comes from new customers? 🔹 How much revenue is lost through cancellations? 🔹 Which customers have upgraded or downgraded? 🔹 Is revenue growing month over month? 🔹 Which customers contribute the most recurring revenue? 💼 This project is especially useful for Data Analysts, Product Analysts, Revenue Analysts, Growth Analysts, and Business Intelligence professionals working with SaaS and subscription-based businesses. Double Tap ❤️ For More0,43%
  • 15 авг.📈 Total Customers 📈 Active Customers 📈 Active Accounts 📈 Total Deposits 📈 Total Withdrawals 📈 Net Transaction Value 📈 Average Transaction Value 📈 Transaction Volume 📈 Customer Average Balance 📈 Total Deposits by Branch 📈 Branch-wise Transaction Volume 📈 Customer Segment Analysis 📈 Premium Customer Contribution 📈 High-Value Customers 📈 Monthly Transaction Growth 📈 Account Growth 📈 Loan Portfolio Value 📈 Average Loan Amount 📈 Loan Distribution by Type 📈 Active vs Closed Loans 📈 Customer Loan Exposure 📈 Deposit-to-Withdrawal Ratio 📈 Transaction Success Rate 📈 Dormant Account Analysis 📈 Unusual Transaction Detection 📈 Customer Profitability 📈 Executive Banking Dashboard 💡 Example 1: Total Deposits SELECT SUM(amount) AS total_deposits FROM transactions WHERE transaction_type = 'Deposit' AND transaction_status = 'Success'; 💡 Example 2: Customer Transaction Summary SELECT c.customer_id, c.customer_name, COUNT(t.transaction_id) AS total_transactions, SUM(t.amount) AS total_transaction_value FROM customers c JOIN accounts a ON c.customer_id = a.customer_id JOIN transactions t ON a.account_id = t.account_id WHERE t.transaction_status = 'Success' GROUP BY c.customer_id, c.customer_name ORDER BY total_transaction_value DESC; 💡 Example 3: Branch-wise Deposits SELECT b.branch_name, SUM(t.amount) AS total_deposits FROM branches b JOIN accounts a ON b.branch_id = a.branch_id JOIN transactions t ON a.account_id = t.account_id WHERE t.transaction_type = 'Deposit' AND t.transaction_status = 'Success' GROUP BY b.branch_name ORDER BY total_deposits DESC; 💡 Example 4: Identify High-Value Customers SELECT c.customer_id, c.customer_name, SUM(a.current_balance) AS total_balance FROM customers c JOIN accounts a ON c.customer_id = a.customer_id GROUP BY c.customer_id, c.customer_name HAVING SUM(a.current_balance) > 500000 ORDER BY total_balance DESC; 💡 Example 5: Monthly Transaction Trend SELECT DATE_TRUNC('month', transaction_date) AS month, COUNT(*) AS transaction_count, SUM(amount) AS transaction_value FROM transactions WHERE transaction_status = 'Success' GROUP BY DATE_TRUNC('month', transaction_date) ORDER BY month; 💡 Example 6: Rank Customers by Balance SELECT c.customer_name, SUM(a.current_balance) AS total_balance, DENSE_RANK() OVER (ORDER BY SUM(a.current_balance) DESC) AS balance_rank FROM customers c JOIN accounts a ON c.customer_id = a.customer_id GROUP BY c.customer_id, c.customer_name; 💡 Example 7: Identify Potentially Unusual Transactions SELECT account_id, transaction_id, transaction_date, amount FROM transactions WHERE amount > 100000 AND transaction_status = 'Success' ORDER BY amount DESC; 🎯 Key Insights You Can Derive 🔹 Which branches generate the highest transaction volume? 🔹 Which customer segments hold the most deposits? 🔹 Who are the highest-value customers? 🔹 Which account types have the highest activity? 🔹 How are deposits and withdrawals trending? 🔹 Which customers have significant loan exposure? 🔹 Which transactions may require additional investigation? 💼 Double Tap ❤️ For More0,43%
  • 13 авг.🚀 SQL Project Series #34 Food & Grocery Delivery Analytics 🛒 Analyze customers, stores, products, orders, deliveries, and payments to understand sales performance, customer behavior, delivery efficiency, and operational costs. 🎯 Business Objectives ✅ Analyze order and revenue trends ✅ Identify top-selling products ✅ Measure customer retention ✅ Analyze store performance ✅ Track delivery efficiency ✅ Identify peak ordering periods ✅ Monitor cancellations and refunds ✅ Optimize product and store performance 📂 Database Setup CREATE DATABASE grocery_delivery_db; USE grocery_delivery_db; Tables Created: customers → customer_id, customer_name, city, signup_date stores → store_id, store_name, city, store_type products → product_id, product_name, category, price orders → order_id, customer_id, store_id, order_date, order_status, delivery_time_minutes, delivery_fee order_items → order_item_id, order_id, product_id, quantity, unit_price Sample data for 5 customers, 4 stores, 5 products, 5 orders already included. 🧠 SQL Concepts You'll Practice ✔ INNER JOIN, LEFT JOIN ✔ Aggregate Functions, GROUP BY, HAVING ✔ CASE WHEN, CTEs, Subqueries ✔ Window Functions ✔ Date & Time Functions ✔ Conditional Aggregation 📊 Business KPIs You Can Build 📈 Total Orders, Completed Orders, Cancelled Orders, Cancellation Rate 📈 Total Revenue, AOV, Average Basket Size, Items Sold 📈 Revenue by Category, Store, City 📈 Top-Selling / Low-Selling Products 📈 Customer Lifetime Value, Repeat Purchase Rate, Retention Rate 📈 Average Delivery Time, On-Time Delivery Rate 📈 Peak Ordering Hour, Peak Ordering Day, Monthly Revenue Growth 📈 Delivery Fee Revenue, Customer Acquisition Trend 📈 Executive Grocery Delivery Dashboard 💡 Example Queries 1. Total Revenue SELECT SUM(oi.quantity * oi.unit_price) AS total_revenue FROM orders o JOIN order_items oi ON o.order_id = oi.order_id WHERE o.order_status = 'Delivered'; 2. Top-Selling Products SELECT p.product_name, SUM(oi.quantity) AS units_sold FROM products p JOIN order_items oi ON p.product_id = oi.product_id JOIN orders o ON oi.order_id = o.order_id WHERE o.order_status = 'Delivered' GROUP BY p.product_name ORDER BY units_sold DESC LIMIT 10; 3. Average Order Value WITH order_values AS ( SELECT 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 WHERE o.order_status = 'Delivered' GROUP BY o.order_id ) SELECT ROUND(AVG(order_value), 2) AS average_order_value FROM order_values; 4. Repeat Customers SELECT customer_id, COUNT(order_id) AS total_orders FROM orders WHERE order_status = 'Delivered' GROUP BY customer_id HAVING COUNT(order_id) > 1; 5. Revenue by Store SELECT s.store_name, SUM(oi.quantity * oi.unit_price) AS revenue FROM stores s JOIN orders o ON s.store_id = o.store_id JOIN order_items oi ON o.order_id = oi.order_id WHERE o.order_status = 'Delivered' GROUP BY s.store_name ORDER BY revenue DESC; 6. Cancellation Rate SELECT ROUND(100.0 * SUM(CASE WHEN order_status = 'Cancelled' THEN 1 ELSE 0 END) / COUNT(*), 2) AS cancellation_rate FROM orders; 7. Peak Ordering Hours SELECT EXTRACT(HOUR FROM order_date) AS order_hour, COUNT(*) AS total_orders FROM orders WHERE order_status = 'Delivered' GROUP BY EXTRACT(HOUR FROM order_date) ORDER BY total_orders DESC;0,42%
  • 5 окт. 2025 г.без подписи0,40%
  • 15 авг.🚀 SQL Project Series #36 Banking Customer & Transaction Analytics 🏦 Analyze customers, accounts, transactions, branches, and loan activity using SQL to understand customer behavior, transaction trends, account profitability, and banking operations. 🎯 Business Objectives ✅ Analyze customer activity ✅ Monitor account balances ✅ Track deposits and withdrawals ✅ Identify high-value customers ✅ Analyze branch performance ✅ Detect unusual transaction patterns ✅ Measure loan performance ✅ Build banking dashboards 📂 Step 1: Create Database CREATE DATABASE banking_analytics_db; USE banking_analytics_db; 📂 Step 2: Create Customers Table CREATE TABLE customers ( customer_id INT PRIMARY KEY, customer_name VARCHAR(100), city VARCHAR(50), customer_segment VARCHAR(30), signup_date DATE ); 📂 Step 3: Create Branches Table CREATE TABLE branches ( branch_id INT PRIMARY KEY, branch_name VARCHAR(100), city VARCHAR(50) ); 📂 Step 4: Create Accounts Table CREATE TABLE accounts ( account_id INT PRIMARY KEY, customer_id INT, branch_id INT, account_type VARCHAR(30), opening_date DATE, current_balance DECIMAL(15,2), account_status VARCHAR(20), FOREIGN KEY (customer_id) REFERENCES customers(customer_id), FOREIGN KEY (branch_id) REFERENCES branches(branch_id) ); 📂 Step 5: Create Transactions Table CREATE TABLE transactions ( transaction_id INT PRIMARY KEY, account_id INT, transaction_date DATETIME, transaction_type VARCHAR(30), amount DECIMAL(15,2), transaction_status VARCHAR(20), FOREIGN KEY (account_id) REFERENCES accounts(account_id) ); 📂 Step 6: Create Loans Table CREATE TABLE loans ( loan_id INT PRIMARY KEY, customer_id INT, loan_type VARCHAR(50), loan_amount DECIMAL(15,2), interest_rate DECIMAL(5,2), loan_status VARCHAR(20), FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); 📂 Step 7: Insert Sample Customers INSERT INTO customers VALUES (1,'Rahul Sharma','Mumbai','Premium','2022-01-10'), (2,'Priya Verma','Delhi','Mass Affluent','2022-04-15'), (3,'Amit Patel','Pune','Premium','2023-02-20'), (4,'Sneha Joshi','Bangalore','Mass Market','2023-06-05'), (5,'Rohan Gupta','Hyderabad','Premium','2024-01-18'); 📂 Step 8: Insert Sample Branches INSERT INTO branches VALUES (101,'Mumbai Central','Mumbai'), (102,'Connaught Place','Delhi'), (103,'Pune Central','Pune'), (104,'Bangalore Main','Bangalore'); 📂 Step 9: Insert Sample Accounts INSERT INTO accounts VALUES (1001,1,101,'Savings','2022-01-10',250000,'Active'), (1002,2,102,'Current','2022-04-15',520000,'Active'), (1003,3,103,'Savings','2023-02-20',175000,'Active'), (1004,4,104,'Savings','2023-06-05',85000,'Active'), (1005,5,101,'Current','2024-01-18',750000,'Active'); 📂 Step 10: Insert Sample Transactions INSERT INTO transactions VALUES (5001,1001,'2025-01-05 10:15:00','Deposit',50000,'Success'), (5002,1001,'2025-01-07 14:30:00','Withdrawal',15000,'Success'), (5003,1002,'2025-01-08 11:20:00','Deposit',120000,'Success'), (5004,1003,'2025-01-10 09:45:00','Withdrawal',25000,'Success'), (5005,1004,'2025-01-12 16:10:00','Deposit',30000,'Success'), (5006,1005,'2025-01-15 13:25:00','Withdrawal',85000,'Success'), (5007,1003,'2025-01-18 18:40:00','Transfer',45000,'Success'); 📂 Step 11: Insert Sample Loans INSERT INTO loans VALUES (9001,1,'Home Loan',5000000,8.25,'Active'), (9002,2,'Personal Loan',800000,11.50,'Active'), (9003,3,'Car Loan',1200000,9.10,'Active'), (9004,4,'Personal Loan',500000,12.00,'Closed'), (9005,5,'Business Loan',3000000,10.25,'Active'); 🧠 SQL Concepts You'll Practice ✔ INNER JOIN ✔ LEFT JOIN ✔ GROUP BY ✔ HAVING ✔ CASE WHEN ✔ CTEs ✔ Subqueries ✔ Window Functions ✔ RANK() ✔ DENSE_RANK() ✔ LAG() ✔ Date Functions ✔ Conditional Aggregation ✔ Financial KPI Calculations 📊 Business KPIs You Can Build0,39%
  • 22:16Essential SQL Topics for Data Analysts 👇 - Basic Queries: SELECT, FROM, WHERE clauses. - Sorting and Filtering: ORDER BY, GROUP BY, HAVING. - Joins: INNER JOIN, LEFT JOIN, RIGHT JOIN. - Aggregation Functions: COUNT, SUM, AVG, MIN, MAX. - Subqueries: Embedding queries within queries. - Data Modification: INSERT, UPDATE, DELETE. - Indexes: Optimizing query performance. - Normalization: Ensuring efficient database design. - Views: Creating virtual tables for simplified queries. - Understanding Database Relationships: One-to-One, One-to-Many, Many-to-Many. Window functions are also important for data analysts. They allow for advanced data analysis and manipulation within specified subsets of data. Commonly used window functions include: - ROW_NUMBER(): Assigns a unique number to each row based on a specified order. - RANK() and DENSE_RANK(): Rank data based on a specified order, handling ties differently. - LAG() and LEAD(): Access data from preceding or following rows within a partition. - SUM(), AVG(), MIN(), MAX(): Aggregations over a defined window of rows. Here is an amazing resources to learn & practice SQL: https://bit.ly/3FxxKPz Share with credits: https://t.me/sqlspecialist Hope it helps :)0,36%
  • 11 авг.WITH ranked_videos AS ( SELECT video_title, category, views, ROW_NUMBER() OVER ( PARTITION BY category ORDER BY views DESC ) AS rn FROM videos ) SELECT video_title, category, views FROM ranked_videos WHERE rn = 1; 💡 Example 4: Calculate Channel Performance SELECT c.channel_name, COUNT(v.video_id) AS total_videos, SUM(v.views) AS total_views, SUM(v.likes) AS total_likes, SUM(v.comments) AS total_comments FROM channels c LEFT JOIN videos v ON c.channel_id = v.channel_id GROUP BY c.channel_name ORDER BY total_views DESC; 💡 Example 5: Analyze Publishing Performance SELECT EXTRACT(DOW FROM publish_date) AS day_of_week, COUNT(*) AS total_videos, ROUND(AVG(views), 0) AS avg_views FROM videos GROUP BY EXTRACT(DOW FROM publish_date) ORDER BY avg_views DESC; 💡 Example 6: Rank Videos by Views SELECT video_title, views, DENSE_RANK() OVER ( ORDER BY views DESC ) AS view_rank FROM videos; 🎯 Key Insights You Can Derive 🔹 Which videos generate the most views? 🔹 Which content categories perform best? 🔹 Which videos have high engagement but relatively low views? 🔹 What publishing days generate the most views? 🔹 Which channels have the strongest audience engagement? 🔹 Which content contributes most to subscriber growth? 🔹 Which videos should be promoted further? 💼 This project is especially useful for Data Analysts, Product Analysts, Growth Analysts, Marketing Analysts, and Business Intelligence professionals working with content, media, and digital platforms. Double Tap ❤️ For More0,33%
  • 23 мар. 2025 г.Top 10 Advanced SQL Queries for Data Mastery 1. Recursive CTE (Common Table Expressions) Use a recursive CTE to traverse hierarchical data, such as employees and their managers. WITH RECURSIVE EmployeeHierarchy AS ( SELECT employee_id, employee_name, manager_id FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.employee_id, e.employee_name, e.manager_id FROM employees e JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id ) SELECT * FROM EmployeeHierarchy; 2. Pivoting Data Turn row data into columns (e.g., show product categories as separate columns). SELECT * FROM ( SELECT TO_CHAR(order_date, 'YYYY-MM') AS month, product_category, sales_amount FROM sales ) AS pivot_data PIVOT ( SUM(sales_amount) FOR product_category IN ('Electronics', 'Clothing', 'Books') ) AS pivoted_sales; 3. Window Functions Calculate a running total of sales based on order date. SELECT order_date, sales_amount, SUM(sales_amount) OVER (ORDER BY order_date) AS running_total FROM sales; 4. Ranking with Window Functions Rank employees’ salaries within each department. SELECT department, employee_name, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank FROM employees; 5. Finding Gaps in Sequences Identify missing values in a sequential dataset (e.g., order numbers). WITH Sequences AS ( SELECT MIN(order_number) AS start_seq, MAX(order_number) AS end_seq FROM orders ) SELECT start_seq + 1 AS missing_sequence FROM Sequences WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.order_number = Sequences.start_seq + 1 ); 6. Unpivoting Data Convert columns into rows to simplify analysis of multiple attributes. SELECT product_id, attribute_name, attribute_value FROM products UNPIVOT ( attribute_value FOR attribute_name IN (color, size, weight) ) AS unpivoted_data; 7. Finding Consecutive Events Check for consecutive days/orders for the same product using LAG(). WITH ConsecutiveOrders AS ( SELECT product_id, order_date, LAG(order_date) OVER (PARTITION BY product_id ORDER BY order_date) AS prev_order_date FROM orders ) SELECT product_id, order_date, prev_order_date FROM ConsecutiveOrders WHERE order_date - prev_order_date = 1; 8. Aggregation with the FILTER Clause Calculate selective averages (e.g., only for the Sales department). SELECT department, AVG(salary) FILTER (WHERE department = 'Sales') AS avg_salary_sales FROM employees GROUP BY department; 9. JSON Data Extraction Extract values from JSON columns directly in SQL. SELECT order_id, customer_id, order_details ->> 'product' AS product_name, CAST(order_details ->> 'quantity' AS INTEGER) AS quantity FROM orders; 10. Using Temporary Tables Create a temporary table for intermediate results, then join it with other tables. -- Create a temporary table CREATE TEMPORARY TABLE temp_product_sales AS SELECT product_id, SUM(sales_amount) AS total_sales FROM sales GROUP BY product_id; -- Use the temp table SELECT p.product_name, t.total_sales FROM products p JOIN temp_product_sales t ON p.product_id = t.product_id; Why These Matter Advanced SQL queries let you handle complex data manipulation and analysis tasks with ease. From traversing hierarchical relationships to reshaping data (pivot/unpivot) and working with JSON, these techniques expand your ability to derive insights from relational databases. Keep practicing these queries to solidify your SQL expertise and make more data-driven decisions! Here you can find essential SQL Interview Resources👇 https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v Like this post if you need more 👍❤️ Hope it helps :) #sql #dataanalyst0,33%
  • 15 авг.🚀 SQL Project Series #35 HR & Employee Analytics 👥 Analyze employees, departments, salaries, attendance, promotions, and attrition using SQL to understand workforce trends and improve HR decision-making. 🎯 Business Objectives ✅ Analyze employee headcount ✅ Track employee attrition ✅ Analyze salary distribution ✅ Measure department performance ✅ Identify high-performing employees ✅ Analyze promotions and tenure ✅ Track attendance ✅ Build HR analytics dashboards 📂 Step 1: Create Database CREATE DATABASE hr_analytics_db; USE hr_analytics_db; 📂 Step 2: Create Departments Table CREATE TABLE departments ( department_id INT PRIMARY KEY, department_name VARCHAR(100), location VARCHAR(50) ); 📂 Step 3: Create Employees Table CREATE TABLE employees ( employee_id INT PRIMARY KEY, employee_name VARCHAR(100), department_id INT, job_title VARCHAR(100), hire_date DATE, salary DECIMAL(12,2), employment_status VARCHAR(20), FOREIGN KEY (department_id) REFERENCES departments(department_id) ); 📂 Step 4: Create Attendance Table CREATE TABLE attendance ( attendance_id INT PRIMARY KEY, employee_id INT, attendance_date DATE, attendance_status VARCHAR(20), FOREIGN KEY (employee_id) REFERENCES employees(employee_id) ); 📂 Step 5: Create Promotions Table CREATE TABLE promotions ( promotion_id INT PRIMARY KEY, employee_id INT, promotion_date DATE, old_job_title VARCHAR(100), new_job_title VARCHAR(100), FOREIGN KEY (employee_id) REFERENCES employees(employee_id) ); 📂 Step 6: Insert Sample Departments INSERT INTO departments VALUES (101,'Data Analytics','Mumbai'), (102,'Finance','Delhi'), (103,'Human Resources','Bangalore'), (104,'Technology','Pune'), (105,'Operations','Hyderabad'); 📂 Step 7: Insert Sample Employees INSERT INTO employees VALUES (1,'Rahul Sharma',101,'Data Analyst','2022-01-10',850000,'Active'), (2,'Priya Verma',102,'Financial Analyst','2021-05-15',920000,'Active'), (3,'Amit Patel',104,'Software Engineer','2020-08-20',1200000,'Active'), (4,'Sneha Joshi',103,'HR Specialist','2023-02-10',650000,'Active'), (5,'Rohan Gupta',105,'Operations Analyst','2022-11-05',750000,'Resigned'); 📂 Step 8: Insert Sample Attendance INSERT INTO attendance VALUES (1,1,'2025-01-02','Present'), (2,1,'2025-01-03','Present'), (3,2,'2025-01-02','Present'), (4,2,'2025-01-03','Absent'), (5,3,'2025-01-02','Present'), (6,4,'2025-01-02','Present'), (7,5,'2025-01-02','Absent'); 📂 Step 9: Insert Sample Promotions INSERT INTO promotions VALUES (1,1,'2024-06-01','Junior Data Analyst','Data Analyst'), (2,3,'2024-01-15','Software Engineer','Senior Software Engineer'); 🧠 SQL Concepts You'll Practice ✔ INNER JOIN ✔ LEFT JOIN ✔ GROUP BY ✔ HAVING ✔ CASE WHEN ✔ Aggregate Functions ✔ CTEs ✔ Subqueries ✔ Window Functions ✔ RANK() ✔ DENSE_RANK() ✔ LAG() ✔ Date Functions ✔ Conditional Aggregation 📊 Business KPIs You Can Build 📈 Total Employees 📈 Active Employees 📈 Employee Attrition Rate 📈 Monthly Hiring Rate 📈 Monthly Attrition Rate 📈 Employee Retention Rate 📈 Average Salary 📈 Median Salary 📈 Salary by Department 📈 Salary by Job Title 📈 Gender Distribution 📈 Employee Tenure 📈 Average Tenure 📈 Promotion Rate 📈 Employees Promoted 📈 Attendance Rate 📈 Absenteeism Rate 📈 Department Headcount 📈 Department Attrition Rate 📈 Highest Paid Employees 📈 Salary Distribution 📈 Hiring Trend 📈 Employee Growth 📈 Executive HR Dashboard 💡 Example 1: Department-wise Employee Count0,28%
  • 13 авг.🎯 Key Insights You Can Derive 🔹 Which products generate the most revenue? 🔹 Which stores have the highest sales? 🔹 When do customers place the most orders? 🔹 Which cities have the strongest demand? 🔹 What percentage of orders are cancelled? 🔹 Which customers make repeat purchases? 🔹 Which categories contribute most to revenue? 🔹 How efficiently are orders being delivered? Double Tap ❤️ For More0,26%