Week 3 - SQL Chips and Chunks Newsletter

1. SQL Chip: Using CASE for Conditional Logic

Need to categorize data in your query results? Use CASE to apply conditional logic within a SELECT statement. Here’s an example that labels employees based on their salary range:

SELECT
name,
salary,
CASE WHEN salary > 100000 THEN 'High'
WHEN salary BETWEEN 50000 AND 100000 THEN 'Medium'
ELSE 'Low' END AS salary_category
FROM employees;

This helps in creating custom categories without modifying the database.

2. SQL Chunk: Advanced Grouping with HAVING Clause

While the GROUP BY clause helps you summarize data, sometimes you need to filter those results based on aggregate values. This is where the HAVING clause comes into play. Unlike WHERE, which filters rows before grouping, HAVING filters groups after the aggregation.

For instance, if you want to find departments with more than 5 employees, you can use HAVING to filter out smaller groups:

SELECT
department,
COUNT(*) AS num_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

In this example, the query groups employees by department and counts the number of employees in each. Then, the HAVING clause ensures that only departments with more than 5 employees are included in the results. This powerful combination of GROUP BY and HAVING allows you to perform more advanced aggregations and insights, which can be essential when analyzing large datasets.

3. SQL Challenge: Find the Highest-Paid Employee in Each Department

This week’s challenge: Write a query to find the highest-paid employee in each department.

Hint: You can use MAX(salary) along with GROUP BY.

SELECT
department,
MAX(salary) AS highest_salary
FROM employees
GROUP BY department;

Try modifying this query to also return the employee names who earn the highest salary in each department!

4. Common SQL Mistake: Misusing COUNT(**)**

Many SQL beginners mistakenly assume COUNT(column_name) and COUNT(*) return the same results.

• COUNT(column_name) only counts non-null values.

• COUNT(*) counts all rows, including those with NULL values.

Example:

SELECT COUNT(department) FROM employees; -- Ignores NULL values
SELECT COUNT(*) FROM employees; -- Counts all rows

Use the appropriate COUNT function depending on your dataset!

5. SQL in Practice: Calculating Customer Retention

Customer retention is key in business analytics. Here’s an example query to count returning customers (customers who made more than one purchase):

SELECT
customer_id,
COUNT(*) AS total_purchases
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;

This helps identify loyal customers and track purchasing trends. How could you modify this to find first-time customers instead?

6. SQL Resource: Data Engineer Academy (Free & Paid Resources)

As part of your journey into SQL and data engineering, don’t forget about Data Engineer Academy. They offer free resources for beginners and intermediate learners, covering SQL, data pipelines, and data warehouses. For those looking to dive deeper, paid members get access to structured courses, hands-on projects, mentorship, one-on-one coaching, mock interviews, job preparation resources, and even guaranteed interviews to help transition into a data engineering career. If you’re serious about leveling up your skills and landing a job, this is a fantastic place to explore!