Challenges are often not a matter of difficulty, but of consistency.
Yesterday I published two solutions for the first ‘LeetCode SQL 50’ challenge.
To improve efficiency, I’ve decided to combine these solutions into one post for every 2 or 3 problems going forward.

LeetCode 595 - Big Countries [SQL 3/50]

This question introduces a new concept: double filtering. Together, we’ll learn how to use it effectively:

A country is big if:

  • it has an area of at least three million (i.e., 3000000 km2), or
  • it has a population of at least twenty-five million (i.e., 25000000).

Write a solution to find the name, population, and area of the big countries.
Return the result table in any order.
The result format is in the following example.
Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

Input: 
World table:
+-------------+-----------+---------+------------+--------------+
| name        | continent | area    | population | gdp          |
+-------------+-----------+---------+------------+--------------+
| Afghanistan | Asia      | 652230  | 25500100   | 20343000000  |
| Albania     | Europe    | 28748   | 2831741    | 12960000000  |
| Algeria     | Africa    | 2381741 | 37100000   | 188681000000 |
| Andorra     | Europe    | 468     | 78115      | 3712000000   |
| Angola      | Africa    | 1246700 | 20609294   | 100990000000 |
+-------------+-----------+---------+------------+--------------+
Output: 
+-------------+------------+---------+
| name        | population | area    |
+-------------+------------+---------+
| Afghanistan | 25500100   | 652230  |
| Algeria     | 37100000   | 2381741 |
+-------------+------------+---------+

Sometimes, we need to combine multiple filters to pinpoint the exact data we’re looking for. This challenge introduces the concept of “double filtering” alongside the “greater than or equals” operator. It might seem like a hurdle at first, but we’ll break it down and show you it’s actually quite manageable! 🖥️

1
2
3
4

-- Write your MySQL query statement below
SELECT  name, population, area
FROM World
WHERE area >= 3000000 OR population >= 25000000 -- That's all!!

LeetCode 1148 - Article Views I [SQL 4/50]

The following question involves several steps. Let’s break down what we need to do:

Write a solution to find all the authors that viewed at least one of their own articles.
Return the result table sorted by id in ascending order.
The result format is in the following example.
Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

Input: 
Views table:
+------------+-----------+-----------+------------+
| article_id | author_id | viewer_id | view_date  |
+------------+-----------+-----------+------------+
| 1          | 3         | 5         | 2019-08-01 |
| 1          | 3         | 6         | 2019-08-02 |
| 2          | 7         | 7         | 2019-08-01 |
| 2          | 7         | 6         | 2019-08-02 |
| 4          | 7         | 1         | 2019-07-22 |
| 3          | 4         | 4         | 2019-07-21 |
| 3          | 4         | 4         | 2019-07-21 |
+------------+-----------+-----------+------------+
Output: 
+------+
| id   |
+------+
| 4    |
| 7    |
+------+

Before we dive-in to the wonderful word of SQL queries, Let’s break down the steps involved:
first we need to define what we are looking for: we need to print the author_id in a column named id from the table Views.
Then we need to filter only the authors who view their own articles.
After that we need to ensure that the output will print us unique values (because we don’t want to see duplicated data) and it must be sorted by the author_id.
Sounds hard? actually it’s not.

1
2
3
4
5

-- Write your MySQL query statement below
SELECT DISTINCT author_id as id
FROM Views
WHERE author_id = viewer_id
ORDER BY author_id asc  ;

I was pleased to see that my solution performed very well on LeetCode. It surpassed the runtime of 98.15% of other users, coming in at only 345ms!

LeetCode 1148

If you have any question you can contact me on Dolev@Ravid.email

See you in the next post 👋