Data Science & Machine Learning
Join this channel to learn data science, artificial intelligence and machine learning with funny quizzes, interesting projects and amazing resources for free For collaborations: @love_data
إظهار المزيد📈 نظرة تحليلية على قناة تيليجرام Data Science & Machine Learning
تُعد قناة Data Science & Machine Learning (@datasciencefun) في القطاع اللغوي الإنكليزية لاعباً نشطاً. يضم المجتمع حالياً 77 616 مشتركاً، محتلاً المرتبة 2 020 في فئة التعليم والمرتبة 3 811 في منطقة الهند.
📊 مؤشرات الجمهور والحراك
منذ تأسيسه في невідомо، حقق المشروع نمواً سريعاً وجمع 77 616 مشتركاً.
بحسب آخر البيانات بتاريخ 14 نوفمبر, 2026، تحافظ القناة على نشاط مستقر. خلال آخر 30 يوماً تغيّر عدد الأعضاء بمقدار -147، وفي آخر 24 ساعة بمقدار -20، مع بقاء الوصول العام مرتفعاً.
- حالة التحقق: غير موثّقة
- معدل التفاعل (ER): يبلغ متوسط تفاعل الجمهور 2.41%. وخلال أول 24 ساعة من النشر يحصد المحتوى عادةً 0.97% من ردود الفعل نسبةً إلى إجمالي المشتركين.
- وصول المنشورات: يحصل كل منشور على متوسط 1 872 مشاهدة. وخلال اليوم الأول يجمع عادةً 755 مشاهدة.
- التفاعلات والاستجابة: يتفاعل الجمهور بانتظام؛ متوسط التفاعلات لكل منشور يبلغ 2.
- الاهتمامات الموضوعية: يركز المحتوى على مواضيع رئيسية مثل learning, accuracy, distribution, panda, dataset.
📝 الوصف وسياسة المحتوى
يصف المؤلف القناة بأنها مساحة للتعبير عن الآراء الذاتية:
“Join this channel to learn data science, artificial intelligence and machine learning with funny quizzes, interesting projects and amazing resources for free
For collaborations: @love_data”
بفضل وتيرة التحديث المرتفعة (أحدث البيانات بتاريخ 15 نوفمبر, 2026) تحافظ القناة على حداثتها ومستوى وصول مرتفع. وتُظهر التحليلات تفاعلاً نشطاً من الجمهور، ما يجعلها نقطة تأثير مهمة ضمن فئة التعليم.
WITH employee_data AS (
SELECT **
FROM employees
)
SELECT **
FROM employee_data;
View:
CREATE VIEW employee_data AS
SELECT **
FROM employees;
The main difference is that a CTE is temporary within a statement, while a view has a persistent definition in the database.
🔹 9. Real-World Data Science Use Cases
CTEs are widely used in analytical SQL queries to:
• Calculate monthly revenue before comparing sales performance.
• Identify high-value customers.
• Find products with declining sales.
• Calculate department-wise employee statistics.
• Prepare clean datasets for reporting.
• Break complex business queries into smaller steps.
Example: Find customers whose total spending is greater than 50,000.
WITH customer_spending AS (
SELECT
customer_id,
SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id
)
SELECT
customer_id,
total_spending
FROM customer_spending
WHERE total_spending > 50000;
This is useful for customer segmentation and identifying high-value customers.
🔹 10. Common Mistakes to Avoid
• Forgetting the WITH keyword.
• Forgetting parentheses around the CTE query.
• Missing commas between multiple CTE definitions.
• Referencing a CTE outside the SQL statement where it is defined.
• Assuming a CTE permanently stores data.
• Giving CTEs unclear names.
• Assuming CTEs always improve query performance.
Important: A CTE improves query organization and readability, but it does not automatically make a query faster. Its execution behavior depends on the database system and query optimizer.
🔹 11. SQL Interview Questions
Q1. What is a CTE in SQL?
A CTE is a temporary named result set defined using the WITH keyword and used within a single SQL statement.
Q2. Which keyword is used to create a CTE?
The WITH keyword.
Q3. Can we create multiple CTEs in one query?
Yes. Multiple CTEs can be defined in a single WITH clause, separated by commas.
Q4. What is the difference between a CTE and a view?
A CTE exists only for one SQL statement, whereas a view is a stored database object that can be reused across queries.
Q5. Do CTEs improve query performance?
Not necessarily. CTEs mainly improve readability and organization. Performance depends on the database optimizer and query structure.
🎯 Practice Questions
Try writing SQL queries for these problems:
• Create a CTE to find employees earning more than 60,000.
• Calculate department-wise average salary using a CTE.
• Find customers whose total spending exceeds 50,000.
• Create two CTEs to calculate and filter product sales.
• Use a CTE with JOIN to display customer names and their total orders.
🎯 Key Takeaways
• CTE stands for Common Table Expression.
• CTEs are created using the WITH keyword.
• They make complex SQL queries easier to understand.
• You can create multiple CTEs in one query.
• CTEs can be used with JOIN, GROUP BY, and WHERE.
• A CTE exists only within its SQL statement.
• CTEs and views are different.
• CTEs improve readability but do not guarantee better performance.
Double Tap ❤️ For More
-----
0.997056 ₽ · /balance_helpSELECT **
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
Equivalent approach using a CTE:
WITH average_salary AS (
SELECT AVG(salary) AS avg_salary
FROM employees
)
SELECT **
FROM employees
WHERE salary > (
SELECT avg_salary
FROM average_salary
);
Both approaches solve the same problem.
🔹 8. CTE vs View
Another important interview concept.WITH department_salary AS (
SELECT
department,
AVG(salary) AS average_salary
FROM employees
GROUP BY department
)
SELECT **
FROM department_salary
WHERE average_salary > 70000;
🔹 5. Using Multiple CTEs
You can create multiple CTEs within a single SQL query. This is especially useful when solving complex business problems step by step.
Suppose you want to:
• Calculate total sales by product.
• Find products generating more than 1,00,000 in sales.
WITH product_sales AS (
SELECT
product_id,
SUM(amount) AS total_sales
FROM orders
GROUP BY product_id
),
high_sales_products AS (
SELECT **
FROM product_sales
WHERE total_sales > 100000
)
SELECT **
FROM high_sales_products;
• The first CTE calculates total sales.
• The second CTE filters high-performing products.
• The final query retrieves the result.
Important: Separate multiple CTE definitions with commas.
🔹 6. CTE with JOIN
You can combine CTEs with JOIN operations.
Example: Find total sales generated by each customer.
Suppose you have:
• customers
• orders
WITH customer_sales AS (
SELECT
customer_id,
SUM(amount) AS total_sales
FROM orders
GROUP BY customer_id
)
SELECT
c.customer_name,
cs.total_sales
FROM customers c
INNER JOIN customer_sales cs
ON c.customer_id = cs.customer_id;
The CTE calculates total sales for each customer. The main query joins that result with the customers table to retrieve customer names.
🔹 7. CTE vs Subquery
This is a common SQL interview question.SELECT employee_name, salary
FROM employees
WHERE salary > 60000;
Using a CTE:
WITH high_salary_employees AS (
SELECT employee_name, salary
FROM employees
WHERE salary > 60000
)
SELECT **
FROM high_salary_employees;
Here:
• The CTE is created using WITH.
• The inner query selects employees earning more than 60,000.
• The outer query retrieves the result from the CTE.
🔹 2. Basic Syntax of a CTE
WITH cte_name AS (
SELECT column1, column2
FROM table_name
WHERE condition
)
SELECT **
FROM cte_name;
Important parts:
• WITH starts the CTE.
• cte_name is the name you give to the temporary result.
• AS introduces the query.
• The query inside parentheses defines the CTE.
• The main query uses the CTE.
🔹 3. CTE with WHERE Condition
You can use CTEs to filter records before applying further conditions.
Example: Find employees earning more than 50,000 and display only those earning more than 70,000.
WITH employee_data AS (
SELECT employee_name, salary
FROM employees
WHERE salary > 50000
)
SELECT **
FROM employee_data
WHERE salary > 70000;
The CTE first selects employees earning above 50,000. The outer query further filters the result.
🔹 4. CTE with GROUP BY
CTEs are very useful when working with aggregate functions.
Example: Calculate the average salary for each department.
WITH department_salary AS (
SELECT
department,
AVG(salary) AS average_salary
FROM employees
GROUP BY department
)
SELECT **
FROM department_salary;
Result: