First and last IP in a network

Task

Given a table where users' connections are logged, find the first and the last address of the networks they connected from.

Notes

• Order the result by the id column
• There's no need to validate anything - it's okay if the user connects from a private network
• (You don't need the connection_time field for this task but without it the input data looks too dull)
• You can read more about IPv4 on Wikipedia (check the First and last subnet addresses section if you need an example/explanation related to this task only)

---

Input table

---------------------------------------------
|    Table    |     Column      |   Type    |
|-------------+-----------------+-----------|
| connections | id              | int       |
|             | connection_time | timestamp |
|             | ip_address      | inet      |
---------------------------------------------

Output table

------------------------
|    Column     | Type |
|---------------+------|
| id            | int  |
| first_address | text |
| last_address  | text |
------------------------

---

Example

For the IP address 182.240.42.115/24 the first address in the network is 182.240.42.0/24, and the last one is 182.240.42.255/24.

 

 

Solution:

SELECT
  id,
  NETWORK(ip_address) AS first_address,
  BROADCAST(ip_address) AS last_address
FROM connections
ORDER BY id

broadcast(inet) is broadcast address for network -> 192.168.1.255/24

network(inet) is extract network part of address -> 192.168.1.0/24

 

 

Reference:

Network Address Functions and Operators