3 Jobs Bill Gates believes Will Survive

In partnership with

In today’s newsletter

1. Bill Gates' predictions about job safety in the age of AI

2. One Leetcode SQL solution (Level: easy)

3. SQL Windows function basics

3 Jobs Bill Gates believes Will Survive (Source)

According to Bill Gates, while Artificial Intelligence is set to take over many jobs, there are three professions that are likely to remain safe, at least for now. In a recent interview, Gates highlighted…

• Coders

• Energy experts

• Biologists 

He reasons that coders, who build and manage AI systems, will still be needed for complex software development, refining AI, and fixing any issues. The energy sector, with its intricate regulations and unpredictable nature (from oil to renewables), requires human expertise for decision-making and handling crises. Lastly, biologists, particularly those in research, rely on creativity and intuition to make breakthroughs, qualities that AI hasn't yet mastered.

While these jobs might be secure for the moment, Gates emphasizes that the impact of AI on the job market will continue to change. He advises everyone to be ready to adapt their skills and innovate to stay relevant in a future where AI will play a significant role in the workforce.

PRESENTED BY 1440 MEDIA

Looking for unbiased, fact-based news? Join 1440 today.

Join over 4 million Americans who start their day with 1440 – your daily digest for unbiased, fact-centric news. From politics to sports, we cover it all by analyzing over 100 sources. Our concise, 5-minute read lands in your inbox each morning at no cost. Experience news without the noise; let 1440 help you make up your own mind. Sign up now and invite your friends and family to be part of the informed.

Subscribe to 1440 today.

LeetCode PROBLEM: LEVEL: SIMPLE

Find Customer Referee: Leetcode Problem solution

NOTE: First try yourself and then scroll down see the answer to this problem

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
| referee_id  | int     |
+-------------+---------+
In SQL, id is the primary key column for this table.
Each row of this table indicates the id of a customer, their name, and the id of the customer who referred them.

Find the names of the customer that are not referred by the customer with id = 2.

Return the result table in any order.

Example 1:

Input: 
Customer table:
+----+------+------------+
| id | name | referee_id |
+----+------+------------+
| 1  | Will | null       |
| 2  | Jane | null       |
| 3  | Alex | 2          |
| 4  | Bill | null       |
| 5  | Zack | 1          |
| 6  | Mark | 2          |
+----+------+------------+
Output: 
+------+
| name |
+------+
| Will |
| Jane |
| Bill |
| Zack |
+------+

If you only use:

SELECT name FROM Customer WHERE referee_id != 2; 

It does not return the expected results because of NULL values in the referee_id column.

Why Doesn't referee_id != 2 Work?

• != only filters known values, but NULL means "unknown."

• Any comparison (!=, =, <, >) with NULL results in FALSE or UNKNOWN, so SQL excludes rows where referee_id IS NULL by default.

Correct Solution SQL

SELECT name  FROM Customer WHERE referee_id IS NULL OR referee_id != 2; 

• referee_id IS NULL → Includes customers not referred by anyone.

• referee_id != 2 → Excludes only those referred by id = 2.

Thumb Rule

Always use IS NULL when dealing with NULL values in conditions.
Comparisons (!=, =, <, >) do not work with NULL, so handle them explicitly.

SQL Window Functions

Window functions help us rank or number rows without losing any data.

Syntax: FOPO Format

F - FUNCTION() → The ranking function (ROW_NUMBER(), RANK(), DENSE_RANK()).
O - OVER → Tells SQL we are using a window function.
P - PARTITION BY → Groups the data (like GROUP BY, but keeps all rows).
O - ORDER BY → Decides how the ranking is assigned within each group.

Every window function follows this structure:

FUNCTION() OVER (PARTITION BY column1 ORDER BY column2) 

• FUNCTION() → The ranking function (e.g., ROW_NUMBER(), RANK(), DENSE_RANK()).

• PARTITION BY column → Groups the data (like GROUP BY but doesn’t collapse rows).

• ORDER BY column1 → Decides how the ranking is assigned.

1. PARTITION BY – The Key to Grouping

Before learning ranking functions, understand PARTITION BY.

It’s like GROUP BY, but it keeps all rows instead of collapsing them.

Example: Show each employee’s salary along with the average salary of their department.

SELECT name, department, salary,         AVG(salary) OVER (PARTITION BY department) AS avg_salary FROM employees; 

How It Works?

• Each department gets a separate calculation.

• Each row is kept, but we see the average for that group.

2. ROW_NUMBER() – Assign Unique Numbers

Gives a unique number to each row, no ties.

Example: Number employees by salary within each department.

SELECT name, department, salary,  
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num 
FROM employees; 

How It Works?

• Employees in the same department are ranked by salary.

• No ties – every row has a different number.

Use Case: Selecting the first row in each group or removing duplicates.

3. RANK() – Assign Ranks, But Skip Numbers

If two people have the same salary, they get the same rank. But the next rank is skipped.

Example: Rank employees by salary within their department.

SELECT name, department, salary,         RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank FROM employees; 

Use Case: Competitions (e.g., Olympic rankings).

 4. DENSE_RANK() – Assign Ranks, No Gaps

Same as RANK(), but without skipping numbers.

SELECT name, department, salary, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank FROM employees; 

👀 Example Output:

Use Case: Award distributions (e.g., Gold, Silver, Bronze).

Quick Summary

When to Use ORDER BY in a Window Function?

ORDER BY inside a window function controls the ranking or numbering order.

Use ORDER BY when using ranking functions (ROW_NUMBER(), RANK(), DENSE_RANK()).

It decides the order in which ranks are assigned.

🔹 Example: Rank employees by salary within each department.

SELECT name, department, salary,         RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank FROM employees; 

• ORDER BY salary DESC → Highest salary gets Rank 1.

• Without ORDER BY, ranking won’t make sense.

Do NOT use ORDER BY when using aggregate functions (SUM, AVG, COUNT) unless required.

• PARTITION BY is enough to group the data.

• Adding ORDER BY might change the result or cause errors.

(Correct Usage):

SELECT name, department, salary, AVG(salary) OVER (PARTITION BY department) AS avg_salary FROM employees; 

Mistake: Adding ORDER BY in AVG()

AVG(salary) OVER (PARTITION BY department ORDER BY salary)  -- wrong

This will create a moving average, not an average per department.

Rule of Thumb:

- Use ORDER BY with ranking functions (ROW_NUMBER(), RANK(), DENSE_RANK()).
- Skip ORDER BY in aggregate functions (AVG(), SUM(), etc.), unless you need a moving calculation.

Thanks for your time and consideration!

Shailesh from NextCareerStep