Find Invalid IP Addresses - Problem

Given a table logs containing server access log information, write a SQL solution to find invalid IP addresses.

An IPv4 address is considered invalid if it meets any of these conditions:

  • Contains numbers greater than 255 in any octet
  • Has leading zeros in any octet (like 01.02.03.04)
  • Has less or more than 4 octets

Return the result table with columns ip and invalid_count ordered by invalid_count and ip in descending order respectively.

Table Schema

logs
Column Name Type Description
log_id PK int Unique identifier for each log entry
ip varchar IP address from server access logs
status_code int HTTP status code for the request
Primary Key: log_id
Note: Each row contains server access log information including IP address and HTTP status code

Input & Output

Example 1 — Mixed Valid and Invalid IPs
Input Table:
log_id ip status_code
1 192.168.1.1 200
2 192.168.01.1 200
3 256.1.1.1 404
4 192.168.01.1 500
5 10.0.0 200
Output:
ip invalid_count
192.168.01.1 2
256.1.1.1 1
10.0.0 1
💡 Note:

192.168.01.1 appears twice and is invalid due to leading zero in third octet. 256.1.1.1 is invalid because 256 > 255. 10.0.0 is invalid because it has only 3 octets instead of 4. 192.168.1.1 is valid so not included in output.
Example 2 — All Valid IPs
Input Table:
log_id ip status_code
1 192.168.1.1 200
2 10.0.0.1 404
3 127.0.0.1 200
Output:
ip invalid_count
💡 Note:

All IP addresses are valid (proper format, no leading zeros, all octets ≤ 255, exactly 4 octets), so the result is empty.
Example 3 — Edge Cases
Input Table:
log_id ip status_code
1 0.0.0.0 200
2 00.0.0.0 404
3 255.255.255.255 200
4 1.2.3.4.5 500
Output:
ip invalid_count
1.2.3.4.5 1
00.0.0.0 1
💡 Note:

0.0.0.0 and 255.255.255.255 are valid edge cases. 00.0.0.0 is invalid due to leading zero. 1.2.3.4.5 is invalid due to having 5 octets instead of 4.

Constraints

  • 1 ≤ log_id ≤ 10000
  • ip contains only digits, dots, and may have invalid formats
  • 200 ≤ status_code ≤ 599

Visualization

Tap to expand
Find Invalid IP Addresses - SQL OverviewInput: logslog_idip1192.168.1.12192.168.01.13256.1.1.14192.168.01.1VALIDATECheck FormatCount InvalidOutput: Invalid IPsipinvalid_count192.168.01.12256.1.1.11Invalid IP Rules:❌ Leading zeros (01)❌ > 255❌ Wrong octet count✓ Valid format
Understanding the Visualization
1
Input
Server logs with IP addresses
2
Validate
Check IP format rules
3
Output
Invalid IPs with counts
Key Takeaway
🎯 Key Insight: Use regex patterns and string functions to validate complex IP address formats efficiently

Related Problems

Asked in
Amazon 28 Microsoft 22 Google 15
32.5K Views
Medium Frequency
~25 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen