LeetCode 1972. First and Last Call On the Same Day SQL Solution

Problem

LeetCode SQL Problem

First and Last Call On the Same Day

Calls table

caller_id	recipient_id	call_time
8	4	2021-08-24 17:46:07
4	8	2021-08-24 19:57:13
5	1	2021-08-11 05:28:44
8	3	2021-08-17 04:04:15
11	3	2021-08-17 13:07:00
8	11	2021-08-17 22:22:22

Solution

MySQL
TSQL

MySQL Solution to report user IDs whose first and last calls on any day were with the same person
WITH ranked_calls
AS (
	SELECT *
		-- Extract date component
		,DATE (call_time) AS call_date
		-- Sum up values of caller_id and recipient_id
		-- The same people involved in the first and last call when id_sum is equal
		,caller_id + recipient_id AS id_sum
		-- Within each date, rank call_time ascending order.
		-- First call for each day will have rank 1
		,RANK() OVER (
			PARTITION BY DATE (call_time) ORDER BY call_time
			) AS first_call_rank
		-- Within each date, rank call_time in descending order
		-- Last call for each day will have rank 1
		,RANK() OVER (
			PARTITION BY DATE (call_time) ORDER BY call_time DESC
			) AS last_call_rank
	FROM Calls
	)
	,same_first_and_last_call
AS (
	SELECT L.caller_id
		,L.recipient_id
	-- Perform Self Join to put first and last call of each date in the same row
	FROM ranked_calls AS F
	INNER JOIN ranked_calls AS L ON F.first_call_rank = 1
		AND L.last_call_rank = 1
		AND F.call_date = L.call_date
		-- Only include rows that have same people involved in the first and last call
		AND F.id_sum = L.id_sum
	)
-- Get caller_id values and alias the column as user_id
SELECT caller_id AS user_id
FROM same_first_and_last_call

UNION

-- Append recipient_id values
SELECT recipient_id
FROM same_first_and_last_call
ORDER BY user_id

Query Output

user_id
1
4
5
8

TSQL Solution to report user IDs whose first and last calls on any day were with the same person
WITH ranked_calls
AS (
    SELECT *
        -- Extract date component
        ,CONVERT(DATE, call_time) AS call_date
        -- Sum up values of caller_id and recipient_id
        -- The same people involved in the first and last call when id_sum is equal
        ,caller_id + recipient_id AS id_sum
        -- Within each date, rank call_time ascending order.
        -- First call for each day will have rank 1
        ,RANK() OVER (
            PARTITION BY CONVERT(DATE, call_time) ORDER BY call_time
            ) AS first_call_rank
        -- Within each date, rank call_time in descending order
        -- Last call for each day will have rank 1
        ,RANK() OVER (
            PARTITION BY CONVERT(DATE, call_time) ORDER BY call_time DESC
            ) AS last_call_rank
    FROM Calls
    )
    ,same_first_and_last_call
AS (
    SELECT L.caller_id
        ,L.recipient_id
    -- Perform Self Join to put first and last call of each date in the same row
    FROM ranked_calls AS F
    INNER JOIN ranked_calls AS L ON F.first_call_rank = 1
        AND L.last_call_rank = 1
        AND F.call_date = L.call_date
        -- Only include rows that have same people involved in the first and last call
        AND F.id_sum = L.id_sum
    )
-- Get caller_id values and alias the column as user_id
SELECT caller_id AS user_id
FROM same_first_and_last_call

UNION

-- Append recipient_id values
SELECT recipient_id
FROM same_first_and_last_call
ORDER BY user_id

Query Output

user_id
1
4
5
8

Problem​

Solution​

Problem

Solution