Summary

SQL Problem Solving: Weather Observation Station

In this lesson, we solve a problem related to the Weather Observation Station for HackerRank under the Basic Select SQL category.

Problem Overview

We have a table named station with the following columns:

ID
City
State
Ladder
Score
N
Long
Score W

The goal is to:

Count the total number of entries (rows) in the station table.
Subtract the number of distinct cities from the total count.

Example

Given the data such as:

New York
New York
Bengaluru

The counts would be:

Total Rows: 3
Distinct Cities: 2 (New York and Bengaluru)

Thus, the final result would be:

3 (total rows) - 2 (distinct cities) = 1

SQL Query Breakdown

Count Total Rows: To count the total number of entries, use:
```
SELECT COUNT(*) FROM station;
```
Count Distinct Cities: To count only unique city names, modify the count operation:
```
SELECT COUNT(DISTINCT city) FROM station;
```

Final Query: Combine the two counts in a single query:

SELECT COUNT(*) - COUNT(DISTINCT city) FROM station;

Conclusion

By running the above query, we can determine the specified count difference. The expected output for this particular problem is 13, and upon executing the code, we confirm that our approach is correct.

Finally, don't forget to submit the code once verified.

Video Transcript

This lesson I will solve the problem with our observation station for on the subcategory basic select in the category SQL from HackerRank. So here's the problem, we have the table station, it has the columns ID, city, state, ladder, score, n, long, and score, w. So we're asked to count the number of entries, that is number of rows in this table. And that's easy, right? If you want to count anything in SQL, the most basic thing you can do is select count, parentheses, star. And that should give us number of rows. Now it's also asking us to subtract the number of distinct cities. That is, we have to take the cities that might be duplicate. For example, we have New York, New York, Bengaluru, like it's said here. We have to take this count of three because there's three rows. But then we have to subtract from the distinct count of cities. In this case, there's two unique cities, New York and Bengaluru. So that's two, so three minus two is one, so that's what the query should return. Now, how can we do that? Now let's start with the thing we know, right? So I'm going to select my SQL here. I'm going to say select count star from station. So this is going to give us the total number of rows in this table. Now we need to subtract something. So here in the select, I'm going to say minus, subtracting another count, right? We need to count city, but if we count city like so, you see it's the wrong answer. It's going to be the same as count star. So we have to find a way of counting only the unique city names. To do that, it's very simple. Before the city, the column name, you have to say distinct. So once you say distinct before the column name, and you pass it to the count function, it's only going to count the unique city names. So in this case, you're going to take the total number of rows, minus the unique ones, and that should give you what the problem is asking for. Run the code. And there you go, the output 13, and we got it passing. Let's submit the code. And that's weather observation station four.