SQL interview questions – Leetcode 613 – Shortest Distance in a Line

Problem Description –

Table point holds the x coordinate of some points on x-axis in a plane, which are all integers. Write a query to find the shortest distance between two points in these points.

The shortest distance is ‘1’ obviously, which is from point ‘-1’ to ‘0’. So the output is as below:

Note: Every point is unique, which means there is no duplicates in table point.

Difficulty Level – Easy

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

Solution –

SELECT MIN(abs(p2.x - p1.x)) shortest
FROM point p1 JOIN point p2
ON p1.x != p2.x

To solve the problem, we have to first joint the points table to itself ON p1.x != p2.x because we don’t want to subtract the number with itself as it will always give us 0.

SELECT 
	p1.x as x1,
	p2.x as x2 
FROM point p1 JOIN point p2 
ON p1.x != p2.x 

Next, to find the distance, we have to subtract x1 and x2 and take the ABS value of the difference as distance can not be negative.

SELECT 
	p1.x as x1,
	p2.x as x2,
	ABS(p2.x - p1.x) as distance  
FROM point p1 JOIN point p2 
ON p1.x != p2.x

We can see that distance is minimum when we subtract -1 and 0. Now we can just use the MIN function to get the answer.