SQL Interview Questions – Leetcode 612 – Shortest Distance in a Plane

Problem Description –

Table point_2d holds the coordinates (x,y) of some unique points (more than two) in a plane. Write a query to find the shortest distance between these points rounded to 2 decimals.

The shortest distance is 1.00 from point (-1,-1) to (-1,2). So the output should be:

Note: The longest distance among all the points are less than 10000.

Difficulty Level – Medium

Problem Link – https://leetcode.com/problems/shortest-distance-in-a-plane/

Solution –

SELECT 
	MIN(ROUND(SQRT(POWER(p2.x - p1.x, 2) + POWER(p2.y - p1.y, 2)),2)) as shortest
FROM point_2d as p1 JOIN point_2d as p2
ON p1.x != p2.x OR  p1.y != p2.y

The distance between two points (x1, y1) and (x2, y2) on a plane can be calculated by pythagorean theorem.

WITH result AS(
SELECT 
	p1.x AS x1,
	p1.y AS y1,
	p2.x AS x2,
	p2.y AS y2,
	SQRT(POWER(p2.x - p1.x, 2) + POWER(p2.y - p1.y, 2)) distance 
FROM point_2d as p1 JOIN point_2d as p2 
ON p1.x != p2.x OR  p1.y != p2.y
)

SELECT * 
FROM result

We can see that the minimum distance is 1. Now, all we have to do is round the numbers to two decimal palces and use the MIN() function to select the answer.

Rating: 1 out of 5.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s