SQL Programming Resources
Відкрити в Telegram
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
Показати більше
Сітка:📈 Аналітичний огляд Telegram-каналу SQL Programming Resources
Канал SQL Programming Resources (@sqlanalyst) у мовному сегменті Англійська є активним учасником. На даний момент спільнота об'єднує 76 661 підписників, посідаючи 1 636 місце в категорії Технології та додатки та 3 966 місце у регіоні Індія.
📊 Показники аудиторії та динаміка
З моменту свого створення невідомо, проект продемонстрував стрімке зростання, зібравши аудиторію у 76 661 підписників.
За останніми даними від 15 вересня, 2026, канал демонструє стабільну активність. Хоча за останні 30 днів спостерігається зміна кількості учасників на 17, а за останні 24 години на -6, загальне охоплення залишається високим.
- Статус верифікації: Не верифікований
- Рівень залученості (ER): Середній показник залученості аудиторії становить 1.50%. Протягом перших 24 годин після публікації контент зазвичай збирає 0.81% реакцій від загальної кількості підписників.
- Охоплення публікацій: В середньому кожен допис отримує 1 148 переглядів. Протягом першої доби публікація в середньому набирає 621 переглядів.
- Реакції та взаємодія: Аудиторія активно підтримує контент: середня кількість реакцій на один пост – 3.
- Тематичні інтереси: Контент зосереджений навколо ключових тем, таких як row, sql, customer_id, logic, desc.
📝 Опис та контентна політика
Автор описує ресурс як майданчик для висловлення суб'єктивної думки:
“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”
Завдяки високій частоті оновлень (останні дані отримано 16 вересня, 2026), канал підтримує актуальність та високий рівень охоплення публікацій. Аналітика показує, що аудиторія активно взаємодіє з контентом, що робить його важливою точкою впливу в категорії Технології та додатки.
76 661
Підписники
-624 години
-67 днів
+1730 днів
Триває завантаження даних...
Схожі канали
Хмара тегів
Вхідні та вихідні згадування
---
---
---
---
---
---
Залучення підписників
вересень '26
вересень '26
+136
в 4 каналах
серпень '26
+418
в 3 каналах
Get PRO
липень '26
+572
в 8 каналах
Get PRO
червень '26
+550
в 8 каналах
Get PRO
травень '26
+597
в 9 каналах
Get PRO
квітень '26
+494
в 4 каналах
Get PRO
березень '26
+166
в 6 каналах
Get PRO
лютий '26
+741
в 14 каналах
Get PRO
січень '26
+716
в 5 каналах
Get PRO
грудень '25
+863
в 4 каналах
Get PRO
листопад '25
+771
в 7 каналах
Get PRO
жовтень '25
+676
в 4 каналах
Get PRO
вересень '25
+468
в 12 каналах
Get PRO
серпень '25
+502
в 25 каналах
Get PRO
липень '25
+625
в 21 каналах
Get PRO
червень '25
+1 033
в 37 каналах
Get PRO
травень '25
+2 110
в 26 каналах
Get PRO
квітень '25
+3 784
в 40 каналах
Get PRO
березень '25
+1 190
в 27 каналах
Get PRO
лютий '25
+881
в 30 каналах
Get PRO
січень '25
+1 239
в 36 каналах
Get PRO
грудень '24
+1 240
в 22 каналах
Get PRO
листопад '24
+3 503
в 23 каналах
Get PRO
жовтень '24
+3 975
в 29 каналах
Get PRO
вересень '24
+4 469
в 26 каналах
Get PRO
серпень '24
+5 990
в 23 каналах
Get PRO
липень '24
+8 180
в 25 каналах
Get PRO
червень '24
+7 119
в 22 каналах
Get PRO
травень '24
+5 879
в 12 каналах
Get PRO
квітень '24
+5 286
в 13 каналах
Get PRO
березень '24
+6 156
в 17 каналах
Get PRO
лютий '24
+4 581
в 8 каналах
Get PRO
січень '24
+5 285
в 4 каналах
Get PRO
грудень '23
+7 504
в 8 каналах
| Дата | Залучення підписників | Згадування | Канали | |
| 16 вересня | +19 | |||
| 15 вересня | +4 | |||
| 14 вересня | +9 | |||
| 13 вересня | +18 | |||
| 12 вересня | +10 | |||
| 11 вересня | +8 | |||
| 10 вересня | 0 | |||
| 09 вересня | +4 | |||
| 08 вересня | +16 | |||
| 07 вересня | +1 | |||
| 06 вересня | +1 | |||
| 05 вересня | +1 | |||
| 04 вересня | +10 | |||
| 03 вересня | +19 | |||
| 02 вересня | +5 | |||
| 01 вересня | +11 |
Дописи каналу
Practice 4 — Rank customers by total spending.
WITH customer_sales AS (
SELECT customer_id, SUM(amount) AS total_spending
FROM orders GROUP BY customer_id
)
SELECT customer_id, total_spending,
RANK() OVER ( ORDER BY total_spending DESC ) AS spending_rank
FROM customer_sales;WITH customer_sales AS (
SELECT customer_id, SUM(amount) AS total_spending
FROM orders GROUP BY customer_id
)
SELECT customer_id, total_spending,
CASE WHEN total_spending >= 100000 THEN 'VIP'
WHEN total_spending >= 50000 THEN 'Premium'
ELSE 'Standard' END AS segment
FROM customer_sales;orders(order_id, customer_id, amount, order_date)
Find the top 5 customers by total spending, but only consider customers who have placed at least 3 orders.
Solution:
WITH customer_metrics AS (
SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_spending
FROM orders GROUP BY customer_id
),
qualified_customers AS (
SELECT customer_id, order_count, total_spending
FROM customer_metrics WHERE order_count >= 3
)
SELECT customer_id, order_count, total_spending
FROM qualified_customers ORDER BY total_spending DESC LIMIT 5;| 2 | Recursive CTEs are an advanced topic, but understanding their purpose is important.
🧠 17. Why CTEs Are Valuable for Data Analysts
Imagine a business request: «Identify customers who increased their spending, classify them by segment, compare them against the average, and calculate their rank.»
Trying to write everything as one giant query can become difficult.
CTEs allow you to think in stages:
• Raw Orders ↓
• Customer Metrics ↓
• Customer Segmentation ↓
• Average Comparison ↓
• Ranking ↓
• Final Report
Each step has a clear purpose.
⚠️ 18. Common CTE Mistakes
Mistake 1 — Forgetting the CTE name
Incorrect: WITH ( SELECT ... )
Correct: WITH customer_sales AS ( SELECT ... )
Mistake 2 — Forgetting the comma between CTEs
Incorrect: Two CTEs without comma
Correct: Separate CTEs with a comma
Mistake 3 — Using a CTE without understanding its grain
A CTE might produce: 1 row = 1 order while you think it produces: 1 row = 1 customer
Always validate the grain.
Mistake 4 — Creating too many unnecessary CTEs
CTEs should make logic clearer. If every two-line transformation becomes its own CTE, the query can become harder to follow. Use them when they improve structure.
🔎 19. Debugging with CTEs
One major advantage is easier debugging.
Suppose your final query produces incorrect revenue. Instead of debugging one huge query, test each stage.
First:
WITH customer_sales AS (
...
)
SELECT *
FROM customer_sales;
• Check the results.
• Then add the next CTE.
• This allows you to identify exactly where the numbers become incorrect.
🎤 SQL Interview Questions
•
Q1. What is a CTE?
A Common Table Expression is a named temporary result set defined using the "WITH" clause and available to the query that follows it.
•
Q2. What is the syntax of a CTE?
WITH cte_name AS ( SELECT ... ) SELECT ... FROM cte_name;
•
Q3. Can you create multiple CTEs?
Yes. WITH cte1 AS ( ... ), cte2 AS ( ... ) SELECT ... FROM cte2;
•
Q4. Can one CTE reference another CTE?
Yes. A later CTE can generally reference an earlier CTE in the same "WITH" clause.
•
Q5. What is the difference between a CTE and a subquery?
Both can represent intermediate query results, but CTEs often make multi-step logic easier to read and reuse within the same statement.
•
Q6. Does a CTE permanently store data?
No. A standard CTE is associated with the SQL statement in which it is defined.
•
Q7. Does using a CTE always improve performance?
No. CTEs primarily improve query organization and readability. Performance depends on the database engine and execution plan.
•
Q8. What is a recursive CTE?
A CTE that references itself, typically used for hierarchical or recursive data.
•
Q9. Why are CTEs useful in analytics?
They allow complex analytical logic to be divided into clear, manageable stages.
•
Q10. What should you check when using multiple CTEs?
Check the grain, row count, joins, aggregations, and filters at each stage.
📝 Practice Questions
Practice 1 — Calculate total spending per customer using a CTE.
WITH customer_sales AS (
SELECT customer_id, SUM(amount) AS total_spending
FROM orders GROUP BY customer_id
)
SELECT * FROM customer_sales;
Practice 2 — Find customers spending more than ₹50,000.
WITH customer_sales AS (
SELECT customer_id, SUM(amount) AS total_spending
FROM orders GROUP BY customer_id
)
SELECT * FROM customer_sales WHERE total_spending > 50000;
Practice 3 — Calculate order count and revenue per customer.
WITH customer_metrics AS (
SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_revenue
FROM orders GROUP BY customer_id
)
SELECT * FROM customer_metrics; | 281 |
| 3 | WITH customer_revenue AS (
SELECT
customer_id,
SUM(amount) AS revenue
FROM orders
GROUP BY customer_id
)
SELECT
SUM(revenue) AS total_revenue,
COUNT(*) AS active_customers,
SUM(revenue) / NULLIF(COUNT(*), 0) AS revenue_per_customer
FROM customer_revenue;
The CTE first creates: 1 row = 1 customer
Then the final query calculates KPIs from that customer-level dataset.
🧱 14. CTE for Multi-Step Analytics
Let's build a slightly more realistic analysis.
• Step 1 — Calculate customer sales
WITH customer_sales AS (
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id
)
• Step 2 — Create customer segments
, segmented_customers AS (
SELECT
customer_id,
order_count,
total_spending,
CASE
WHEN total_spending >= 100000 THEN 'VIP'
WHEN total_spending >= 50000 THEN 'Premium'
ELSE 'Standard'
END AS segment
FROM customer_sales
)
• Step 3 — Analyze segments
SELECT
segment,
COUNT(*) AS customers,
SUM(total_spending) AS revenue
FROM segmented_customers
GROUP BY segment
ORDER BY revenue DESC;
The complete query:
WITH customer_sales AS (
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id
),
segmented_customers AS (
SELECT
customer_id,
order_count,
total_spending,
CASE
WHEN total_spending >= 100000 THEN 'VIP'
WHEN total_spending >= 50000 THEN 'Premium'
ELSE 'Standard'
END AS segment
FROM customer_sales
)
SELECT
segment,
COUNT(*) AS customers,
SUM(total_spending) AS revenue
FROM segmented_customers
GROUP BY segment
ORDER BY revenue DESC;
This is a good example of structured analytical SQL.
🪟 15. CTE + Window Functions Preview
CTEs become especially powerful when combined with window functions.
For example:
WITH customer_sales AS (
SELECT
customer_id,
SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id
)
SELECT
customer_id,
total_spending,
RANK() OVER (
ORDER BY total_spending DESC
) AS spending_rank
FROM customer_sales;
• The CTE creates the customer-level metric.
• The window function ranks the customers.
• This pattern is extremely common in analytics.
🔄 16. Recursive CTEs
There is another advanced type of CTE: Recursive CTE
It allows a query to repeatedly reference itself.
Common use cases include:
• organizational hierarchies
• employee-manager structures
• category trees
• folder structures
• graph-like relationships
• generating sequences
Example structure:
WITH RECURSIVE employee_tree 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 employee_tree t
ON e.manager_id = t.employee_id
)
SELECT *
FROM employee_tree; | 113 |
| 4 | The CTE changes the grain to: 1 row = 1 customer
That makes the final filtering straightforward.
📈 6. CTE + JOIN
CTEs become even more useful when combined with JOINs.
WITH customer_sales AS (
SELECT
customer_id,
SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id
)
SELECT
c.customer_name,
cs.total_spending
FROM customers c
JOIN customer_sales cs
ON c.customer_id = cs.customer_id;
• The CTE handles the aggregation.
• The main query handles the customer information.
💰 7. CTE + COALESCE
Want to include customers who haven't placed orders?
WITH customer_sales AS (
SELECT
customer_id,
SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id
)
SELECT
c.customer_id,
c.customer_name,
COALESCE(cs.total_spending, 0) AS total_spending
FROM customers c
LEFT JOIN customer_sales cs
ON c.customer_id = cs.customer_id;
Now customers without orders appear with: total_spending = 0
🧮 8. CTE + CASE
We can also create business segments.
WITH customer_sales AS (
SELECT
customer_id,
SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id
)
SELECT
customer_id,
total_spending,
CASE
WHEN total_spending >= 100000 THEN 'VIP'
WHEN total_spending >= 50000 THEN 'Premium'
ELSE 'Standard'
END AS customer_segment
FROM customer_sales;
• The CTE creates the metric.
• "CASE" converts the metric into business categories.
🔍 9. CTE vs Subquery
Both can solve similar problems.
• Subquery
SELECT *
FROM (
SELECT
customer_id,
SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id
) AS customer_sales
WHERE total_spending > 50000;
• CTE
WITH customer_sales AS (
SELECT
customer_id,
SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id
)
SELECT *
FROM customer_sales
WHERE total_spending > 50000;
The CTE often makes multi-step logic easier to read.
🧠 10. CTE vs Temporary Table
A CTE is not the same as a permanent table.
• CTE
WITH sales AS (...)
SELECT ...
Generally exists only for the duration of that SQL statement.
• Temporary table
CREATE TEMP TABLE sales AS
SELECT ...;
A temporary table can generally be referenced by multiple statements during its session, depending on the database.
Simple distinction:
• CTE → temporary named query result
• Temporary table → temporary database object
⚡ 11. CTE Does Not Automatically Mean Faster
A common misconception is:
CTEs make queries faster. Not necessarily.
CTEs primarily improve:
• readability
• organization
• maintainability
• debugging
• step-by-step logic
Performance depends on the database engine and how it optimizes the query. Some databases may inline a CTE, while others may materialize it in certain situations.
So: Use CTEs for clear logic, not simply because you expect better performance.
🧪 12. CTE for Data Quality Analysis
Suppose we want to identify customers with missing contact information.
First create a cleaned customer dataset:
WITH cleaned_customers AS (
SELECT
customer_id,
TRIM(customer_name) AS customer_name,
LOWER(TRIM(email)) AS email
FROM customers
)
SELECT *
FROM cleaned_customers
WHERE email IS NULL;
This creates a clean intermediate layer before analysis.
📊 13. CTE for KPI Calculation
Suppose we want: «Revenue per customer.» | 104 |
| 5 | 🚀 SQL Roadmap 2026 — Part 13
🧱 SQL CTEs — Writing Complex Queries in Simple Steps
As SQL queries become more advanced, they can become difficult to read.
You may have:
• JOINs
• Subqueries
• GROUP BY
• CASE
• Aggregations
• Multiple calculations
all inside one query.
This is where CTEs become extremely useful.
CTE stands for: «Common Table Expression»
A CTE lets you create a temporary named result set and then use it in your main query.
Think of it as:
• Step 1 → Prepare the data
• Step 2 → Transform the data
• Step 3 → Analyze the data
• Step 4 → Return the result
🧠 1. Basic CTE Syntax
A CTE starts with:
WITH cte_name AS (
SELECT
...
FROM ...
)
SELECT *
FROM cte_name;
Example:
WITH customer_sales AS (
SELECT
customer_id,
SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id
)
SELECT *
FROM customer_sales;
The CTE customer_sales acts like a temporary result set for the duration of the query.
📊 2. Why Use CTEs?
Without a CTE, a complex query can become difficult to understand.
For example:
SELECT
customer_id,
total_spending
FROM (
SELECT
customer_id,
SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id
) AS customer_totals
WHERE total_spending > 50000;
With a CTE:
WITH customer_totals AS (
SELECT
customer_id,
SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id
)
SELECT
customer_id,
total_spending
FROM customer_totals
WHERE total_spending > 50000;
The second version is often much easier to read.
🧩 3. CTEs Break Complex Problems into Steps
Suppose the business question is:
«Find customers whose total spending is above the average customer spending.»
Instead of writing everything as one large nested query, break it into logical steps.
• Step 1 — Calculate spending per customer
WITH customer_totals AS (
SELECT
customer_id,
SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id
)
• Step 2 — Calculate average spending
WITH customer_totals AS (
SELECT
customer_id,
SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id
),
average_spending AS (
SELECT
AVG(total_spending) AS avg_spending
FROM customer_totals
)
• Step 3 — Compare customers with the average
WITH customer_totals AS (
SELECT
customer_id,
SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id
),
average_spending AS (
SELECT
AVG(total_spending) AS avg_spending
FROM customer_totals
)
SELECT
c.customer_id,
c.total_spending
FROM customer_totals c
CROSS JOIN average_spending a
WHERE c.total_spending > a.avg_spending;
Now the logic is much easier to follow.
🔗 4. Multiple CTEs
A single query can contain multiple CTEs.
Structure:
WITH first_cte AS (
...
),
second_cte AS (
...
),
third_cte AS (
...
)
SELECT ...
FROM third_cte;
Later CTEs can reference earlier CTEs.
🏢 5. Real-World Example
Suppose an e-commerce company wants:
«Customers with spending above ₹50,000 and at least 5 orders.»
First calculate customer-level metrics:
WITH customer_metrics AS (
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id
)
SELECT
customer_id,
order_count,
total_spending
FROM customer_metrics
WHERE order_count >= 5
AND total_spending > 50000; | 185 |
| 6 | Here are some essential data science concepts from A to Z:
A - Algorithm: A set of rules or instructions used to solve a problem or perform a task in data science.
B - Big Data: Large and complex datasets that cannot be easily processed using traditional data processing applications.
C - Clustering: A technique used to group similar data points together based on certain characteristics.
D - Data Cleaning: The process of identifying and correcting errors or inconsistencies in a dataset.
E - Exploratory Data Analysis (EDA): The process of analyzing and visualizing data to understand its underlying patterns and relationships.
F - Feature Engineering: The process of creating new features or variables from existing data to improve model performance.
G - Gradient Descent: An optimization algorithm used to minimize the error of a model by adjusting its parameters.
H - Hypothesis Testing: A statistical technique used to test the validity of a hypothesis or claim based on sample data.
I - Imputation: The process of filling in missing values in a dataset using statistical methods.
J - Joint Probability: The probability of two or more events occurring together.
K - K-Means Clustering: A popular clustering algorithm that partitions data into K clusters based on similarity.
L - Linear Regression: A statistical method used to model the relationship between a dependent variable and one or more independent variables.
M - Machine Learning: A subset of artificial intelligence that uses algorithms to learn patterns and make predictions from data.
N - Normal Distribution: A symmetrical bell-shaped distribution that is commonly used in statistical analysis.
O - Outlier Detection: The process of identifying and removing data points that are significantly different from the rest of the dataset.
P - Precision and Recall: Evaluation metrics used to assess the performance of classification models.
Q - Quantitative Analysis: The process of analyzing numerical data to draw conclusions and make decisions.
R - Random Forest: An ensemble learning algorithm that builds multiple decision trees to improve prediction accuracy.
S - Support Vector Machine (SVM): A supervised learning algorithm used for classification and regression tasks.
T - Time Series Analysis: A statistical technique used to analyze and forecast time-dependent data.
U - Unsupervised Learning: A type of machine learning where the model learns patterns and relationships in data without labeled outputs.
V - Validation Set: A subset of data used to evaluate the performance of a model during training.
W - Web Scraping: The process of extracting data from websites for analysis and visualization.
X - XGBoost: An optimized gradient boosting algorithm that is widely used in machine learning competitions.
Y - Yield Curve Analysis: The study of the relationship between interest rates and the maturity of fixed-income securities.
Z - Z-Score: A standardized score that represents the number of standard deviations a data point is from the mean.
Credits: https://t.me/free4unow_backup
Like if you need similar content 😄👍 | 384 |
| 7 |  🎓 𝗧𝗼𝗽 𝗜𝗻-𝗗𝗲𝗺𝗮𝗻𝗱 𝗙𝗥𝗘𝗘 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻𝘀 𝘁𝗼 𝗠𝗮𝘀𝘁𝗲𝗿 𝗶𝗻 𝟮𝟬𝟮𝟲 🔥
Explore these FREE certification courses in today’s most in-demand technology fields:
📊 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 :- https://pdlink.in/4eRA6eF
💻 𝗪𝗲𝗯 𝗗𝗲𝘃𝗲𝗹𝗼𝗽𝗺𝗲𝗻𝘁 :- https://pdlink.in/4gP18Eo
💫 𝗔𝗿𝘁𝗶𝗳𝗶𝗰𝗶𝗮𝗹 𝗜𝗻𝘁𝗲𝗹𝗹𝗶𝗴𝗲𝗻𝗰𝗲 :- https://pdlink.in/45HWa5Q
☁️ 𝗖𝗹𝗼𝘂𝗱 𝗖𝗼𝗺𝗽𝘂𝘁𝗶𝗻𝗴 :- https://pdlink.in/4zrksPn
🟧 𝗔𝗪𝗦 :- https://pdlink.in/4j4Jxtv
🛡️ 𝗖𝘆𝗯𝗲𝗿𝘀𝗲𝗰𝘂𝗿𝗶𝘁𝘆 & 𝗔𝘇𝘂𝗿𝗲 :- https://pdlink.in/4f0GNuH
⚡ Start learning today and prepare yourself for better career opportunities in 2026! | 486 |
| 8 | ⚠️ 23. Common Subquery Mistakes
Mistake 1 — Returning multiple rows with =
Use IN instead of = when multiple rows are expected.
Mistake 2 — Forgetting NULL behavior with NOT IN
Mistake 3 — Ignoring duplicates
Mistake 4 — Making the query unnecessarily complicated
🎤 SQL Interview Questions
Q1. What is a subquery?
A query nested inside another query.
Q2. What is a scalar subquery?
A subquery that returns a single value.
Q3. When should you use IN?
When the subquery returns a set of values.
Q4. What does EXISTS do?
It checks whether at least one matching row exists.
Q5. What is a correlated subquery?
A subquery that references a column from the outer query.
Q6. What is a derived table?
A subquery in the FROM clause treated as a temporary result set.
Q7. Can a subquery be used in SELECT?
Yes.
Q8. What is the difference between IN and EXISTS?
IN compares against a set, while EXISTS checks for existence.
Q9. Why is NOT IN dangerous with NULL?
Three-valued logic can cause unexpected results.
Q10. Can every subquery be replaced with a JOIN?
Many can, but the best approach depends on the logic.
📝 Practice Questions
Practice 1 — Employees earning more than average:
SELECT employee_name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
Practice 2 — Customers with at least one order:
SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
);
Practice 3 — Customers with more than 5 orders:
SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5
);
Practice 4 — Products higher than average price:
SELECT product_name, price
FROM products
WHERE price > (
SELECT AVG(price)
FROM products
);
Practice 5 — Customers who never placed an order:
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);
🧪 Mini SQL Challenge
Find customers whose total spending is greater than the average customer spending.
SELECT customer_id, total_spending
FROM (
SELECT
customer_id,
SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id
) AS customer_totals
WHERE total_spending > (
SELECT AVG(total_spending)
FROM (
SELECT
customer_id,
SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id
) AS totals
);
💡 Double Tap ❤️ For More
-----
0.118646 ₽ · /balance_help | 901 |
| 9 | Means: Return customers for whom no matching order exists.
🧠 12. EXISTS vs IN
IN → Compare against a set of returned values
EXISTS → Check whether a matching row exists
🔄 13. Correlated Subquery
Depends on the current row of the outer query.
SELECT
e.employee_name,
e.salary,
e.department_id
FROM employees e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department_id = e.department_id
);
«Is this employee's salary higher than the average salary of their own department?»
🏢 14. Above-Department-Average Salary
This is a correlated subquery. Bob is compared against the Sales average, while David is compared against the IT average.
📦 15. Subquery in FROM
It can appear in the FROM clause.
SELECT
customer_id,
total_spending
FROM (
SELECT
customer_id,
SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id
) AS customer_sales;
This is often called a derived table.
📈 16. Finding High-Value Customers
SELECT
customer_id,
total_spending
FROM (
SELECT
customer_id,
SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id
) AS customer_sales
WHERE total_spending > 50000;
🧩 17. Subquery in SELECT
SELECT
c.customer_name,
(
SELECT COUNT(*)
FROM orders o
WHERE o.customer_id = c.customer_id
) AS order_count
FROM customers c;
⚠️ 18. But Be Careful with Correlated Subqueries
Instead of a correlated subquery in SELECT, you could use:
SELECT
c.customer_name,
COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY
c.customer_id,
c.customer_name;
The better choice depends on the database optimizer, table size, indexes, and other factors.
🧱 19. Nested Subqueries
SELECT *
FROM products
WHERE price > (
SELECT AVG(price)
FROM products
WHERE category_id IN (
SELECT category_id
FROM categories
WHERE category_name = 'Electronics'
)
);
Deeply nested queries can become difficult to read. Use CTEs instead.
🧠 20. Subqueries vs JOINs
Subquery:
SELECT *
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
);
JOIN:
SELECT DISTINCT
c.*
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id;
Choose based on readability, business logic, and performance.
🏆 21. Subqueries for Business Analysis
Find products above average:
SELECT product_name, price
FROM products
WHERE price > (
SELECT AVG(price)
FROM products
);
Find customers with at least one order:
SELECT customer_id, customer_name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);
Find customers with more than 10 orders:
SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 10
);
🧮 22. Subquery for KPI Comparison
SELECT order_id, customer_id, amount
FROM orders
WHERE amount > (
SELECT AVG(amount)
FROM orders
); | 627 |
| 10 | 🚀 SQL Roadmap 2026 — Part 12
🧩 SQL Subqueries — Using One Query Inside Another
So far, we've learned how to retrieve, filter, group, transform, and combine data.
But sometimes a business question requires one query to use the result of another query.
For example:
«Find employees whose salary is higher than the average salary.»
First, we need to calculate:
SELECT AVG(salary)
FROM employees;
Then compare every employee against that result.
A subquery allows us to do both inside one SQL statement.
🧠 1. What Is a Subquery?
A subquery is a SQL query written inside another SQL query.
SELECT *
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
The inner query:
SELECT AVG(salary) FROM employees
calculates the average salary. The outer query then uses that result.
Think of it as:
Outer Query → Needs an answer → Subquery calculates the answer → Outer Query uses it
🔹 2. Basic Subquery Structure
SELECT column_name
FROM table_name
WHERE column_name operator (
SELECT ...
);
The inner query is enclosed in ( ... ).
📊 3. Subquery Returning One Value
A scalar subquery returns a single value.
SELECT
employee_name,
salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
The inner query returns something like 65000. The outer query then finds employees earning more than 65000.
💰 4. Employees Earning Above Average
Classic interview question.
SELECT
employee_name,
salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
Logic: Calculate average salary → Compare every employee → Keep salary > average
🏆 5. Products More Expensive Than Average
SELECT
product_name,
price
FROM products
WHERE price > (
SELECT AVG(price)
FROM products
);
🔢 6. Subquery with COUNT()
Customers who have placed more than 5 orders:
SELECT
customer_id,
customer_name
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5
);
🔗 7. Subquery with IN
Useful when a subquery returns multiple values.
SELECT *
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
);
Returns customers who have at least one order.
⚠️ 8. Single Value vs Multiple Values
One value → Use =, >, <, >=, <=
WHERE salary > (
SELECT AVG(salary)
FROM employees
)
Multiple values → Use IN
WHERE customer_id IN (
SELECT customer_id
FROM orders
)
🚫 9. NOT IN
SELECT
customer_id,
customer_name
FROM customers
WHERE customer_id NOT IN (
SELECT customer_id
FROM orders
);
⚠️ Important NULL Warning: NOT IN can produce unexpected results if the subquery contains NULL. For anti-matching, NOT EXISTS is often safer.
🔎 10. EXISTS
SELECT
c.customer_id,
c.customer_name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);
Means: Return the customer if at least one matching order exists.
🚫 11. NOT EXISTS
SELECT
c.customer_id,
c.customer_name
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
); | 617 |
| 11 |  🎓 𝗧𝗼𝗽 𝗜𝗻-𝗗𝗲𝗺𝗮𝗻𝗱 𝗙𝗥𝗘𝗘 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻𝘀 𝘁𝗼 𝗠𝗮𝘀𝘁𝗲𝗿 𝗶𝗻 𝟮𝟬𝟮𝟲 🔥
Explore these FREE certification courses in today’s most in-demand technology fields:
📊 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 :- https://pdlink.in/4eRA6eF
💻 𝗪𝗲𝗯 𝗗𝗲𝘃𝗲𝗹𝗼𝗽𝗺𝗲𝗻𝘁 :- https://pdlink.in/4gP18Eo
💫 𝗔𝗿𝘁𝗶𝗳𝗶𝗰𝗶𝗮𝗹 𝗜𝗻𝘁𝗲𝗹𝗹𝗶𝗴𝗲𝗻𝗰𝗲 :- https://pdlink.in/45HWa5Q
☁️ 𝗖𝗹𝗼𝘂𝗱 𝗖𝗼𝗺𝗽𝘂𝘁𝗶𝗻𝗴 :- https://pdlink.in/4zrksPn
🟧 𝗔𝗪𝗦 :- https://pdlink.in/4j4Jxtv
🛡️ 𝗖𝘆𝗯𝗲𝗿𝘀𝗲𝗰𝘂𝗿𝗶𝘁𝘆 & 𝗔𝘇𝘂𝗿𝗲 :- https://pdlink.in/4f0GNuH
⚡ Start learning today and prepare yourself for better career opportunities in 2026! | 267 |
| 12 |  🚀 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲 𝘁𝗼 𝗚𝗲𝘁 𝗮 𝗛𝗶𝗴𝗵-𝗣𝗮𝘆𝗶𝗻𝗴 𝗝𝗼𝗯 𝗶𝗻 𝟮𝟬𝟮𝟲 📊
Build job-ready skills through live online classes, practical assignments and real-world projects.
💼 End-to-End Placement Support
🤝 500+ Partner Companies
🎓 2000+ Students Placed
🏆 Highest Salary: ₹41 LPA
📞 Get FREE career counselling and check your eligibility!
🔗 𝗥𝗲𝗴𝗶𝘀𝘁𝗲𝗿 𝗡𝗼𝘄 👇
https://pdlink.in/45vk5ph
⚡Prepare for roles such as Data Analyst, Business Analyst, BI Analyst and Reporting Analyst. | 1 055 |
| 13 |  🚀 𝗧𝗼𝗽 𝟯 𝗙𝗥𝗘𝗘 𝗥𝗲𝘀𝗼𝘂𝗿𝗰𝗲𝘀 𝘁𝗼 𝗟𝗲𝗮𝗿𝗻 𝗜𝗻-𝗗𝗲𝗺𝗮𝗻𝗱 𝗧𝗲𝗰𝗵 𝗦𝗸𝗶𝗹𝗹𝘀 🔥
💫 Artificial Intelligence (AI)
📊 Data Analytics
🔐 Cybersecurity
🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 👇:-
https://pdlink.in/4y2XyN1
🎯 Perfect for Students • Freshers • Beginners • Tech Enthusiasts
💡 Learn for FREE → Build Skills → Upgrade Your Career | 1 496 |
| 14 | 𝐒𝐐𝐋 𝐂𝐚𝐬𝐞 𝐒𝐭𝐮𝐝𝐢𝐞𝐬 𝐟𝐨𝐫 𝐈𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰:
Join for more: https://t.me/sqlanalyst
1. Danny’s Diner:
Restaurant analytics to understand the customer orders pattern.
Link: https://8weeksqlchallenge.com/case-study-1/
2. Pizza Runner
Pizza shop analytics to optimize the efficiency of the operation
Link: https://8weeksqlchallenge.com/case-study-2/
3. Foodie Fie
Subscription-based food content platform
Link: https://lnkd.in/gzB39qAT
4. Data Bank: That’s money
Analytics based on customer activities with the digital bank
Link: https://lnkd.in/gH8pKPyv
5. Data Mart: Fresh is Best
Analytics on Online supermarket
Link: https://lnkd.in/gC5bkcDf
6. Clique Bait: Attention capturing
Analytics on the seafood industry
Link: https://lnkd.in/ggP4JiYG
7. Balanced Tree: Clothing Company
Analytics on the sales performance of clothing store
Link: https://8weeksqlchallenge.com/case-study-7
8. Fresh segments: Extract maximum value
Analytics on online advertising
Link: https://8weeksqlchallenge.com/case-study-8 | 2 464 |
| 15 | 𝐒𝐐𝐋 𝐂𝐚𝐬𝐞 𝐒𝐭𝐮𝐝𝐢𝐞𝐬 𝐟𝐨𝐫 𝐈𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰:
Join for more: https://t.me/sqlanalyst
1. Danny’s Diner:
Restaurant analytics to understand the customer orders pattern.
Link: https://8weeksqlchallenge.com/case-study-1/
2. Pizza Runner
Pizza shop analytics to optimize the efficiency of the operation
Link: https://8weeksqlchallenge.com/case-study-2/
3. Foodie Fie
Subscription-based food content platform
Link: https://lnkd.in/gzB39qAT
4. Data Bank: That’s money
Analytics based on customer activities with the digital bank
Link: https://lnkd.in/gH8pKPyv
5. Data Mart: Fresh is Best
Analytics on Online supermarket
Link: https://lnkd.in/gC5bkcDf
6. Clique Bait: Attention capturing
Analytics on the seafood industry
Link: https://lnkd.in/ggP4JiYG
7. Balanced Tree: Clothing Company
Analytics on the sales performance of clothing store
Link: https://8weeksqlchallenge.com/case-study-7
8. Fresh segments: Extract maximum value
Analytics on online advertising
Link: https://8weeksqlchallenge.com/case-study-8 | 1 |
| 16 |  𝗧𝗼𝗽 𝟱 𝗙𝗥𝗘𝗘 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 𝘁𝗼 𝗞𝗶𝗰𝗸𝘀𝘁𝗮𝗿𝘁 𝗬𝗼𝘂𝗿 𝗗𝗮𝘁𝗮 𝗦𝗰𝗶𝗲𝗻𝗰𝗲 𝗖𝗮𝗿𝗲𝗲𝗿 📊
Want to start a career in Data Science without spending money?
Here are 5 beginner-friendly learning resources covering essential skills such as Python, SQL, Machine Learning and hands-on projects.
🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 👇:-
https://pdlink.in/4ilAmok
🎯 Perfect for Students • Freshers • Beginners • Aspiring Data Scientists
💡 Learn → Practice → Build Projects → Create Your Portfolio | 2 108 |
| 17 |  𝗗𝗮𝘁𝗮 𝗦𝗰𝗶𝗲𝗻𝗰𝗲 𝗙𝗥𝗘𝗘 𝗢𝗻𝗹𝗶𝗻𝗲 𝗠𝗮𝘀𝘁𝗲𝗿𝗰𝗹𝗮𝘀𝘀 😍
💫Accelerate your career in Data Science
💫Discover the skills, tools and career roadmap needed to enter this high-demand field.
🔥 Beginner-friendly online session—no prior experience required!
𝗥𝗲𝗴𝗶𝘀𝘁𝗲𝗿 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘 👇:-
https://pdlink.in/46adC3l
(Only few slots left )
📅 Date: September 11, 2026
⏰ Time: 7:00 PM | 1 956 |
| 18 | A customer has 3 orders. After joining customers with orders, how many rows can that customer produce? | 1 625 |
| 19 | Why can a JOIN cause duplicate rows? | 1 470 |
| 20 | What does a LEFT JOIN return? | 1 393 |
